Database Design-multi-value Storage

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.

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, and store multiple values together. Separate values with 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.

Database Design tables with multi-value Attributes

This design is not feasible. It is mainly because the ID in a table is unique and there is no way to keep multiple records later.
Split the table into two tables and associate them with one column.
1: nametb: id int (10), name varchar2 (20), serialno int (10)
2: teltb: name varchar2 (20), tel number (11)
Table 1 is unique, and table 2 is multi-row.
You can achieve your goals.

Database storage problems

For a fixed table, the space occupied by each field is fixed. For integer fields, no matter how large the value is or how small the occupied bytes are, for string fields, no matter how long the length is and how short the number of bytes is, the part whose length exceeds the defined width cannot be stored, and the part whose length is lower than the defined width is wasted.

For a dynamic table, the length of the string type field affects the size of the physical space occupied. If the length exceeds the defined length, the storage space is saved if the storage content is lower than the defined maximum length.

Generally, as long as a field in the table is changed to a string type (VARCHAR), the table is a dynamic table, and all string-type (CHAR) fields are actually longer. On the contrary, if there is no VARCHAR field, the table is fixed.

For dynamic tables, it can save storage space, but the speed will slow down. The principle is very simple, especially when the length of a modified record changes, the system has to do a lot of things.

The above dynamic and fixed values are only for the string type. If your table stores integers, they must be fixed. Regardless of the size and size of the stored integer, the occupied space is the same.

In addition, for a field that can be empty, this field occupies more space, and the physical storage of this field requires a record where the content is empty. However, for fields that can be empty, if they are fixed tables or non-string fields in Dynamic tables, the occupied space is fixed, only in the string type fields of the dynamic table, empty strings occupy less space than strings with content.