Database Design-multi-value Storage

The problem of storing multiple values is a common problem in database design. I think it is necessary to come up with it if many developers suffer losses. Business scenario: A business document with multiple contacts. One device maintains multiple maintenance teams. The following is an example of createtableBILL (bill_idnumberprimarykey, bill_name

The problem of storing multiple values is a common problem in database design. I think it is necessary to come up with it if many developers suffer losses. Business scenario: A business document with multiple contacts. One device maintains multiple maintenance teams. The following is an example of createtable BILL (bill_id numberprimarykey, bill_name

The problem of storing multiple values is a common problem in database design. I think it is necessary to come up with it if many developers suffer losses.

Business scenario: A business document with multiple contacts. One device maintains multiple maintenance teams. The following is an example.

Createtable BILL

(

Bill_id numberprimarykey,

Bill_name varchar2 (20 ),

Bill_contentvarchar2 (200 ),

Contact_idnumber-- For the user_id of the user table

);

1. In the initial design, there was only one contact. Later, the requirement changed and there were multiple contacts. There are several solutions:

Solution 1: add several fields, contact_id1, contact_id2, contact_id3 ....

Solution 2: change the number type of contact_id to varchar2. Multiple values are stored together and separated by commas ).

Solution 3: Add a table bill_contact

Createtable bill_contact

(

Bill_id number,

Contact_idnumber

);

Altertable BILL_CONTACT

Addconstraint pk_bill_contactprimarykey (BILL_ID, CONTACT_ID );

2. Comparison of several solutions

Solution 1 is obviously not suitable. I do not know how many fields to create. Even if I know that there are at most several contacts, it is very troublesome to query. The query list contains records of contacts 100 and 101,

Select * from bill_contact

Where (contact_id = 100and contact_id1 = 101)

Or (contact_id = 101and contact_id1 = 100 );

Query records that contain contact 100,

Select * from bill_contact

Where (contact_id = 100or contact_id1 = 101 or ....);

The advantage of solution 2 is convenience. Developers only need to modify a small amount of Code and are generally accepted by developers. A. However, the analysis and statistics functions are not long enough. For example, you need to list all the documents of a contact in a certain period of time, and count the number of contacts in each document.

B. the query will also become inefficient. Inconsistent types lead to implicit conversion and index failure.

C. The modification is complex and requires additional logic processing in the code.

D. Some system primary keys use 32-bit UUID. If the contact has 10 more characters, the length of this field is 500, which is a little scary.

Select * from bill_contact

Where contact_idlike '2017, %'

Or contact_id like '%, 100'

Or contact_id like '%, 100, % ';

I previously wrote a special optimization solution for this problem,Multi-value processing of a single field in Database Design

Solution 3 exactly makes up for the determination of solution 2. Developers always worry that the performance of table Association is too poor. In fact, it is redundant because it can be indexed at this time. Another benefit is that you can expand the contact information, such as the first contact or the second contact, which is not feasible in solution 2. Transformation requires more work for developers than solution 2.

Select * from bill_contact a, bill_contact B

Where a. bill_id = B. bill_id

And B. contact_idin (100,101 );

3. How to choose the multi-value problem?

Solution 1 must not be selected.

Solution 2 is suitable for no analysis and statistics on multi-value columns and no query.

Solution 3 is the ideal solution in my mind, although it may cause some workload.