问题描述
我有一个关于数据库架构的问题。
我们正在建立一个CMS。有很多字段将有预先填充的选择。例如，客户的信用状态可以是好，坏，未知或存款。项目的规范是这些预先填充的选择是动态的，管理员可以通过后端添加新的值。因此，我需要将这些值存储在数据库中。
我很难在两种方法之间做出决定
1 ）为每种类型的列表有一个表。示例将是list_CrediStatus，list_Branches，list_Markets等表。
优点是表不是巨大的，它们彼此分离。因此，一个表上的数据和查询负载可能不会影响其他表？缺点是会有很多。也许30？每个表都需要一个查询。
2）有两个表。有一个描述表，您可以在其中定义所有不同的列表名称（list_CreditStatus，list_Branches等）。另一个表包含所有列表的所有值，以及将每行连接到描述表中的标识符的外键。 p>
优点是更少的表，1查询和统一格式。缺点可能在于性能。这个表需要被查询很多。它将有许多行和大量的数据。
有没有人有任何建议？我倾向于选项2.也让我知道如果这没有意义。这是一个很难写的清楚的问题。
谢谢，
Jed
事物在一个表和不同的东西在单独的表。这意味着你可以选择1.记住：基于字段名称的表面相似性并不意味着它们就像事物。
表解决方案是诱人的吸引力，因为它看起来更简单，但它不是。您需要保持这些单独的代码变得相当复杂。
此外，您不能使用正确的外键。你如何说ORDER有一个列CREDIT_STATUS引用列表，不允许有人丢弃血型（或其他）值？
I have a question about database architecture.
We are building a CMS. There are a lot of fields that will have pre-populated selections. An example is Customer's Credit Status can be "Good","Bad","Unknown", or "Take a Deposit". A spec of the project is that these pre-populated selections be dynamic, that the admin can add new values via the backend. So I need to store these values in the database.
I am struggling to decide between two approaches
1) Have a table for each kind of list. Example would be tables like list_CrediStatus, list_Branches, list_Markets, etc.
Advantages are that the tables are not huge and they are separate from one another. Therefore data and query load on one table might not effect the others? Disadvantages are that there will be many of them. Maybe 30? And that there will need to be a query per table.
2) Have two tables. Have a description table where you get to define all the different list names (list_CreditStatus, list_Branches, etc.) Have another table contains all the values of all lists plus a foreign key that links each row to its identifier in the description table.
Advantages are less tables, 1 query and uniformed format. The disadvantages might be in performance. This table will need to be queried a lot. It will have many rows and a lot of data.
Does anyone have any advice? I am leaning towards Option 2. Also let me know if this does not make sense. It was a hard question to write clearly.
Thanks,Jed
Always keep like things in one table and unlike things in separate tables. this means you go with option 1. Remember: A superficial similarity based on field names does not mean they are like things.
The single-table solution is seductively appealing because it looks simpler but it is not. The code you will need to keep these separate becomes quite complicated.
Plus, you cannot make use of a proper foreign key. How do you say that an ORDER has a column CREDIT_STATUS that references the list table, without allows somebody to drop in a Blood Type (or some other) value?
这篇关于多个数据库表或组合成一个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！