I have a question about database architecture.
We are building a CMS. There are many fields that will have pre-filled selections. An example is Good, Bad, Unknown, or Take a Deposit. The specificity of the project is that these pre-populated samples are dynamic, that the administrator can add new values through the backend. Therefore, I need to store these values in a database.
I'm struggling to decide between the two approaches
1) The table has a table for each kind of list. For example, tables such as list_CrediStatus, list_Branches, list_Markets, etc.
The advantages are that the tables are not huge and they are separated from each other. Therefore, loading data and queries on one table may not affect others? The disadvantages are that there will be many of them. Maybe 30? And that there should be a query for each table.
2) Have two tables. There is a description table where you can define all list names (list_CreditStatus, list_Branches, etc.). The other table contains all the values of all lists plus a foreign key that associates each row with its identifier in the description table.
The benefits are fewer tables, 1 query and a unified format. Disadvantages may be in performance. This table should be requested a lot. It will have a lot of rows and a lot of data.
- ? 2. , . .
,