Some problems in database design _ database

Premise statement, personal opinion:

There is no best, only the most suitable.
For different perspectives, the so-called "most suitable" is also different.
Design is always accompanied by "compromise".

Please do not try to prove that the personal opinion is "best" in the discussion.

Everyone put forward their own experience, ideas, lessons and so on, so that the people involved in the discussion according to their own conditions (which we can not fully envision for others), have a choice to get "I need."
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

The following are for transaction-type databases:
1. Whether to use a federated primary key. Individuals tend to use fewer joint primary keys. Because this lowers the efficiency of the index, a federated primary key typically uses at least one business field, often a string type, and theoretically a multiple-field index is slower than the index of a single paragraph. It doesn't seem to be that refreshing either.
In the actual design, I try to avoid using a federated primary key, and sometimes "have to" use a federated primary key.

2.PK the use of meaningless fields (logical primary key) or meaningful fields (business primary key). Individuals tend to "logical primary key", the reason is that the design of the database model structure clear, the relationship between clear, often more in line with the "third paradigm" (although not intentional, hehe). And it's easier to avoid "federated primary Keys", and you can use index-efficient field types, such as int, long, and number. Disadvantage is the use of meaningless fields to establish the relationship between tables, so that the query increased across the table, the efficiency of the decline. (Contradictions are everywhere, just said before can improve efficiency, here immediately and reduce efficiency). "Business primary Key" can improve the simplicity and efficiency of the query coding.
Personal use of the actual situation, the overall "logical primary key" than "business primary key" to perform less efficient, but not too low to meet the requirements. The use of "logical primary key" is more advantageous to the structure of database model, clearer relationship and easier maintenance than adopting "business primary key".
Never do this for analytical databases, such as data warehouses.

3. Do not use many-to-many relationships. Individuals tend to use less many-to-many relationships. This problem is not a database design problem, in the database design, the many-to-many relationship is only in the logic model (E-R) phase, the physical model is not a many-to-many relationship, the actual database will not have "many-to-many" relationship. This is a problem with ORM, such as using Hibernate, which sometimes makes coding look more flexible, at the cost of a significant reduction in efficiency.
The actual use of the individual, the design of the basic do not consider a many-to-many relationship, but the code will always have a group of members to use a number of many-to-many relationship, their own set up many of the ORM, so that their coding more convenient, used in the small amount of data in the place, the impact is not. Large amount of data, then "Prohibit use".

4. Add a state field to each table. I'm used to setting up a state field for each table at design time, with a value of 0 or 1, and a default value of 1, which can be customized for specific business or operational significance. Can be used as a state control field, such as a query, update, delete condition, whether the document is valid (business document corresponding table will have the business sense of the "have/invalid" or "status" field, in which case, I will add a state field), or even just control whether a piece of data is "valid" (The effective meaning of your own set). It may also be useful in data migrations, such as when transferring to a database used for analysis.

5. Set up some alternate fields for each table. No way, I always design a "perfect" datasheet, add a few alternate fields to each table (I usually use string type, as you can) to cope with "rainy time", especially the need for long-term maintenance, the business may have temporary changes in the system.

6. Try not to deposit in a table the fields of its associated table. The proposal does not exist. Doing so can improve query efficiency, but it is difficult to maintain data consistency in a situation where there are many tables and many associated tables. The database structure is also very bad. And does not save, also does not make the efficiency very low.

7. Do not modify the database directly. Personally think this is important, when you need to change, you should first modify the model, and then synchronize the physical database, especially the team development, or else to do more to deal with, may also introduce more errors.

Comment on the 17 floor Ylt 2006-09-04 Hasi wrote Liu_w_j wrote that the relationship between the tables is best not to have a closed loop (not to refer to the loop), otherwise the operation of the relationship between the tables will be very complicated
Because no one's going to be so designed.

There's a good chance of that. For example, a parent object has multiple child objects, and a frequently needed operation is to get the first child object of the parent object. Then it is possible to produce the following design:
Java Code table Parent ID first_child_id table Child ID order_id parent_ Id

This creates a closed loop. Of course this is a bad design, would rather do more than one query. The 16 floor Hasi 2006-09-04 Liu_w_j wrote that the relationship between the tables is best not to have closed loops (not to refer to loops), otherwise the operation of the relationship between the tables will be very complex
Because no one's going to be so designed. The relationship between the tables of the 15 Liu_w_j 2006-08-16 best not to have closed loops (not to refer to loops), otherwise the operation of the relationship between the tables will be very complex 14 floor throws an abnormal love 2006-07-31 about Union primary key
I think of a way
You can use the Equlse method to
Wrap two or three fields that should be combined with equlse

I don't know if this is going to be a bad idea. 13 Floor tianxinet 2006-07-27 in order not to make space too long, not all of the original quote:

