EAV of database Design (entity, attribute, value)

There is such a business, used for customer records to do every day, because it is very professional things, need a professional notebook, this record has more than 20 kinds. In the actual work there are more than 20 kind of notebook, the format of the book will change a little every other year. How to design a database?

There are two types of scenarios:

1. Create a separate table for each notebook.

2. Dynamic tables, put the properties of the notebook into a dictionary, the contents of the book are stored in the form of entities, attributes, values.

   -- format of the storage notebook

Create Table note_dictionary

(

dictionary_id Number ,

Dictionary_type varchar2 (), -- The type of the book, that is, a variety of notebook

Dictionary_name varchar2 ($) -- name in the notebook

);

   -- storing the contents of a notebook

Create Table note_content

(

content_id Number ,

note_id Number , -- the ID of a specific notebook

dictionary_id Number , -- ID of the field in the notebook

content varchar2 (+) -- The contents of the field

);

If only from the developer's point of view, Scenario 2 will be selected. Scenario 1 How much workload ah, each change the format of the notebook to make adjustments, Plan 2 has an extension ah, the adjustment format does not need to change the code. No controversial option 2.

After choosing Scenario 2, many of the benefits of traditional database design code are sacrificed, and implementations become more complex if they are previously related.

1. The field type in the notebook cannot be established, and if it is date and number, it can only be represented by VARCHAR2. What's wrong with the incorrect type selection is written in my previous blog. There may also be problems with statistics, even if you control the previous paragraph well, there is no guarantee that the last to the database after the correct, the problem is too much to see.

2. You cannot add a constraint and default value that is not NULL.

3. If you want to check a record of a field, you need to find a dictionary, and then go to the content table to find, not intuitive, involving later maintenance data more cumbersome.

4. If there is a relationship between the records, to establish an association, the need to add a table, the implementation is very complex.

5. After a period of time the content table becomes very cumbersome, the system is its data volume is large.

6. I have previously maintained this design code, because it is universal, sometimes changed here, do not know whether it will affect other places. The use of reflection in Java code, debug is difficult to debug, the problem is difficult to locate. Generally speaking, there is a feeling that the body is inferior to death.

The above module I tend to choose scenario 1, the development workload is a little, but the maintenance workload is small, the data more accurate. Is that a must not use option 2? Also not, business, business, or business , if your documents do not need statistical analysis, the amount of data is not large, the type of documents is very high, the level of developers, you want to do the notebook can be configured, that can choose Scenario 2. If you do not know the details of the business, stick to option 2, you must bear the risks I said above.

EAV of database Design (entity, attribute, value)