Database table and field names, design
Set the table for a field (in oracle example)
For any field length should not be too stingy, or unknown cause before and after the change sets should be amended
1, the switching field proposal number (1,0) instead of varchar2 (1), to avoid user error Save Y / N, rather than the 1 / 0, this case may introduce problems (agree)
2, coding for the data dictionary field, not petty identified as 3, the best uniform for the 32-bit experience has shown, sometimes by the data access from other systems the impact of the absence of the control items, will directly save the original value, while the original values are generally 5-10 bit unification of the benefits of 32-bit, you can consider using the GUID, to generate data Dictionaries are encoded, so that the merger gives them the upper data. (very much agree)
3, for general input fields, such as: number, axle number, number, not for a moment of "absolute" and setting determines the length, preferably into the optimum length of the uniform, such as 32-bit!
Such as: license number was originally six, was soon changed to 7! Axis start No. 8, but actually No. 15 shaft! Bearing a number from 10 to 20
4, for a similar name fields: such as Name, data dictionary name of the project, etc., then a large number of the best, set to 60!
5, the type of field for the Notes, the general content of about 30 Chinese characters, it recommended setting is 100
6, for the long text field, the general content of about 200 Chinese characters, recommended setting for the 1000
7, save the SQL statement on the field (special circumstances, such as the configuration transport conditions, etc.), at least set to 2000, the maximum is 4000
8, the digital field, unless the precision, unity for the number is a better choice (if necessary, a good accuracy as far as possible ahead of time to consider possible changes)
number default precision is 15 bits (integer digits + = 15 decimal places, decimal point location of any), other large values can also be saved, but is used in scientific notation, there is loss of precision with a number, do not specify the precision of the largest benefit is not limited precision and range value if the designated number (2,1), then into the value range between -9.9 to 9.9, if the user to adjust for the two proposed accuracy, you need to modify the database and the program!
9, to date no statement on the type of
Second, the principle of selection keys and indexes
A key principle of selection:
1) 4 key design principles to create a foreign key related fields.
All keys must be unique.
Avoid the use of composite keys.
Foreign key is always associated with a unique key field.
2) Use the system-generated primary key
When designing the database using system-generated key as the primary key, then the index of the actual control of the integrity of the database. Thus, the database and non-interrupting mechanism to control the storage of data in each row access.
Generated by the system as the primary key and a key advantage: when the button has the same structure, is easy to find flaws in logic.
3) Do not use the user's key (primary key is not to be updated of)
In determining what the field with the key as the table when the users can be careful to edit the field. Usually do not choose where the user can edit the field as the key.
4) Optional key key sometimes call the shots call the shots with the optional key to further key, the index can have a strong ability to build.
2 index using principles:
Index is to obtain data from the database one of the most efficient manner. 95% of the database performance problems can be solved using indexing technology.
1) the logical primary key using the unique group index, keys on the system (as stored procedures) using only non-group indexes, foreign key column for any non-group index. Consider how much space a database, how to access the table, there are these visits are mainly used for reading and writing.
2) Most databases index the primary key fields automatically created, but do not forget to index the foreign key, they are also frequently used keys, such as run queries to display the main table and a table of all records related to need them.
3) Do not index memo / note field, not an index of large fields (there are many characters), to do so will make the index take up too much storage space.
4) Do not index small tables used for small data tables not set any keys, if they often do not even insert and delete operations did it. Insert and delete operations on the index maintenance may consume more than scanning the table space of time.
Three other design
(1) All fields in the design, in addition to the following data type timestamp, image, datetime, smalldatetime, uniqueidentifier, binary, sql_variant, binary, varbinary, must have default values.
The default value is a null character value character string''; numeric value default value is 0; logic-based default value 0; which: the system type in the value of all logic 0 is represented as "false"; value 1 expressed as "true." datetime, smalldatetime type field in no default, must be NULL.
(2) When the field is defined as a string type to use varchar instead of when the proposed nvarchar.
(3) the table should not be stored depends on the other key non-key information
Four of experience in database table design
Oracle database table is one of the most basic objects. Lofty towers from the ground, the base object for the database is very important. Because its design is reasonable, performance-related directly with the database. Rookie from Oracle database to a database of experts in this process, design and management in the table, more or less, will make some mistakes. I today to talk about their experiences in this area and lessons may be able to give you some warning.
Experience One: In the design of large database, to allow NULL columns in the back on the table.
Table in the database design process, some requirements must be non-empty fields, such as the table of keywords, document number field and so on. In the database table creation, it is often necessary to set these fields non-null. The case, you can force the user to input data to enhance data consistency.
Meanwhile, in a table, there will be many fields, such as employee information table, the staff of love, notes and other fields, it may allow for the air. Using database terminology, these fields is no contents "NULL" field.
When the database table design for this type of empty fields to be special attention. On the one hand, the NULL field, not to say that it is the space inside the store. If the use of "''" sign to check the empty field, then the records are often the result of finding out what we want. When stored in the database because, if the field is NULL, then this value is not stored. On the other hand, precisely because of this property above, so, in table design, the best field to allow NULL on the end of the table, when the database is larger or more empty fields, then you can greatly reduce the database storage space.
In addition, when creating tables in the database is established in accordance with the time to sort out the. So, when a column is set up, unless you delete and then set up, or can not adjust the column order. This requires the database administrator create the table before, it is necessary to the column, and whether merely having a clear definition. Only then can a reasonable arrangement order of the columns.
Experience 2: develop work hard to write this comment.
Determine whether a person is a database of experts, from a "Notes" on the little habits can be seen. If a time in database development, diligence and write Note , such as tables or fields in the establishment of the time to make use of comment fields in the command table and the notes, then Ke Yi Shang Panduan from Yidingchengdu aspect of the expert he is Zhe. On the contrary, if the development of the database, comments do not have much, you basically can judge, he is a rookie just getting started. Or that its not participated in the design of large databases.
This is mainly because, in the database development process, is often not a person can complete. ERP in the development of a database such as when there may be some people responsible for developing basic form, but some people responsible for maintaining the view, and so on. We are a division of labor. Meanwhile, in the foreground there are programmers need to call back the database tables and fields. So, in a large database and application systems are often the result of collaboration you.
To this end, in order to improve the readability of tables and fields, the development of a database management table, we can see to understand, we need to table and field to a more detailed comment. In the Oracle database in order to provide meaningful other partners, help information, they can use the Comment command description table, field role and invoking the precautions. This little command, you can greatly improve the readability of the table and field and improve database and application development team collaboration.