Several forms of database design with many-to-many relationships _ MySQL

[Switch] database design several forms of multi-to-multi-link bitsCN.com

Many-to-many relationships require at least three tables. we call one table a master table and the other a relational table, the other is called a dictionary table or sub-table (the sub-table has few records and is basically stable, for example, the forum name; the sub-table has many contents and the content changes, for example ).
According to the database addition, deletion, query, and modification operations, you can use inner join or select * from primary table where id in (select primary table id from relational table) for multiple link searches)

1. Role appointment

Features: the foreign key combinations of relational tables do not have repeated records. generally, a relational table does not require a time field or a primary key. one table is a dictionary table.
Interface features: displays the master table, and uses checkbox or multiple select statements to set the multi-choice relationship.
For example, you can appoint a moderator (user table-relational table-Forum name table), role permission control, and so on. if you are a member of five Forum versions, you can create a five-row link table record, the two foreign keys of the relational table are associated with primary keys.
Add link: if no combination record exists, insert it.
Delete link: if a combination record exists, delete it.

2. set group type

Features: similar to the role appointment type, the foreign key combination of the relational table does not have repeated records, and the relational table generally does not require the time field and primary key. The difference is that the primary and secondary tables are not dictionary tables, and they may be quite unfixed.
Interface features: displays the master table, and replaces the simple checkbox or multiple select statements with search, or adds one by one.
For example, a song album (album table-relational table-song table ). Mobile phone group (group table-relational table-mobile phone table ). User circle (circle table-relational table-user table ). Article tag (article table-relational table-label table)
Add link: same moderator appointment type.
Delete link: same moderator appointment type.


3. Ledger

Features: a relational table can have repeated records. a relational table generally has time fields, primary keys, and text fields to indicate the cause of each link (consumption ).
Interface features: displays the relationship table and uses radio or drop-down to set the single-choice relationship.
For example, a cash consumption statement or an order (user table-order table-consumption reason table) may be repeatedly consumed on the same thing for multiple times. Point change records also belong to this category.
Add relationship: whether there is a combination of records, insert, record time.
Delete link: Delete a link based on the link table PK.


4. Comment Reply type

Features: The same as the primary account relationship table generally has a time field and a primary key. The difference is that the fields in the text font are used to describe the content of each link (Comment reply ).
Interface features: reply text box.
For example, if you reply to a forum (user table-reply table-Post table), you may post comments on different posts for multiple times.
Add relationship: whether there is a combination of records, insert, record time and text.
Delete link: Delete a link based on the primary key of the link table (Reply table.

5. intra-site SMS

Features: The primary and secondary tables are the same, and the relational tables generally have time fields and primary keys. the emphasis is on the text fields in the relational tables to describe the content (message) of each link) or other markup positions are used to indicate the time when the text is read.
Interface features: reply text box.
For example, for intra-site text messages (user table-SMS table-user table), users may send a group of messages or send a single message, with tags indicating the time when the text has been read.
Add relationship: whether there is a combination of records, insert, record time and text.
Delete link: Delete a link based on the primary key of the link table (Reply table.

6. user friend

Features: The primary and secondary tables are the same, with the same set grouping type. the foreign key combinations of the relational tables do not have repeated records. the relational tables generally do not need time fields and primary keys.
Interface features: displays the master table in the same set grouping type, and replaces the simple checkbox or multiple select statements with search, or adds one by one.
For example, a file from a download site (file table-relational table-file table) can be opened by a software tool. the software tool itself is also a file that can be downloaded. A user's friend is also a user (user table-friend relationship table-user table)
Add link: same moderator appointment type.
Delete link: same moderator appointment type.


7. unknown attribute type

Features: At the initial stage of design, when some field types and names of the primary table are uncertain, the relational table is actually an extensible field of the primary table,
A [primary table] (ID ),
A [attribute name table] (attribute ID. attribute name ),
A [attribute value table] contains three fields:
Attribute Value (attribute Value varchar (500 ))
Primary table ID
Property ID

In this way, the minimum redundancy can be achieved.
(Different from common many-to-many relationships: values are stored in varchar, because these values are generally not used for calculation ).

For example:

There is a kind of material in the database design of the military called "war materials", that is, the materials are seized during war, and the military itself does not know the attributes of these materials.

For example, the seized chemicals include chemical names, general names, whether there is radiation, measurement units, packaging specifications, quantities, and so on, or whether the chemicals are other unknown things.
In this way, you can

Something strange. attribute set ["XX strange attribute name"] = "XX strange value ";
A metamorphosis. attribute set ["XX abnormal attribute name"] = "XX abnormal value ";

Storage in this way.

For example:

There are thousands of mobile phone models. in addition to the common attributes, there are hundreds of different attributes. the attribute names and value types are different. some mobile phones have this attribute, while others do not.
For such "polymorphism", we adopt the above design structure.
The effect is equivalent:

A strange mobile phone. property set ["XX strange property name"] = "XX strange value ";
Abnormal mobile phone. attribute set ["XX abnormal property name"] = "XX abnormal value ";

Interface features: when setting the attributes of a row record in the master table, you need to list all possible attribute names, each corresponding to a text box.

The purpose of this summary is to make a general background.
As long as there are:
1. common maintenance for a single table (1-2 types ).
2. common one-to-multiple relationship maintenance (1-2 types ).
3. general multi-to-multi-link maintenance (7-10 types ).
4. general tree relationship maintenance (2-3 types ).

Basically completed 80% of the background work.

In addition, all projects are common. if a team has multiple projects at the same time, it can save a lot of time.

BitsCN.com