This is the case, generally speaking, it is better to use a logical primary key for a business table, whereas for a public base table it is better to use a business primary key.
>> is generally the case. (I have contacted the Bom,bom of the large automobile manufacturing group corresponding to the information system is the basic table, we generally say that there is "process BOM", "Production BOM", but there is even a "development BOM", inherited relationship for "development BOM"-> " Process BOM "->" Production BOM ", are more than hundreds of thousands of of the data, from the point of view of the table is not a large table, but is a large base table, but is divided into multiple layers (vehicle-> assembly-> Assembly->...-> parts-> materials, Think about how wide it is involved in the process of localization in the so-called "development of the BOM" constantly changing, resulting in "process BOM", "Production BOM" also constantly changing, this situation or the use of logical primary key is better.

The many-to-many relationship is derived from the logical model, but the logical model is the abstraction of the physical model, who does not have a many-to-many relationship in the physical model? In ORM based processing, many-to-many relationships are often simplified into bidirectional one-to-many relationships (relatively speaking, Many-to-many is complex). Whether to prohibit the use of, more lies in the establishment of the model.
>> of course, in terms of abstraction, there are many pairs of physical models. But since it has been from abstraction to concrete, this refers to the specific, do not abstract back to the

It's a good idea, but it's usually more recommended to use character data, such as 0,1,2,4,8, instead of the simple 0 and 1.
>> agree that this is more flexible

A good structure setting is not in an alternate field, but in an extended table. Alternate fields are always finite and type-fixed.
>> I said the standby field is not for "formal" use, in the maintenance process, temporary business adjustment may be used.


For non-process business, that is, the data module is relatively independent, the landlord's suggestion is good, but for the process of business, such as MRP based on orders, supply chain, and so on, specific business information, that is, business fields can best exist in the corresponding subsequent table, light attention to the structure and ignore efficiency is not a desirable way
>> This is a pair of contradictions, I think the most appropriate measure is the performance, resource consumption, contradictions outstanding when the best way is to build a simulated operating environment measured. According to the measured situation, choose a "cost-effective" better solution.

In the order, the business primary key is the order number, orderNo, I do not know the name of the upper or the ID, but as I said earlier, for the Business Type table, it is better to use a logical primary key. However, it should be noted that there are two kinds of meanings of association, one is direct Table association, the other is indirect association established through Multi-layer Association. For the former, the landlord's proposal is right, but for the latter, it is not necessarily. Often in the actual system is the need to sacrifice the structure in exchange for efficiency.
>> Yes, sacrifice structure for efficiency is often done, sacrificing efficiency for the structure of things do not less do, hehe 12 floor Phoenix Dance 2006-03-03 Tianxinet wrote hanny0918 wrote good, but 6th "try not to deposit in a table its relevance The field of the table. "Landlord how to solve the relationship between the two tables."
Create another table to be the associated table of two tables. Whether this is too much trouble.
Or there is another solution.

Does not refer to a foreign key.

For example, "order form" and "receiving table" through the OrderID to establish a relationship, the receiving list of course to save OrderID This field, but try not to facilitate the "ordername" this field also deposited in the order, the business key is the order number, OrderNo, I don't know the name or ID of the upper floor, but as I said earlier, it's better to use a logical primary key for a business type table. However, it should be noted that there are two kinds of meanings of association, one is direct Table association, the other is indirect association established through Multi-layer Association. For the former, the landlord's proposal is right, but for the latter, it is not necessarily. Often in the actual system is the need to sacrifice the structure in exchange for efficiency. 11 Floor Phoenix Dancing Phoenix 2006-03-03 Tianxinet wrote
The following are for transaction-type databases:
1. Whether to use a federated primary key. Individuals tend to use fewer joint primary keys. Because this lowers the efficiency of the index, a federated primary key typically uses at least one business field, often a string type, and theoretically a multiple-field index is slower than the index of a single paragraph. It doesn't seem to be that refreshing either.
In the actual design, I try to avoid using a federated primary key, and sometimes "have to" use a federated primary key.

Agree!
Reference
2.PK the use of meaningless fields (logical primary key) or meaningful fields (business primary key). Individuals tend to "logical primary key", the reason is that the design of the database model structure clear, the relationship between clear, often more in line with the "third paradigm" (although not intentional, hehe). And it's easier to avoid "federated primary Keys", and you can use index-efficient field types, such as int, long, and number. Disadvantage is the use of meaningless fields to establish the relationship between tables, so that the query increased across the table, the efficiency of the decline. (Contradictions are everywhere, just said before can improve efficiency, here immediately and reduce efficiency). "Business primary Key" can improve the simplicity and efficiency of the query coding.
Personal use of the actual situation, the overall "logical primary key" than "business primary key" to perform less efficient, but not too low to meet the requirements. The use of "logical primary key" is more advantageous to the structure of database model, clearer relationship and easier maintenance than adopting "business primary key".
Never do this for analytical databases, such as data warehouses.
This is the case, generally speaking, it is better to use a logical primary key for a business table, whereas for a public base table it is better to use a business primary key.

Reference
3. Do not use many-to-many relationships. Individuals tend to use less many-to-many relationships. This problem is not a database design problem, in the database design, the many-to-many relationship is only in the logic model (E-R) phase, the physical model is not a many-to-many relationship, the actual database will not have "many-to-many" relationship. This is a problem with ORM, such as using Hibernate, which sometimes makes coding look more flexible, at the cost of a significant reduction in efficiency.
The actual use of the individual, the design of the basic do not consider a many-to-many relationship, but the code will always have a group of members to use a number of many-to-many relationship, their own set up many of the ORM, so that their coding more convenient, used in the small amount of data in the place, the impact is not. Large amount of data, then "Prohibit use".
The many-to-many relationship is derived from the logical model, but the logical model is the abstraction of the physical model, who does not have a many-to-many relationship in the physical model? In ORM based processing, many-to-many relationships are often simplified into bidirectional one-to-many relationships (relatively speaking, Many-to-many is complex). Whether to prohibit the use of, more lies in the establishment of the model.

Reference
4. Add a state field to each table. I'm used to setting up a state field for each table at design time, with a value of 0 or 1, and a default value of 1, which can be customized for specific business or operational significance. Can be used as a state control field, such as a query, update, delete condition, whether the document is valid (business document corresponding table will have the business sense of the "have/invalid" or "status" field, in which case, I will add a state field), or even just control whether a piece of data is "valid" (The effective meaning of your own set). It may also be useful in data migrations, such as when transferring to a database used for analysis.
It's a good idea, but it's usually more recommended to use character data, such as 0,1,2,4,8, instead of the simple 0 and 1.

Reference
5. Set up some alternate fields for each table. No way, I always design a "perfect" datasheet, add a few alternate fields to each table (I usually use string type, as you can) to cope with "rainy time", especially the need for long-term maintenance, the business may have temporary changes in the system.
A good structure setting is not in an alternate field, but in an extended table. Alternate fields are always finite and type-fixed.

Reference
6. Try not to deposit in a table the fields of its associated table. The proposal does not exist. Doing so can improve query efficiency, but it is difficult to maintain data consistency in a situation where there are many tables and many associated tables. The database structure is also very bad. And does not save, also does not make the efficiency very low.
For non-process business, that is, the data module is relatively independent, the landlord's suggestion is good, but for the process of business, such as MRP based on orders, supply chain, and so on, specific business information, that is, business fields can best exist in the corresponding subsequent table, light attention to the structure and ignore efficiency is not a desirable way

Reference
7. Do not modify the database directly. Personally think this is important, when you need to change, you should first modify the model, and then synchronize the physical database, especially the team development, or else to do more to deal with, may also introduce more errors. Very much in favor of. The 10 floor tianxinet 2006-02-26 hanny0918 wrote well, but 6th "try not to deposit a table with its associated table fields." "Landlord how to solve the relationship between the two tables."
Create another table to be the associated table of two tables. Whether this is too much trouble.
Or there is another solution.

Does not refer to a foreign key.

For example, "order form" and "receiving table" through the OrderID to establish a relationship, the receiving list of course to save OrderID This field, but try not to facilitate the "ordername" this field is also deposited in the receiving table 9 floor Micro Swift double fly 2005-12-07 wl95421 wrote me General
Two fields that are often available

One is domain
Another one is stamp (time stamp)

Plus domain is because I do some things like permission
Often used by multiple systems
And sometimes some forms are used in Domain 8. Mini-Swift Double fly 2005-12-06 I generally use the following organizational design to add an internal code to the organizational structure
1 head Office
101 Branch
10101 Branch Department

Many business entities if belong to a certain department, not by the primary key of the department table to association, but directly using the internal code, so you can easily retrieve all levels of agency-wide data 7 floor drama said Qianlong 2005-12-06 study: 6 Floor wl95421 2005-12-05 I General
Two fields that are often available

One is domain
Another one is stamp (time stamp)

Plus domain is because I do some things like permission
Often used by multiple systems
And sometimes some forms of things are also used in domain 5 micro-swift double fly 2005-12-04 in no table to add an accurate to the second timestamp field, new, modified to set the value, in tracking bugs too convenient 4 floor Hasi 2005-12-01 CYBERWJW Wrote
I think the change record time, modify the record person generally do not need, you just save the current modification of the person, if the next everyone modify the record, the front cover;
Create a record time, create a record person this depends on the table, if not the important table, I think there is no need to add.

Of course, only the current modification of the person and time, because the previous changes are invalid Ah, the database also has no historical records 3 floor hanny0918 2005-11-30 Good, but 6th "try not to deposit in a table its associated table fields." "Landlord how to solve the relationship between the two tables."
Create another table to be the associated table of two tables. Whether this is too much trouble.
Or there is another solution. 2 floor CYBERWJW 2005-11-30 I also basically agreed to hug the Lord, but the 5th general I will not leave a spare field, if the extra field is increased, also need space to retain Ah, will affect the database, and then if you need to add the field, we can temporarily join, It will not affect future maintenance, nor will it affect the system.
Hasi wrote basically agree with the landlord's point of view,
And we usually create a record time in each table, create a record person, modify the record time, modify the record person four fields.
I think the change record time, modify the record person generally do not need, you just save the current modification of the person, if the next everyone modify the record, the front cover;
Create a record time, create a record person this depends on the table, if not the important table, I think there is no need to add. 1 Floor Hasi 2005-11-30 Basically agree with the view of the landlord,
And we usually create a record time in each table, create a record person, modify the record time, modify the record person four fields.