多个数据库表或合并为一个
我有一个关于数据库架构的问题。
我们正在构建一个 CMS。有很多字段会预先填充选择。例如，客户的信用状态可以是“良好”、“不良”、“未知”或“接受存款”。该项目的一个规范是这些预先填充的选择是动态的，管理员可以通过后端添加新值。所以我需要将这些值存储在数据库中。
我正在努力在两种方法之间做出决定
1）为每种列表准备一个表格。例如，list_CrediStatus、list_Branches、list_Markets 等表。
优点是这些表并不大，而且彼此独立。因此，一个表上的数据和查询负载可能不会影响其他表？缺点是数量会很多。也许30？并且每个表都需要一个查询。
2）有两张桌子。有一个描述表，您可以在其中定义所有不同的列表名称（list_CreditStatus、list_Branches 等）。有另一个表包含所有列表的所有值以及将每行链接到描述表中的标识符的外键。
优点是表少，1次查询，格式统一。缺点可能是性能方面。这张表需要被多次查询。它将有很多行和大量数据。
有人有什么建议吗？我倾向于选项 2。如果这没有意义，请告诉我。这是一个很难写清楚的问题。
谢谢， 杰德
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
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
始终将相似的内容放在一张表中，将不同的内容放在不同的表中。这意味着您选择选项 1。请记住：基于字段名称的表面相似性并不意味着它们是相似的。
单表解决方案极具吸引力，因为它看起来更简单，但事实并非如此。您需要将它们分开的代码变得非常复杂。
另外，您无法使用正确的外键。您怎么说订单有一个引用列表的 CREDIT_STATUS 列，但不允许有人输入血型（或其他）值？
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?
我会为每种类型准备一个表。为什么 ？除其他原因外，您可能会很容易地发现这些数据类型逐渐积累专门针对该类型的附加信息。如果所有内容都合并到一张表中，那将很难满足。
（免责声明：我曾经开发过这样一个系统，其中包含月份、日期、商品类型、真/假（是的！）、假期日期等。它基本上是一个巨大的杂项袋，类似于 仁智天商场）
不用担心每个表的查询。这就是数据库所擅长的，我不会太早优化。直到您遇到问题/问题为止。
I would have a table per type. Why ? Amongst other reasons, you may easily find that these data types gradually accrue additional info specifically for that type. If everything is consolidated into one table, that's going to be very difficult to cater for.
(disclaimer: I've worked on such a system with a table containing months, days, types of commodity, true/false (yes!), holiday dates etc. It was basically a huge miscellaneous grab bag and resembled the Celestial Emporium of Benevolent Knowledge)
Don't worry about queries per table. That's what databases are good at, and I wouldn't optimise too early. Not until you've got problems/issues.
你所说的太糟糕了，它实际上有一个名字。这就是通常所说的反模式——一种需要避免的软件开发模式。
名称是一个真正的查找表 。
我添加了一个链接，使用该术语您会找到其他链接。
表格很好，引用完整性也很好。
需要时同时使用两者。
what you are talking about is so bad that it actually has a name. It's what is generically known as an anti-pattern - a pattern of software development to be avoided.
the name is the One True Lookup Table.
I included one link, use that term you'll find others.
tables are good, referential integrity is good.
Use both when needed.
在我看来，您好像在谈论域约束表，但我可能是错的。如果您确实在谈论域约束，那么您希望每个域约束都位于其自己的表中。这两个表都是可靠的，
并且都比通过使用整数作为主键来实现域约束的表更好（IMO）。 （因为每个这样的表都需要额外的联接来获取人类可以使用的信息。）
It sounds to me like you're talking about tables of domain constraints, but I could be wrong. If you are, indeed, talking about domain constraints, you want each domain constraint in its own table. Both these tables are defensible,
and both are better (IMO) than a table that implements a domain constraint by using an integer as its primary key. (Because each such table requires an additional join to get information humans can use.)