Database Design Guide (2) Design tables and fields

Design tables and fields
1. Check for various changes
When designing a database, I will consider which data fields may change in the future. For example, the last name is like this (note
It refers to the surname of a westerners, for example, the surname of a female after marriage ). Therefore, when establishing a system to store customer information, I tend
Store the Last Name field in a separate data table, and add fields such as the start date and end date to track this field.
Change of data entries.
-Shropshire glad
2. Use meaningful field names
One time I participated in the development of a project with programs inherited from other programmers, the programmer liked to use the screen
It is not a good idea to display data indicator naming fields, but unfortunately, she also prefers some strange naming methods.
A combination of Hungary naming and serial number control, such as cbo1, txt2, and txt2_ B.
Unless you are using a system that only targets your abbreviated field name, please try to clarify the field description as much as possible. Of course.
Too much. For example, although Customer_Shipping_Address_Street_Line_1 I is descriptive, no one wants
Enter such a long name.
-Lamont Adams
3. prefix naming
If multiple tables have multiple fields of the same type (such as FirstName), you may use the prefix of a specific table (such
CusLastName) to help you identify fields.
-NotoriousDOG
Timeliness data should include the "Last updated date/time" field. The time mark is used to locate the cause of the data problem and relocate the problem by date.
It is particularly useful to manage/reload data and clear old data.
-Kol
4. Standardization and data-driven
Data Standardization not only facilitates yourself but also facilitates others. For example, if your user interface needs to access external data
Source (files, XML documents, other databases, etc.), you may wish to store the corresponding connection and path information in the user interface support table
. In addition, if the user interface executes tasks such as workflows (sending emails, printing letterhead, and modifying record statuses ),
Workflow data can also be stored in the database. There is always a need to make effort in advance, but if these processes adopt
Data-driven, not hard-coded, makes policy change and maintenance much easier. In fact, if the process is data-driven
, You can put a considerable amount of responsibility to the user, the user to maintain their own workflow process.
-Tduvall
5. standardization cannot overhead
For those who are not familiar with the normalization term, standardization can ensure that the fields in the table are the most basic.
And this measure helps eliminate data redundancy in the database. There are several forms of standardization, but Third Normal
Form (3NF) is generally considered to have the best balance in terms of performance, scalability, and data integrity. In short, 3NF rules
Set:

· Each value in the table can be expressed only once.
· Each row in the table should be uniquely identified (with a unique key ).
· The table should not store non-key information dependent on other keys.
Databases that comply with the 3NF standard have the following features: a group of tables specifically store the associated data connected by keys. For example,
A 3NF database that stores customers and their related orders may have two tables: Customer and Order. The Order table is not a package.
Contains any information about the Customer associated with the order, but the table stores a key value that points to the Customer table containing the Customer information
.
There are also higher levels of standardization, but will higher standards be better? The answer is not necessarily. In fact, for some projects
Even 3nf can introduce too much complexity to the database.
-Lamont Adams
For the sake of efficiency, table standardization is sometimes necessary. There are many such examples. There was a development finance Analysis
A non-standardized table is used to reduce the query time from an average of 40 seconds to about two seconds. Although I have to do this,
However, I will never regard Non-standardization of data tables as a design concept of course. The specific operation is just a derivation. So if the table
It is entirely possible to re-generate a non-standardized table.
-Epepke
6. Microsoft Access Report skills
If you are using Microsoft Access, you can replace the numbered name with a user-friendly field name. For example
Customer name replaces txtcnam. In this way, when you use a wizard to create a form and report, its name will make
It is easier for programmers to read.
-Jwoodruf
7. inactive or inactive indicators
Add a field to indicate whether the record is no longer active in the business. Customers, employees, and others
This will help you filter the active or inactive status when you run the query again. It also eliminates the need for new users
Some problems encountered during data deletion. For example, some records may not be used by them any more.
Preventive effect.
-Theoden
8. Use the role entity to define columns of a certain type
When you need to define a thing that belongs to a specific category or has a specific role, you can use the role entity to create a specific time threshold.
Links to achieve self-documenting.
The meaning here is not to allow the PERSON object to carry the Title field, but to explain why the PERSON entity and
What about the PERSON_TYPE object to describe a person? Then, for example, when John Smith, Engineer is promoted to John
Smith, ctor and finally climb to the high position of John Smith and CIO. All you have to do is change two tables.
The key value of the relationship between PERSON and PERSON_TYPE, and a date/time field is added to know when the change is made.
Occurred. In this way, your PERSON_TYPE table contains the possible types of all persons, such as Associate,
Engineer, ctor, CIO, or CEO.
Another alternative is to change the PERSON record to reflect the change of the new title, but this way the time cannot be tracked.
The specific time when the individual is located.

