Database table design details

2010-03-23  来源:本站原创  分类:Database  人气:268 

The principle of building a database table

Database table and field names, design

SQL Optimization - Index

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.

  • Database table design details 2010-03-23

    The principle of building a database table Database table and field names, design SQL Optimization - Index 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

  • About the value of encoding used to store an unlimited hierarchical tree structure of the database table design 2009-02-05

    About the value of encoding used to store an unlimited hierarchical tree structure of the database table design, here is detail. Here I tried to use relatively short text, few graphics, and related core sql statement to describe the design: First, we

  • Tree structure to facilitate efficient query the database table design 2011-05-04

    Option One: Parent ID, recursive queries. Option Two: Parent ID + front coding, string prefix matching queries. Option Three: about the value of coding, so numerical information. About the value of encoding used to store an unlimited hierarchical tre

  • About the database table design 2011-08-02

    Seen a lot of large software database table design, the following conclusions. Most of China's design is based on database tables to the business, if you do not understand the business would like to understand the specific meaning of the database tab

  • Steps on the database table design 2010-03-31

    The design of the database table 1, the first based on relevant business needs (mainly refer to output and input conditions) planning out the basic structure 2 state of the field based on business rules design 3, according to the company or departmen

  • Database table design 2010-10-19

    1. Original documents and the relationship between entities is one to one, many, many to many relationship. In general, they are one to one relationship: the one and only original documents corresponds to a corresponding entity. In exceptional circum

  • Database table design for the back-end business processes of 2011-07-14

    Is crucial, but how can one obtain the optimal design of the database table? I can not do. Confused. Analysis of the demand is not clear? Did a thorough understanding of business processes? Too little experience?

  • Authority structure of the database table design 2010-07-19

    Core Tip: - permission license create table res_permission (roleid INTEGER, resourceidvarchar2 (30), operationid integer, primary key (roleid, resourceid, operationid)) - role definition create table res_role (roleid INTEGER, rolenamevarchar2 (30), r

  • Database table design company 2011-05-11

    When designing the table structure, some field names are not very standardized so as to cause ambiguity, since it would take a lot of time to maintain, in this design, some to follow the company's current specifications: Field does not appear in capi

  • Company database table design specifications 2011-05-11

    When designing the table structure, some field names are not very standardized, then easily lead to ambiguity, but also spend a lot of time after the maintenance, in this design, some companies have to comply with current specifications: Field does n

  • Database table design techniques during the class get the application 2011-07-08

    1 Background Large number of database design portability problems if other aspects of the database if oracle, mysql, sybase database, date, datetime, but there may be oracle mysql sybase may not have not In this case, our unity with varchar (23) that

  • On database design skills (table design) 2010-11-16

    To facilitate their reading, some deletion. Transfer: Computer program = data structures + algorithms. Object-oriented program development, the first thing to do, the first analysis to be addressed throughout the

  • Large forum such as the design of the database table structure 2011-03-02

    Do not consider this issue first, according to business requirements to design. After completion of performance testing, and then determine whether to optimize the database, according to the performance test results to develop optimal solutions. You

  • Table design principles 2010-09-24

    Table design principles 1) Should not design a database for the entire system, but rather components of the system architecture by , For each component of business handled by the database design for component units : Between different components corr

  • Oracle Table Design Precautions 2010-10-19

    Oracle database table is one of the most basic objects. From the lofty tower from the ground, the base object for the database, it is very important. Because its design is reasonable, performance-related directly with the database. To the database fr

  • Design of the database table structure 2010-06-03

    Article Design of the database table structure 2008-05-29 17:51 As described in this article title, just like to talk about the design of the database table itself, also referred to the relevant table structure and the performance and scalability pro

  • petshop4.0 Xiangjie bis (data access layer for database access design) 2010-03-27

    In series 1, I analyzed the whole PetShop architecture design, and referred to the hierarchical concept. From the beginning of this section, I will turn on the layers to make code-level analysis, in order to obtain more detailed and in-depth understa

  • DatabaseMetaData usage (transfer) method to obtain the database table structure 2010-03-10

    DatabaseMetaData usage (transfer) 2008-08-07 at 12:25 1. Be an instance of this object Connection con; con = DriverManager.getConnection (url, userName, password); DatabaseMetaData dbmd = con.getMetaData (); 2. Methods getTables usage <br /> prototy

  • Data table design principles 2010-03-21

    1) should not be for the entire system database design, but should be based on system architecture of the components division, for each component of the operations handled by the database design component units; between different components of the co

  • sphinx in the cluster statistics and data table design to achieve 2010-04-12

    Opening: sphinx is a simple but very powerful search plug-in based on a mysql package. 1 search speed slightly faster than lucene, indexing speed on word sub-word than lucene tools such as: IK, paoding so fast. (Personal usual practice of speaking) 2