Thinking about the dictionary table and the instance table

Why introduce dictionary tables and instance tables

First, let's look at a small example to understand why you should introduce a dictionary table and an instance table.

Scenario One: If you are playing a game, you suddenly explode a very rare weapon, if the developer designed a table like this: Table
inst_equip (
  ID,
  userId,
  outfit name,
  equipment picture,
  equipment Properties,
  ...
);
This doesn't seem to be a problem, just add a record to the Inst_equip table. Then we'll look at the next scene.

Scenario Two: If this outfit is suddenly presented to 10,000 players, then the
problem comes ... The Inst_equip table of the database needs to be added 10,000 times to the information of this outfit when adding records.
However, this is not fatal, if the system thinks the equipment is too bug, to modify a field of it, then the records of the 10,000 Inst_equip table will have to make the same changes to each one ...

Solution Solutions

The game developer decided to pull out the information from the equip table, set up table
Dict_equip (
    ID,
    outfit name,
    equipment picture,
    equipment properties,
    ...
);
In this case, the Inst_equip table becomes like this: Table
inst_equip (
    ID,
    userId,
    equipid,       //game dictionary ID
    ...
);

Benefits

In this case, if you need to modify the equipment properties, only one record of the Dict_equip record needs to be modified. Sort of like a combination in Java.

what are dictionary tables and instance tables

Having said so much, what is a dictionary table and an instance table

? Dictionary table: Simply put, the user's various operations do not have an impact on their records (increased deletion), it holds some information that is not relevant to the user. Example: Dict_equip (Equipment dictionary table)

instance table: Conversely, the instance table is a bit of information that is closely related to the user's information. Example: (inst_equip) Player equipment Instance table

Thinking

What are the benefits of such a table?
(1) First, the logic is more clear, because the system is for the user Service, so the table is divided into the user-related tables (instance table), the user is not related to the table (dictionary table);
(2) Second, it is mentioned above to facilitate the maintenance of the dictionary table data (increased deletion operation);
(3) Furthermore, the efficiency of query numbers is higher than the query string, and the storage space is smaller (the storage of string duplication will cause the waste of space);
(3) Finally, the redundancy of data in the database is reduced. Meet the requirements of the third paradigm.