-Teburlew
10. Use the data of common entity naming organizations
The simplest way to organize data is to use common names, such as PERSON, ORGANIZATION, ADDRESS, and
PHONE and so on. When you combine these common names or create specific sub-entities, you get
Your own special version. At the beginning, the main reason for using general terms is that all specific users can have abstract things
Physical.
With these abstract representations, you can use your own special names in the 2nd-level identifiers. For example, PERSON may be
Employee, Spouse, Patient, Client, Customer, Vendor, or Teacher. Similarly,
ORGANIZATION may also be MyCompany, MyDepartment, Competitor, hospization,
Warehouse, Government, etc. The last ADDRESS can be Site, Location, Home, Work,
Client, Vendor, and FieldOffice.
The use of general abstract terms to identify the category of "things" allows you to gain tremendous flexibility in associating data to meet business requirements.
In addition, this can significantly reduce the amount of redundancy required for data storage.
-Teburlew
11. users from all over the world
When designing databases that use networks or other international features, remember that most countries have different fields.
For example, in some countries, such as New Zealand, there is no postal code.
-Billh
12. Separate data tables are required for data duplication.
If you find that you are inputting data repeatedly, create a new table and a new relationship.
-Alan Rash
13. Three useful fields should be added to each table
· DRecordCreationDate. In VB, the default value is Now (), and in SQL Server, the default value is GETDATE ()
· SRecordCreator, which defaults to not null default user under SQL Server
· NRecordVersion indicates the record version. It helps to accurately describe the original data that contains 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 the street address in just one line. Address_Line1, Address_Line2, and Address_Line3
To provide greater flexibility. In addition, it is best to have your own data table with your own phone number and email address.
And tag category.
-Dwnerd
Be careful about over-standardization, which may cause performance problems. Although the separation of address and phone table can usually achieve
The best status, but if you need to frequently access this type of information, you may store the "preferred" information in the parent table (such
Customer. The compromise between non-standardization and accelerated access is of some significance.
-Dhattrem

15. Use multiple name fields
I am surprised that many people leave a field for name in the database. I think this is only available to beginners.
But in fact, this approach is very common online. We recommend that you treat the last name and first name as two fields.
Combine them during query.
-Klempan
Klempan is not the only person who notices that a single name field is used. It is better to make this situation more user friendly.
Methods. I usually create a computing column in the same table and use it to automatically connect the standardized field.
It also changes when the data changes. However, this is a clever way to use modeling software. In short, connect
Fields can effectively isolate user applications and developer interfaces.
-Damon
16. Use case-insensitive object names and special characters
One of the most annoying things in the past is that the database has a mix of case-insensitive object names, such as CustomerData. This question
Questions from Access to Oracle Database exist. I don't like the case-insensitive object naming method.
You do not need to manually modify the name. Think about it, can such databases/applications mix into the day when more powerful databases are used? Full adoption
The name with uppercase letters and lower delimiters is more readable (CUSTOMER_DATA ).
Leave spaces between characters.
-Bfren
17. Retain words with caution
Make sure that your field name does not conflict with the reserved words, database systems, or common access methods. For example
There is a table in the ODBC Connection Program, where DESC is used as the description field name. The consequences can be imagined! DESC is
The reserved word after DESCENDING. A select * Statement in the table is usable, but I get a lot
Useless information.
-Daniel Jordan
18. Maintain the consistency of Field Names and types
Ensure consistency when naming a field and specifying its data type. Assume that a field is called
"Agreement_number", you should change the name to "ref1" in another table ". Assume that the data type is in a table
Is an integer, so do not change it to the struct type in another table. Remember, when you finish your work, others need to use your data.
Database.
-Setanta
19. Carefully select the number type
Be especially careful when using smallint and tinyint types in SQL. For example, if you want to see the total monthly sales, your total
If the segment type is smallint, you cannot perform computation if the total amount exceeds $32,767.
-Egermain
20. Delete tags
The table contains a "delete tag" field to mark rows as deleted. Do not delete it separately in the relational database.
A row; it is best to use the data clearing program and carefully maintain the integrity of the index.
-Kol

21. Avoid using triggers
The functions of a trigger can be implemented in other ways. When debugging a program, the trigger may become interference. If you do need to collect
With triggers, you 'd better document them in a centralized manner.
-Kol
22. Include version Mechanism
We recommend that you introduce a version control mechanism to the database to determine the version of the database in use. In any case, you must implement this goal.
. After a long period of time, users' needs will always change. In the end, you may need to modify the database structure. Although you can pass the check
New fields or indexes are used to determine the database structure version, but I found that the version information is directly stored in the database.
Right ?.
-Richard Foster
23. leave sufficient margin for text fields
Text fields of the ID type, such as the customer ID or order number, should be set to be larger than expected, because the time is not long for you
Most of them will be embarrassed to add additional characters. For example, assume that your customer ID is 10 digits long. Then you should
Set the field length of the database table to 12 or 13 characters. Is this a waste of space? It's a bit, but it's not what you think.
So much: a field with three characters longer has 1 million records, and a little index is added to make the entire data
The database occupies 3 MB of space. However, this extra space can be occupied without restructuring the entire database in the future.
.
-Tlundin
24. Column naming skills
We found that if you use a uniform prefix for the column names of each table, you will get a large value when writing SQL expressions.
Big simplification. This operation also has some disadvantages. For example, if the function of the automatic table Connection Tool is damaged, the latter places the public column names with some numbers.
Database connection, but sometimes these tools are not connected incorrectly. For a simple example, suppose there are two tables:
Customer and order. The prefix of the customer table is Cu _, so the sub-segment names in the table are as follows: cu_name_id,
Cu_surname, cu_initials, and cu_address. The prefix of the Order table is or _, so the sub-segment name is:
Or_order_id, or_cust_name_id, or_quantity, and or_description.
In this way, the SQL statement for selecting all 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;
If these prefixes are not available, they are written as follows:
Select * from customer, order
Where customer. Surname = "myname"
And customer. name_id = order. cust_name_id
And order. Quantity = 1
The number of characters in the 1st SQL statements is not small. But if the query involves five tables or more columns, you will know this technique.
More useful.
-Bryce Stenberg