Logical database Design-cross-road (many-to-many relationships)

I. Introduction to the pattern of disorderly crossing

Programmers typically use a comma-delimited list to avoid creating cross-tables in many-to-many relationships, which is defined as an anti-pattern called a disorderly crossing.

For example: In a product management system, a person can have multiple products, a product must correspond to a person, so there are the following databases:

　　

However, over time, there is a product that may have multiple contacts. So in order to minimally modify the database, probably many people will change the type of account_id to varchar, so that you can list the number of account ID in the column, each ID separated by commas. Such a design seems feasible because no additional tables or columns are created. Simply changing the data type of a field succeeds in achieving the goal. Let's take a look at the disadvantages of doing this. The database foreign key is removed after the change, and the Product_account becomes Varcahr.

　　

1. Inquire the product of the designated account.

Because all foreign keys are merged into a single cell, the query becomes quite difficult. You cannot use commas at this time. Below is a test for all products with account ID 1.

2. Check the account number of the specified product

It is neither elegant nor time-consuming to locate a row of data using a comma-delimited list for long table join queries.

3. Perform aggregate queries

Aggregate queries use SQL built-in aggregate functions. such as count (), SUM (), AVG (). These functions are then designed for grouping, not for comma-delimited lists, and are difficult to use with these aggregate functions.

4. Update the account number of the specified product

You can add a new ID to the end of the list using string concatenation, but this does not make the list stored sequentially.

5. Delete the account of the specified product

6. Verify the Product ID

How to prevent users from entering illegal fields such as ' Apple ' in the Product_account column.

7. Select the appropriate delimiter

If you store a list of strings instead of a list of numbers, some entries in the list may contain delimiters. There may be a problem with using commas as separators. Of course, you can change one character at a time, but even then there's no guarantee that the new character will never appear in the entry,

8, List length limit

How much data can you store in a varchar (50) structure? This depends on the length of each entry, and if each entry is only 2 characters long, you can save more than 10 data, but if each entry is 6, you can save a few.

If your team has said the following, it is likely that you have used a clue to the design pattern of "crossing the Road" in the project.

(1) How many data does the list support?

This problem is mentioned when selecting the maximum length of the varchar column, because the length is not OK .

(2) Do you know how to do a word search in SQL?

If you use regular expressions to extract parts of a string, this might be a hint, meaning you should store the data separately.

　　(3) Which characters do not appear in any of the list entries?

You want to use a split symbol that won't be confusing. However, any character may appear in a value in the field one day.

ii. Rational use of anti-patterns

If your application requires a comma-delimited storage format, it may not be necessary to get individual items in the list. Similarly, if your application receives the source data in a comma-delimited format, and you only need to store and use them and not make any changes to them, you do not have to separate the values.

Third, the ultimate solution-Create a cross-table

Store account_id in a separate table, rather than in the product table, so that each account can occupy a single row. This new table is called Contacts. Achieve a many-to-many relationship between product and account.

The design of the database diagram is as follows:

　　

The rest will not be said, you will find that if it is based on the above 3 table design, then for the above cross-road pattern proposed 8 problems can be easily solved. In addition, querying for contacts.account_id is much more efficient than separating strings in a comma-delimited list. In many databases, declaring a column as a foreign key implicitly creates an index for it.

　In addition, you can add additional attributes to the crosstab, for example, to record the date a contact was added to the product, or the first contact of the product and the second contact. these are not possible in a comma-delimited list.

Logical database Design-cross-road (many-to-many relationships)