Classification of reference types in databases
When designing any database, it is always necessary to store a sea of reference information. All sorts of classifiers lists of goods, people
I refer to the reference books all the information that is needed solely for a beautiful output to the user. And as an option from her it will be necessary to take a couple of tsiferok. Any (weight of the box). Inside the application there will still be all sorts of IDs and constants. When designing from them, you can abstract away and assume that they simply are and will be shown as necessary.
One of the solutions to which everyone comes sooner or later is not to create any structure for reference books at all, but to keep everything in a kind of universal structure. Which looks great on the classes in the application.
')
EAV model
Many things have been written about her on the Internet. But the main idea, subject to practice, is to use it only in the case when user-defined entities are necessary. This is such a rare class of projects that they are not even worth talking about.
In any other case, we get an unreadable, slow-working structure. Without any advantages.
Classic directories
• Non-Extensible Directories
Example: The gender of a person.
The advantages of selecting this class are that we immediately, at the design stage, determine how much and what values will be in them.
Thanks to this, we can design the user interface accordingly, write CASE on all possible options, and so on. It is always easier to work with a limited number of values in the directory than with an unlimited.
For example:
If you make the directory extensible, this design will not work. It is necessary to make additional tables, write additional requests.
It is easiest to do non-extensible directories simply by constants in the corresponding fields of objects. No need for tables, keys, nothing. For integrity control, we impose a restriction on this field listing all possible options.
• Expandable directories
Example: Country of residence
The advantages of issuing this class are that we will not require the assignment of a unique code for each new value. We will not need such codes at all.
There is a great temptation to make a table with a list of all countries. And implement the selection from the list. In the best case with the search for the first letter. But in practice it turns out that 95% of people will be from Russia. And the remaining 5% from ten other countries. And this entire list of hundreds of items will never be fully used.
Where it is better to make a regular text field for input, and show the list filtered by the first letters. Estets can get a button showing the entire list at once. The list itself is generated by all previously entered values.
Expandable directories are best done simply by text attributes. Control of duplicates will be carried out thanks to the drop-down list of suitable options. Well, it's quite good to add to the administrator the ability to create synonyms of the Russian Federation = Russia for autochange.
As with non-extensible directories, it is not necessary to make separate tables, keys. No need to check the integrity, and think about how to store changes. They are expressed up to the usual attributes of the objects.
• Directories worthy of individual tables
Example: Products
This type includes all directories that have their own attributes or refer to something. We make for them separate tables.
They need to design, think. There is no general approach.
Codes
Sometimes it becomes necessary to store many Code-Name links. Under such links it is best to allocate a separate table. And in all other tables store only codes. This approach is fully compatible with this reference book storage system. For example, for a non-extensible directory, you can add Codes (“M”, “F”, “H”) and their corresponding names (“male”, “female”, “UFO”)
UPD: Criticism heard. Article updated.
I refer to the reference books all the information that is needed solely for a beautiful output to the user. And as an option from her it will be necessary to take a couple of tsiferok. Any (weight of the box). Inside the application there will still be all sorts of IDs and constants. When designing from them, you can abstract away and assume that they simply are and will be shown as necessary.
One of the solutions to which everyone comes sooner or later is not to create any structure for reference books at all, but to keep everything in a kind of universal structure. Which looks great on the classes in the application.
')
EAV model
Many things have been written about her on the Internet. But the main idea, subject to practice, is to use it only in the case when user-defined entities are necessary. This is such a rare class of projects that they are not even worth talking about.
In any other case, we get an unreadable, slow-working structure. Without any advantages.
Classic directories
• Non-Extensible Directories
Example: The gender of a person.
The advantages of selecting this class are that we immediately, at the design stage, determine how much and what values will be in them.
Thanks to this, we can design the user interface accordingly, write CASE on all possible options, and so on. It is always easier to work with a limited number of values in the directory than with an unlimited.
For example:
CASE
WHEN sex = '' then caption = ''
WHEN sex = '' then caption = ''
WHEN sex = '' then caption = ''
END
If you make the directory extensible, this design will not work. It is necessary to make additional tables, write additional requests.
It is easiest to do non-extensible directories simply by constants in the corresponding fields of objects. No need for tables, keys, nothing. For integrity control, we impose a restriction on this field listing all possible options.
• Expandable directories
Example: Country of residence
The advantages of issuing this class are that we will not require the assignment of a unique code for each new value. We will not need such codes at all.
There is a great temptation to make a table with a list of all countries. And implement the selection from the list. In the best case with the search for the first letter. But in practice it turns out that 95% of people will be from Russia. And the remaining 5% from ten other countries. And this entire list of hundreds of items will never be fully used.
Where it is better to make a regular text field for input, and show the list filtered by the first letters. Estets can get a button showing the entire list at once. The list itself is generated by all previously entered values.
Expandable directories are best done simply by text attributes. Control of duplicates will be carried out thanks to the drop-down list of suitable options. Well, it's quite good to add to the administrator the ability to create synonyms of the Russian Federation = Russia for autochange.
As with non-extensible directories, it is not necessary to make separate tables, keys. No need to check the integrity, and think about how to store changes. They are expressed up to the usual attributes of the objects.
• Directories worthy of individual tables
Example: Products
This type includes all directories that have their own attributes or refer to something. We make for them separate tables.
They need to design, think. There is no general approach.
Codes
Sometimes it becomes necessary to store many Code-Name links. Under such links it is best to allocate a separate table. And in all other tables store only codes. This approach is fully compatible with this reference book storage system. For example, for a non-extensible directory, you can add Codes (“M”, “F”, “H”) and their corresponding names (“male”, “female”, “UFO”)
UPD: Criticism heard. Article updated.
Source: https://habr.com/ru/post/36735/
All Articles