Turn: Multi-to-many relationship design in database

Ext.: http://www.cnblogs.com/xuqiang/archive/2010/08/30/1953515.html

http://xuqiang.github.io/.

[Email protected].

Several forms of many-to-many relationships in database design
Foreword: Many-to-many relationships require at least 3 tables, we call a table the main table, one is called the relational table, the other is called a dictionary table or a sub-table (The dictionary table is relatively small, and basically stable, such as: The name of the section, the table is more content, content changes, for example).
In accordance with the database of additions and deletions to check the operation, many-to-many relationship lookup can be used inner JOIN or SELECT * from the main table where ID in (select Main Table ID from the relationship table)

1, role-appointed

Features: Relationship table Two foreign key combinations have no duplicate records, the relationship table generally does not require time fields and primary keys, there is a table is a dictionary type table.
Interface Features: Displays the main table and sets the multi-select relationship with a checkbox or multiple selection select.
For example: appoint Moderator (User table-Relationship table-section name table), role permissions control, etc., the user is a 5-section moderator, as long as the relationship table 5 Row Records can be established, the relationship table of two foreign keys have a federated primary key nature.
Add Relationship: If there is no combination record, insert it.
Delete Relationship: If there is a combination record, delete it.

2, set Group type

Features: Similar to the role appointment type, the relationship table two foreign key combinations have no duplicate record, the relational table generally does not need the time field and the primary key. The difference is that the primary and secondary tables are not dictionary tables, and may be very large and non-fixed.
Interface Features: Display the main table, use search instead of a simple checkbox or multi-select Select, or add a single bar.
For example: Song album (album Table-Relationship table-song table). Cell Phone grouping (grouping table-Relationship table-Mobile phone table). User Circle (Circle table-Relational table-user table). Article tags (article table-Relational Table-label table)
Increase relationship: With moderator appointment type.
Delete Relationship: With moderator appointment type.

3, Detailed account type

Features: The relationship table can have a repeating record, the relationship table usually has a time field, there is a primary key, there may be a text-type field to explain each occurrence of the reason (consumption).
Interface Features: Display the relationship table, set the single-selection relationship with radio or dropdown.
For example: Cash consumption statement or order (user table-order form-consumption reason table), users may repeatedly spend on the same thing. The change of points record also belongs to this category.
Add Relationship: No matter the combination of records, insert, record time.
Delete Relationship: Based on the relationship table PK Delete.

4, comment reply type

Features: The same as the detailed Ledger relationship table usually has a time field, there is a primary key, the difference is focused on the text type of the field used to describe each occurrence of the relationship (comment reply).
Interface Features: Reply text box.
For example: Forum reply (User form-reply form-post table), users may comment on different posts on the response fee.
Add Relationship: No matter the combination of records, insert, record time and text.
Delete Relationship: Based on the Relationship table (reply table) PK Delete.

5, in-Station SMS

Features: The main sub-table is the same, the relationship table generally has a time field, there is a primary key, focusing on the relationship table type of the field used to describe each occurrence of the relationship (message) or other marker bit to indicate the text read state time, etc.
Interface Features: Reply text box.
For example: in the station SMS (User table-SMS Table-user table), the user may send the user mass or single, there is a marker to indicate the text has been read status time.
Add Relationship: No matter the combination of records, insert, record time and text.
Delete Relationship: Based on the Relationship table (reply table) PK Delete.

6, user friend type

Features: The main sub-table is the same, the same set grouping type, the relationship table two foreign key combinations no duplicate records, the relationship table generally does not need time fields and primary keys.
Interface features: Grouped by the same set, display the main table, with a search instead of a simple checkbox or multi-select Select, or the addition of one bar.
For example: Download the file of the site, (File table-Relational table-file table) can be opened by software tools, software tools itself is a kind of file, can be downloaded. User's friend, also User (user table-Friend Relationship table-user table)
Increase relationship: With moderator appointment type.
Delete Relationship: With moderator appointment type.

7, unknown attribute type

features: In the early stages of design, when certain field types and names of primary tables are indeterminate, the relational table is actually an extensible field of the main table .
a [primary table] (ID),
A [Property name table] (property ID. Property name),
A [attribute value table] that includes 3 fields:
Property values (property value varchar ($))
primary table ID
Property ID
This makes it possible to minimize the degree of redundancy.
(unlike common many-to-many relationships, values are stored uniformly with varchar, because values of this type are generally not used for calculations).
For example:
the Army's database design has a kind of material called "War to pay the material", is seized when the war, the army does not know what these materials have the property.
For example, seized chemicals have chemical names, generic names, whether they have radiation, units of measure, packaging specifications, quantities, etc., or not chemicals are anything else unknown.
so it can be.
Something strange. Property Collection ["A Strange attribute name"]= "a Strange value";
Something perverted. Property set ["A Perverted attribute name"]= "a perverted value";
this is stored.
Another example:
There are thousands of types of mobile phone models, in addition to the common attributes there are hundreds of different attributes, attribute names and value types are not the same, some phones have this property, and some do not.
for this "polymorphic", we use the above design structure.
the effect is equivalent to:
a strange cell phone. Property collection ["A Strange attribute name"]= "a Strange value";
a sick cell phone. Property set ["A Perverted attribute name"]= "a perverted value";
interface Features: When setting the property of the main table row record, all possible attribute names are listed, each corresponding to a text box.

Turn: Multi-to-many relationship design in database