As a project analysis, database design is a very important and difficult problem,
Completely in accordance with the paradigm may not meet user needs, not conducive to programming,
It seems to be a concrete analysis of specific problems, database design is a compromise between paradigm and demand.
When I was at school, I didn't see how important the data type was, and now I found out about the data type
The specific content is also very important, you can know the compatibility problem between different databases
What to do with it.
Database Design Tips:
Part 2nd-Design tables and fields
1. Review of changes
When I design a database, I take into account which data fields may change in the future. For example, a surname is like this (note
The surname of a Westerner, such as a woman's surname after marriage. So, when building a system to store customer information, I tend to
Store the last Name field in a separate table, plus fields such as start and end days so that you can track the
Changes to the data entry.
-shropshire Lad
2. Use a meaningful field name
Once I took part in the development of a project, which had programs inherited from other programmers, that programmer liked to use on the screen
Display data indicating the name field, which is not bad, but unfortunately, she also likes to use some strange nomenclature, its name mining
A combination of Hungarian named and controlled serial numbers, such as Cbo1, Txt2, Txt2_b, and so on.
Unless you are using a system that only targets your abbreviated field names, make the fields as clear as possible. Of course, don't
Overdo it, like customer_shipping_address_street_line_1 I though very descriptive, but no one wants to
Type such a long name, the specific scale is in your grasp.
-lamont Adams
3. Use prefix naming
If multiple tables have many fields of the same type (for example, FirstName), you might use a prefix for a particular table (for example,
Cuslastname) to help you identify the field.
-notoriousdog
The timeliness data should include the recently updated Date/Time field. Time stamp on the cause of the problem finding the data, re-by date
It is especially useful to work with data overload and erase old data.
-kol
5. Standardization and data-driven
The standardization of data is not only convenient for itself but also convenient for others. For example, if your user interface has access to external data
Source (file, XML document, other database, etc.), you may wish to store the corresponding connection and path information in the User Interface support table
In Also, if the user interface executes tasks such as workflow (sending mail, printing letterhead, modifying record status, and so on), that
The data that generates the workflow can also be stored in the database. Scheduling always requires effort, but if these processes adopt a number
It is much more convenient to change and maintain the policy, according to the driver rather than the hard-coded method. In fact, if the process is data driven
, you can put a lot of responsibility to the user, the user to maintain their workflow process.
-tduvall
6. Standardization cannot be overdone
For those unfamiliar with the word Standardization (normalization), standardization ensures that the fields within the table are the most basic
Elements, and this measure helps to eliminate data redundancy in the database. There are several forms of standardization, but third Normal
Form (3NF) is often considered to be the best balance of performance, extensibility, and data integrity. In simple terms, the 3NF gauge
Set
· Each value in the table can only be expressed once.
· Each row in the table should be uniquely identified (with a unique key).
· Non-key information that depends on other keys should not be stored in the table.
Databases adhering to the 3NF standard have the following characteristics: There is a set of tables that specifically store the associated data connected by a key. Say
A 3NF database that holds customers and their related orders may have two tables: Customer and order. Order table not wrapped
Contains any information about the customer that the order is associated with, but a key value is placed in the table that points to the customer list containing the account information
In that line.
There are higher levels of standardization, but is it better to be more standard? The answer is not necessarily. In fact, for some projects to
said that even 3NF could introduce too much complexity into the database.
-lamont Adams
For efficiency reasons, it is sometimes necessary to standardize the tables, and there are many examples. There was a development financial analysis
The software's job is to use non-standard tables to reduce the query time from an average of 40 seconds to about two seconds. Even though I had to do this,
But I do not regard the non-standard of the data table as the design idea of course. And the specific operation is only a derivation. So if the table
It is entirely possible that problems have been generated to recreate non standardized tables.
-epepke
7. Microsoft Access Reporting Tips
If you are using Microsoft Access, you can use a user-friendly field name instead of a numbered name: for example, with
Customer Name replaces Txtcnam. This way, when you create a form and a report with a wizard, the name will let those not
Being a programmer is easier for people to read.
-jwoodruf
8. Inactive or not used indicator
Adding a field to indicate whether the record is inactive in the business is useful. Whether it's a customer, an employee, or something.
, doing so can help you to filter the active or inactive state when you run the query again. It also eliminates the use of new users
Some of the problems faced by the data, for example, some of the records may no longer be used for them, and then deleted can play a certain
Preventive action.
-theoden
9. Use role entities to define columns belonging to a category
When you need to define things that belong to a particular category or have a specific role, you can create a specific time pass with a role entity
, so that you can achieve self documentation.
The implication here is not to have the person entity with the title field, but rather to say, why not use the person entity and
Person_type entities to describe people? Then, for example, when John Smith, Engineer promoted to John,
Smith, Director and finally climbed to John Smith, CIO's high, and all you have to do is change two tables
The key value of the relationship between person and Person_type, while adding a date/Time field to know when the change is
happen. In this way, your Person_type table contains all the possible types of person, such as associate,
Engineer, Director, CIO or CEO.
Another alternative would be to change the person record to reflect the change in the new title, but not to be able to track it in time.
The specific time of the person's place.
-teburlew
10. Use of commonly used entity naming agency data
The easiest way to organize your data is to use commonly used names, such as: person, organization, address, and
PHONE and so on. When you combine these commonly used generic names or create specific corresponding pairs of entities, you get
A special version of yourself. The main reason for using generic terminology at the outset is that all the specific users are able to
of the body.
With these abstract representations, you can use your own special name in level 2nd identification, for example, a person may be
Employee, spouse, Patient, Client, Customer, vendor or teacher. The same,
Organization may also be mycompany, mydepartment, competitor, hospital,
Warehouse, government and so on. The last address can be specific for site, Location, home, Work,
Client, Vendor, Corporate and Fieldoffice.
Using generic abstract terms to identify "things" allows you to get a great deal of information about the data to meet your business requirements.
Activity, while doing so can significantly reduce the amount of redundancy required for data storage.
-teburlew
11. Users from all over the world
When designing a database that uses a network or other international features, be sure to remember that most countries have different field lattices
, such as postal code, some countries, such as New Zealand, there is no zip code said.
-billh
12. Data duplication requires the use of discrete data tables
If you find yourself typing data repeatedly, create a new table and a new relationship.
-alan Rash
13.3 Useful fields that should be added to each table
· Drecordcreationdate, in VB under the default is now (), and in SQL Server defaults to GETDATE ()
· Srecordcreator, defaults to not NULL default USER under SQL Server
· Nrecordversion, a recorded version mark, which helps to accurately describe the origin of NULL data or lost data in the record
Because
-peter Ritchie
14. Use multiple fields for addresses and telephones
It is not enough to describe a street address on a single line. Address_line1, Address_line2 and Address_line3 can
To provide greater flexibility. Also, phone numbers and email addresses have their own data tables, with their own type
and tag categories.
-dwnerd
Be careful about standardizing too much, which can lead to performance problems. Although address and phone table separation can usually be achieved
Best state, but if you need to access this type of information frequently, you might want to store "preferred" information in its parent table (for example,
Customer, etc.) more appropriate. The compromise between non-standard and accelerated access is of some significance.
-dhattrem
15. Using Multiple name fields
I was surprised that many people left a field in the database for name. I think only developers who are just getting started
, but it's actually very common online. I suggest that you should treat last name and first name as two fields, and then
And then put them together in the query.
-klempan
Klempan is not the only person who notices the use of a single name field to make this situation more user-friendly and better
Some way. My most common use is to create a computed column in the same table that automatically joins the normalized field, which
It also changes when the sample data is changed. However, this is tricky to do with modeling software. In short, the connection is used
Fields can be used effectively to isolate user applications and developer interfaces.
-damon
16. Beware of case-by-case mixing of object names and special characters
One of the things that annoyed me the most in the past is that the database has a mixture of uppercase and lowercase object names, such as CustomerData. This question
Problems from Access to Oracle databases. I do not like the use of this case-by-case method of object naming, the result is not
You don't have to change your name manually. Think about the day that this database/application can get mixed in with a more powerful database? With full
Uppercase and contains the name of the next stroke is better readable (customer_data), absolutely not in the object name of the
Spaces between characters.
-bfren
17. Beware of reserved words
Make sure your field name doesn't conflict with reserved words, database systems, or common access methods, such as the one I wrote recently
There is a table in the ODBC connector that uses DESC as the Description field name. The consequences are conceivable! DESC is
Descending the reserved word after the abbreviation. A SELECT * statement in the table can be used, but I get a lot of
Useless information.
-daniel Jordan
18. Maintain consistency of field names and types
You must ensure consistency when you name a field and specify the data type for it. If the field is called in a table
"Agreement_number", you don't change the name to "Ref1" in the other table. If the data type is in a table
is a whole number, that's not going to be a character in the other table. Remember, you've done your job, and the others will use your number.
According to the library.
-setanta
19. Carefully select the number type
Use smallint and tinyint types in SQL with special care, for example, if you want to look at the total monthly sales, your total word
The segment type is smallint, so if the total is over $32,767 you can't do the calculation.
-egermain
20. Delete tag
Include a delete tag field in the table so that you can mark the row for deletion. Do not delete them separately in the relational database
A row, preferably with a data-clearing program and careful maintenance of indexing integrity.
-kol
21. Avoid using triggers
The functionality of triggers can often be implemented in other ways. Triggers can become interference when the program is debugged. If you really need to pick
With triggers, you'd better focus on documenting it.
-kol
22. Include version mechanism
It is recommended that you introduce a version control mechanism in your database to determine the version of the database in use. You're going to make it happen anyway.
Please. A long time, the user's needs will always change. The database structure may eventually be required to be modified. Although you can pass the check
Check the new field or index to determine the version of the database structure, but I found that the version information is stored directly in the database is not more square
Then?
-richard Foster
23. Sufficient margin for text field
Text fields of type ID, such as customer ID or order number, and so on should be set more than the general imagination, because the time is not long you
Most likely to be embarrassed by adding extra characters. Let's say your customer ID is 10 digits long. Then you should
Set the length of the database table field to 12 or 13 characters long. Is that a waste of space? It's a little, but it's not what you think.
So many: A field with a length of 3 characters in 1 million records, plus a little index of the case only let the entire data
The library occupies 3MB more space. But this extra space does not need to refactor the entire database in the future to achieve database size
's growth.
-tlundin
24. Column Naming tips
We found that if you use a uniform prefix for the column names of each table, the SQL expression will be written with a large
A big simplification. There are drawbacks to doing this, such as breaking the automatic table join tool, which makes the common column name the same number
The database is linked, but even these tools sometimes don't connect incorrectly. For a simple example, suppose there are two tables:
Customer and order. The Customer table prefix is cu_, so the child segments in the table are named as follows: CU_NAME_ID,
Cu_surname, Cu_initials and cu_address. The Order table prefix is or_, so the child segment name is:
or_order_id, or_cust_name_id, Or_quantity and Or_description.
The SQL statement that selects all the data from the database can be written as follows:
Select * from Customer, order
Where cu_surname = "MyName"
and cu_name_id = or_cust_name_id
and or_quantity = 1;
This is the case without these prefixes:
Select * from Customer, order
Where customer.surname = "MyName"
and customer.name_id = order.cust_name_id
and order.quantity = 1
The 1th SQL statement does not have a few characters to type. But if the query involves 5 tables and even more columns, you know the trick.
It's more useful.
-bryce Stenberg
Part 3rd-Select keys and Indexes
1. Data mining should be planned in advance
My marketing department had to deal with over 80,000 contacts at a time and fill in the necessary data for each customer (this is definitely not a small
Live). I also want to identify a group of customers as a marketing target. When I design tables and fields from the very beginning, I try not to
Add too many fields to the primary index to speed up the database. Then I realized that specific group queries and information mining
Neither accurate nor fast. As a result, the data fields are rebuilt and merged in the primary index. I found out that there was a plan phase
When it's critical-why do I use numbers as the primary index field when I want to create a system type lookup? I can use the fax number.
To retrieve it, but it's almost as much of a system type as it doesn't matter to me. Use the latter as the primary field, database update
Re-indexing and retrieval is much faster.
-hscovell
There are differences in data indexing between operational data warehouses (ODS) and Data warehousing (DW) environments. In the DW environment
, you need to consider how the sales department organizes the sales activities. They are not database administrators, but they determine the
Key information. Here the designer or database worker should analyze the database structure to determine between performance and correct output
The best conditions.
-teburlew
2. Using system-generated primary keys
This day is similar to skill 1, but I think it is necessary to remind everyone here. If you are always designing a database using
The system-generated key is used as the primary key, then you actually control the index integrity of the database. In this way, the database and the non-human mechanism
Effectively controls access to each row in the stored data.
The advantage of using a system-generated key as a primary key is that it is easy to find a logical defect when you have a consistent key structure.
-teburlew
3. Decomposition fields for indexing
To isolate named fields and include fields to support user-defined reports, consider factoring other fields (even primary keys) as their groups
As an element so that users can index it. Indexing speeds up the execution of SQL and Report Builder scripts. Say
I usually create a report if I have to use an SQL like expression because the Case Number field cannot be decomposed into
Year, serial number, case type and defendant code, and other elements. Performance can also go bad. If the year and type words
Segments can be broken down into indexed fields. Then these reports will run much faster.
-rdelval
4. Key Design 4 principle
· Creates a foreign key for the associated field.
· All keys must be unique.
· Avoid using composite keys.
· A foreign key is always associated with a unique key field.
-peter Ritchie
5. Don't forget the index
An index is one of the most efficient ways to get data from a database. 95% of database performance problems can be obtained by indexing technology
Solve. As a rule, I usually use a unique group index on a logical primary key, using the System key (as a stored procedure)
A unique, non-group index that uses a non group index on any foreign key columns. However, the index is like salt, too much food on the lll. You
Consider how large the database is, how the tables are accessed, and whether these accesses are primarily used for reading and writing.
-tduvall
Most databases index automatically created primary key fields, but don't forget to index foreign keys, which are also frequently used keys, than
Running a query displays a record of the primary table and all associated tables. Also, do not index memo/note fields, do not
To index large fields (with many characters), this makes the index occupy too much storage space.
-gbrayton
6. Do not index the common small table
Do not set any keys for small data tables, even if they often have inserts and deletes. To these inserts and
The index maintenance of a delete operation may consume more time than the scan table space.
-kbpatel
7. Do not select the Social Security Number (SSN) as a key
Never use the SSN as the key to the database. In addition to privacy reasons, it is more and more the government's tendency to disallow
The SSN is used for other purposes other than revenue related, and the SSN needs to be entered manually. Never use a manually typed key as the primary
Key, because once you enter the error, the only thing you can do is to delete the entire record and start from scratch.
-teburlew
When I was in college in the 70 's, I remembered that the SSN had been used as a school number, of course, even though it was right and wrong.
Law's. And people know it's illegal, but they're used to it. Later, as the crime of identity theft increased
Plus, my current college campus is painfully removing the SSN from a large stall of data.
-generalist
8. Do not use the user's key
When deciding what field to use as a key for a table, be sure to be careful what fields the user will edit. Not usually.
Select the fields that the user can edit as the key. This will force you to take the following two measures:
· Imposes restrictions on the behavior of the user editing fields after the record is created. If you do, you may find your application
The order of business needs to change abruptly, and users need not be flexible enough to edit those fields that are not editable. When using
The user has to enter data and then save the record before they find out the system is out of order. Delete Rebuild? If the record is not
Does rebuilding allow users to walk away?
· Some methods for detecting and correcting key conflicts are presented. Usually, it takes a bit of energy to get it done, but in terms of performance,
The price is much higher. Also, key corrections may force you to break through the separation between your data and the business/user interface layer
From.
So it's an old saying: your design needs to be adapted to the user rather than to the user to adapt to your design.
-lamont Adams
The reason that you do not make the primary key updatable is that in relational mode, the primary key implements an association between the different tables. Like what
The Customer table has a primary key CustomerID, and the client's order is stored in another sheet. The primary key of the order table might
A combination of OrderNo or OrderNo, CustomerID, and dates. Regardless of which key setting you choose, you will need to
The order form is stored in CustomerID to ensure that you can find a record of the orders for the user who placed the order.
If you modify the CustomerID in the Customer table, then you must find all the relevant records in the order list to enter
Row modification. Otherwise, some orders will not belong to any customer--the integrity of the database is finished.
If the index integrity rules are applied to the table level, it is almost impossible to write large amounts of code and append deleted records
Change the key of a record and all the associated records in the database. This process is often incorrectly clustered and should be avoided as much as possible.
-ljboast
9. Optional keys can sometimes be the key
Remember, it's not the machine that queries the data, it's people.
If you have an optional key, you may further use it as the master key. In that case, you will have the ability to build a powerful index. This
Samples can prevent people who use the database from having to connect to the database to properly filter the data. In a database with strict control over a domain table
, this kind of load is more eye-catching. If the optional key is really useful, it is the level of the primary key.
My view is that if you have an optional key, such as a state_code in a national table, you should not be on a unique key that cannot be changed.
Create a subsequent key. All you have to do is create worthless data. For example, the following examples:
Select COUNT (*)
From address, state_ref
where
address.state_id = state_ref.state_id
and State_ref.state_code = ' TN '
My approach is this:
Select COUNT (*)
From address
where
and State_code = ' TN '
If you create this kind of table association because of the overuse of the table's subsequent keys, the workload really needs to be considered.
-stocker
10. Don't forget the foreign key
The primary key fields that are automatically created by most database indexes. But don't forget to index the foreign key fields, which are in the record you want to query the main table
And its associated records are used every time. Also, do not index memo/notes fields and do not index large text fields
(many characters), this will allow your index to occupy a large amount of database space.
-gbrayton
Part 4-Ensuring data integrity
1. Enforce data integrity with constraints rather than business rules
If you are dealing with requirements according to business rules, then you should check the business hierarchy/user interface: If the business rules change later
, then only need to update.
If the requirements are rooted in the need to maintain data integrity, there is a need to impose constraints on the database level.
If you do use constraints on the data tier, you have to make sure that you have a way to use the user understanding for the reason that the update cannot be checked by the constraint
Language to inform the user interface. The field name itself is not enough unless you have a lengthy field name. -lamont Adams
Whenever possible, use a database system to achieve the integrity of your data. This includes not only the integrity achieved through standardization but also
Includes the functionality of the data. When writing data, you can also increase the trigger to ensure the correctness of the data. Don't rely on the business layer
ensure data integrity; it does not guarantee the integrity of the tables (foreign keys) so it cannot be imposed on other integrity rules.
-peter Ritchie
2. Distributed Data System
For a distributed system, it should be before you decide whether to copy all the data at each site or keep the data in one place
Estimate the amount of data for the next 5 or 10 years. When you transfer data to another site, preferably in the database field
Set some tags in the. Update your tags after receiving your data at the destination site. In order to make this data transfer, please write down
Your own batch or scheduler runs at specific intervals and does not allow users to transfer data after a daily job. Local
Copy your maintenance data, such as calculation constants and interest rates, and set the version number to ensure that the data is identical at every site.
-suhair TechRepublic
3. Force indication Integrity
There is no good way to eliminate harmful data after it has entered the database, so you should remove it before it enters the database. Excited
The indication integrity characteristics of the live database system. This will keep the data clean and force developers to devote more time
Error conditions.
-kol
4. Relationship
If there is a many-to-many relationship between two entities, and it is possible to translate into many-to-many relationships, then you might want to set the
Chengdo to multiple relationships. Shifting from the existing many-to-many relationship to many-to-many relationships is much more difficult than a many-to-many relationship in the beginning.
-cs Data Architect
5. Using the View
To provide another layer of abstraction between your database and your application code, you can create a dedicated
View without having to access the data table directly from the application. This would also give you a lot more to do when dealing with database changes
Free.
-gay Howe
6. Planning for data retention and recovery
Consider the data retention strategy and include it in the design process to proactively design your data recovery process. Adoption can be published to the user/development
The data dictionary of the personnel realizes the convenient data recognition while ensuring the documentation of the data source. Write an online update to "update the query" for
Later, in case of data loss, updates can be processed again.
-kol
7. Use stored procedures to make the system do heavy work
After resolving a lot of trouble to produce a highly-complete database solution, my team decided to encapsulate some
A functional group of associated tables that provides a set of regular stored procedures to access groups to speed up and simplify client code
Of In the meantime, we found that the 3GL encoder set all possible error conditions, such as the following:
SELECT Cnt = COUNT (*)
from [<table>]
WHERE [<primary key column>] = <new value>
IF Cnt = 0
BEGIN
INSERT into [<table>]
([< primary key column>])
VALUES (<new value>)
End
ELSE
BEGIN
<indicate duplication error>
End
and a non-3GL encoder does this:
INSERT into [<table>]
([< primary key column>])
VALUES
(<new value>)
IF @ @ERROR = 2627-Literal ERROR code for Primary Key Constraint
BEGIN
<indicate duplication error>
End
The 2nd program is much simpler and, in fact, leverages the functionality we give to the database. Although I personally do not like to use embedded text
Word (2627). But that can be easily replaced with a bit of advance processing. The database is not just a place to store data
Square, it is also a place to simplify coding.
-a-smith
8. Use Find
The best way to control data integrity is to limit the user's choice. Should provide a clear value to the user whenever possible
List for the selection. This will reduce the error and misunderstanding of the typing code while providing consistent data. Certain public data are particularly appropriate
Close search: Country code, status code, etc.
-cs Data Architect
Part 5th-Various tips
1. Documents, documents, documents
Documentation is documented for all shortcuts, naming conventions, restrictions, and functions.
-nickypendragon
Use database Tools that annotate tables, columns, triggers, and so on. Yes, it's a bit of a bother, but in the long run it's done off
It is useful to send, support, and track modifications.
-chardove
Depending on the database system you are using, there may be some software that will give you some documentation for your quick start. You might want to open it first.
began to say, and then get more and more details. Or you may want to schedule periodic prefetching, while entering new data along with your
Progress to detail each part. Whichever way you choose, always document your database, or in the database itself
Create documents internally or separately. That way, when you're over a year and then you go back and do the 2nd version, your chance to make a mistake
will be greatly reduced.
-mrs_helm
2. Use common English (or any other language) rather than coding
Why do we often use codes (such as the supply code for 9935A may be the ink pen, 4xf788-q may be the Book of accounts
Code)? There are many reasons. But users often think in English instead of coding. The accountant who worked for 5 years might know
4xf788-q is something, but new arrivals are not necessarily. When you create a drop-down menu, list, report, it's best to follow English
Name sorting. If you need to code, then you can attach the code next to the user knows English.
-amasa
3. Save Common Information
It is useful to have a table that stores general database information specifically. I often hold the current version of the database, recently checked/repaired
Complex (access), the name of the associated design document, customer, and so on. This allows you to implement a simple mechanism for tracking data
Library, when customers complain that their database does not meet the desired requirements and contact you, do this for non-client/server environments
Particularly useful.
-richard Foster
4. Testing, testing, repeated testing
After you have established or revised a database, you must test the data field with the new data entered by the user. Most importantly, let the user test
Try and work with users to ensure that the data type you choose meets your business requirements. Testing requires putting the new database into the actual service
Before completion.
-juneebug
5. Inspection design
A common technique for checking database design during development is to check the database through its supported application prototypes. Other words
The prototype application for each final expression data ensures that you examine the data model and see how to retrieve the data.
-jgootee
6. Access Design Tips
For complex Microsoft Access database applications, you can put all the primary tables in one database file, but
Add other database files and load special functions related to the original database. Use these functions to connect to the main file as needed
The primary table in. such as data entry, data QC, statistical analysis, providing reports to management or government departments, and various types of read-only
Inquiries and so on. This approach simplifies the allocation of user and group permissions and facilitates the grouping and partitioning of application functions to
It is easy to manage when the program must be modified.
-dennis Walden
