Database Design-EAV (entity, attribute, value), eav entity

Database Design-EAV (entity, attribute, value), eav entity

There is such a business that is used by customers to record what they do every day. Because it is very professional, professional record books are required. There are more than 20 such records. In actual work, there are also 20 different records, and the format of the records changes a bit every other year. How to design databases?

There are two solutions:

1. Create a separate table for each record.

2. A dynamic table stores the attributes of the record book in a dictionary. The content of the record book is stored in the form of objects, attributes, and values.

-- Stores the format of the record book

Createtable note_dictionary

(

Dictionary_idnumber,

Dictionary_typevarchar2 (50 ),-- The type of the record book, that is, more than 20 records

Dictionary_namevarchar2 (200)-- Name in the record book

);

-- Stores the content of the record book

Createtable note_content

(

Content_idnumber,

Note_id number,-- The id of a specific log book

Dictionary_idnumber,-- Id of the field in the record book

Contentvarchar2 (1000)-- Field content

);

From the developer's perspective, solution 2 is selected. Solution 1: how much work is required. Every time you change the format of the record book, you have to adjust the format. solution 2 has an extended type. You do not need to change the code to adjust the format, the two tables in the partition area solve the problem of more than 20 tables. There is no dispute over option 2.

After solution 2 is selected, the benefits of designing code for many traditional databases will be sacrificed. If the log book is related before, the implementation will become more complex.

1. The field types in the record book cannot be specified. For Date and number fields, they can only be represented by varchar2. If the type selection is incorrect, I wrote it in my previous blog. In addition, the statistics may also have problems. Even if you control the data well before, you cannot guarantee that the data is correct after it is finally stored in the database. This problem is seen too much.

2. Constraints and default values of not null cannot be added.

3. If you want to query field a of a record, you need to first look for the dictionary and then look for it in the content table. This is not intuitive and involves the trouble of maintaining data in the future.

4. If there is a relationship between the records, you need to add a table to establish the Association, which is complicated.

5. After a period of time, the content table will become very bulky, and the system has a large amount of data.

6. I have previously maintained this design code, because it is common and sometimes changed here, I don't know if it will affect other places. Reflection is used in Java code. debugging is difficult to debug and the problem is difficult to locate. In general, there is a sense of being inferior to dead.

7. All complex queries become complex.

I prefer solution 1 for the above modules. The development workload is large, but the maintenance workload is small, and the data is more accurate. Isn't solution 2 necessarily recommended? No,Business, business, or businessIf your documents do not require statistical analysis, the data volume is not large, there are many types of documents, and the level of developers is very high, and you want to configure the record book, you can choose solution 2. If you do not have a detailed understanding of the business and stick to solution 2, you must bear the risks I mentioned above.

Determining the entity, attributes, and relationships in Database Design

What does it mean? A student is an entity, a course is an entity, a student's student ID is an attribute, a course name is an attribute, and a student's Course Selection Record is a link. You should not have asked this question.

Database terminology: Single-value and multi-value Attributes

Simply put, a single value attribute can only be a value.
The multi-value attribute can have one or more values.

For example:
There is an Employee entity (employeeID, name, sex, age, tel, departID), where employeeID is the Employee number, name is the Employee name, sex is the Employee gender, age is the Employee age, tel is the employee's phone number and records the employee's mobile phone number and office phone number.
(1) A. name, which can be differentiated by employeelD
B. sex. No action is required.
C. tel. You can add the employee ID of tel to an object independently.
D. tel. Only one phone number can be recorded.

Tel is a multi-value attribute because it can be a mobile phone number or an office phone number !!

Attribute:

Properties and categories are defined separately. Each attribute is defined only once, but can be used in multiple categories. For example, the "Description" attribute can be used in multiple categories, but it only needs to be defined once in the architecture to maintain data consistency.

Attribute is used to describe objects. Each attribute has its own definition, which describes the information type specific to this attribute. Each Attribute in the architecture can be specified in the "Attribute-Schema" category, which determines the information that each Attribute definition must contain.

The attribute list that can be applied to a special object is determined by the category (the object is an instance of the category) and any super class of the object category. The attribute is defined only once, but can be used multiple times. This ensures that all categories shared with the same attribute can be consistent.

Multi-value Attributes

The attribute can be single or multi-value. The schema definition of the attribute specifies whether the attribute instance must be multi-value. An instance with a single value attribute can be empty or contain a single value. An instance with multi-value attributes can be empty or contain a single value or multi-value. Each value of a multi-value attribute must be unique.

Index attributes

Indexes are applied to attributes rather than categories. Indexing an attribute helps you quickly query objects with this attribute. When you mark an attribute as "indexed", all instances of this attribute will be added to this index, instead of simply adding instances that are members of a specific category to the index.

Adding indexed attributes will affect the replication time, available memory, and database size of Active Directory. Because the database has become larger, it takes more time to copy the database.

Multi-value attributes can also be indexed. Compared with the index of a Single-value attribute, the index of a multi-value attribute further increases the size of Active Directory and requires more time to create objects. When selecting the attribute to be indexed, make sure that the selected shared attribute is balanced between overhead and performance.
An indexed architecture attribute can also be searched by containers that store the attribute, thus avoiding searching the entire Active Directory database. This not only shortens the time required for searching, but also reduces the amount of resources required during the search.