Dictionary table Design

========================================================== ========================================================== ========================================================== ==========

 

Dictionary table design and application examples in response to requirements of Zhifeng brothers, today I took some time to write something about dictionary table design. By the way, I would like to use a small application to experience the design as a use case.
Let's take a look at what a dictionary is.
Time is tight. Let's talk about it later.
The necessity of dictionary existence and its benefits.
Same as above ^_^
Dictionary design ideas.
The dictionary information serves as the basic parameter in the system. Basically, some important basic information should be maintained by the system administrator before the system serves the formal business, some dictionary information is maintained by the system administrator or other users during use.

That is to say, we have the need to "maintain dictionary information", so we need to consider this need when designing the structure of the dictionary information table.
During maintenance, we certainly hope to maintain the dictionary information in different classes, so we need to design a dictionary category table (dic_type) with the following structure:
ID (Dictionary type ID)
Name (Dictionary type name)

For specific table data features, see the following example.
With a category table, we also need a specific data table (dic_data) that stores dictionary information for each category ):\

Atid (automatically increasing ID, no practical effect)
Typeid (ID of the dictionary category to which dictionary information belongs)
ID (Dictionary information ID, which is used in the Program)
Name (Dictionary Information Content)

In this way, in our business information table, the value of the dictionary-related field is the ID value in dic_data, which involves displaying information on the interface, the displayed information must be the ID of the original dictionary information, which is certainly not what the user wants. Based on this requirement,
We create a view for each type of dictionary information (For details, refer to the example below ),
Associate the information table with the corresponding view to obtain the real content corresponding to the dictionary information Id.

Application Example.
Fake and customized student information management system
The dictionary type table is designed as follows (dic_type ):
ID name
1 sex
2...

The dictionary content table is designed as follows (dic_data ):
Atid typeid ID name
1 1 male
2 1 2 female
3 2 1...
4 2 2...
5 2 3...
6 2 4...
............

Gender dictionary view (vw_sex ):
Select ID, name from dic_data where typeid = 1

Assume that the student information table is t_student:
ID name... sex
1 collection... 1
2 flowers... 2
3 just now... 1

You can use the following method to retrieve the student list information:
Select t_student.id as studentid, t_student.name as studentname, vw_sex.name as sexname
From t_student left join vw_sex on t_student.sex = vw_sex.id

The result is as follows:
Studentid studentname sexname
1 A male
2 B female
3 c female

 

The time is too short and the writing is too rough. Please forgive me and try again later...

Green Channel: Please follow my favorites to contact me

 

========================================================== ========================================================== ========================================================== ==========

 

There are two ways to design a database dictionary table. One is the traditional method. Each dictionary table has two fields: ID and name. The second method is to place data from all dictionary tables in the same table. The structure is as follows:

Typetable (typeid, typename) [master table, used to record dictionary table name information]; datatable (typeid, dataid, dataname) [slave table, record all dictionary table data information]

For example, gender and marital status, there are two records in typetable: {02, Gender}, {06, Marital Status}. In datatable, there are three records in each table: {, 0, female, male/, others },{, unmarried/, married/, divorced}

Another patient list (patientid, sexid, marrystatusid ...)

To query patient information, run the following SQL statement:

Select B. dataname as sexname, C. dataname as marrystatusname

From Patient A left join datatable B on B. dataid = A. sexid and B. typeid = '02'

Left join datatable C on C. dataid = A. marrystatusid and B. typeid = '06'

When you execute this SQL statement in a database, because the datatable contains about 1000 pieces of data, the first method will definitely affect the database.

(Of course, in actual business, there may be about 5-10 similar dictionary tables and about patient data records)

However, I don't know how much data is retrieved from all dictionary tables in a system, which affects the database performance and reduces the performance by a few percent? Will there be other hidden risks?

========================================================== ========================================================== ========================================================== ==========

The upstairs may not understand what the landlord meant.

It does not mean that the education and nationality tables have a large amount of data, but that the personnel table may have too many attributes (not necessarily the personnel table or other entities here, that is, as the complexity of the system increases, the attribute of the object increases ). Here, we take personnel as an example to describe the attributes of nationality and education. If a person still has a position, there must be more ranks. If there are other...

That is to say, when we get the full data of an instance, we will join dozens of tables. This problem should be solved by the author.

Person_info (person_id, name, country_id, education_id, position_id ,....)
Country (country_id, name ,...)
Position (position_id, name ,...)
Education (education_id, name ,....)
...

Therefore, the landlord adopted another design method:
All attribute classes (attributes are also entities, but only attributes of the primary table) are placed in a table and distinguished by attribute names and attribute values.
Persion_info (persion_id, name ,...)
1 aaa
2 bbb
Attributes (attributes_id, persion_id, attributes_name, attributes_value)
1 1 country China
2 1 Education Primary School
3 1. Head of position
4 2 Country USA
5 2 Master's degree in education
6 2 postion DBA