SQL表设计协助
我正在为一个业务团队构建多个基于网络的工具。其中许多工具需要一个受管理的城市名称列表。现在，每个工具都有自己的位置查找表，我想将其集中起来，以便一个位置表可用于所有工具，因此不需要多次添加位置。
我已经创建了主位置表，
-----------------------------
| LocationID | LocationName |
-----------------------------
| 1 | Reno |
-----------------------------
| 2 | San Diego |
-----------------------------
我想添加特定于每个工具的其他字段，但不一定相互关联。我应该创建其他表来管理这些字段，还是根据需要将新字段添加到此位置表中？
我最初的想法是创建表来保存每个附加工具的设置以供参考。
WebTool1 settings table
------------------------------------------------------
| LocationID | HasAirConditioning| HasSecurityGuard |
------------------------------------------------------
| 1 | TRUE | TRUE |
------------------------------------------------------
| 2 | FALSE | TRUE |
------------------------------------------------------
WebTool2 settings table
-------------------------------------------------------
| LocationID | ServerName | RequiresDriveMapping |
-------------------------------------------------------
| 1 | DELLSERVER1 | TRUE |
-------------------------------------------------------
| 2 | HPSERVER3 | FALSE |
-------------------------------------------------------
这是一个好的策略吗？如果没有，为什么？
I'm building multiple web based tools for a business group. Many of these tools require a managed list of city names. Right now, each tool has its own look-up table of locations and I'd like to centralize it so one location table can be used for all tools, so a location doesn't need to be added multiple times.
I've created the master location table
-----------------------------
| LocationID | LocationName |
-----------------------------
| 1 | Reno |
-----------------------------
| 2 | San Diego |
-----------------------------
I'd like to add additional fields that are specific to each tool, but don't necessarily relate to one another. Should I create other tables to manage these fields, or just add the new fields to this location table as the need arises?
My initial thought is to create tables to hold the settings for each additional tool to reference.
WebTool1 settings table
------------------------------------------------------
| LocationID | HasAirConditioning| HasSecurityGuard |
------------------------------------------------------
| 1 | TRUE | TRUE |
------------------------------------------------------
| 2 | FALSE | TRUE |
------------------------------------------------------
WebTool2 settings table
-------------------------------------------------------
| LocationID | ServerName | RequiresDriveMapping |
-------------------------------------------------------
| 1 | DELLSERVER1 | TRUE |
-------------------------------------------------------
| 2 | HPSERVER3 | FALSE |
-------------------------------------------------------
Is this a good strategy? If not, why?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我认为这是一个非常好的策略。
对于数据库，我倾向于先规范化，然后再提出问题……很少会遇到性能问题……在这种情况下，查询语法不会变得更复杂。
唯一的问题是您可能想要构造约束以确保关系保持 1:1。如果它是一个平面表，这不会成为问题，但很难判断哪些字段属于哪个工具。
字段应出现在基表中的时间是它们在所有工具中通用的时间。
我个人喜欢我的数据库结构来反映业务。在我看来，感觉更自我记录。
I think thats a perfectly fine strategy.
With databases i tend to normalise first and ask questions later ... It is very rare that you run into performance problms ... and in this case the query syntax wont get more complicated.
The only problem is that you may want to construct constraints to ensure that the relations stay at 1:1. If it was a flat table this would not be an issue, but it would be harder to tell what fields belong to what tool.
The time when fields should appear in the base table is when they are common across all tools.
I personally like my database structure to reflect the business. It feels more self documenting imo.
如果您有大量额外字段，那么将它们作为一种自然分区进行划分是有意义的。否则，就数据库而言，我认为这并不重要。
从纯模式的角度来看，通过一些内在的逻辑分组而不是应用程序来划分字段可能也更有意义。例如 LocationItInfrastruct 和 LocationBuildingOperations 或类似的而不是 LocationApp1 和 LocaationApp2。
总的来说，我认为无论哪种方法对您个人来说最有意义并且最容易使用，都将是最好的解决方案。
If you have a large number of those extra fields, it would make sense to keep them divided as a kind of natural partitioning. Otherwise, I don't think it really matters one way or the other as far as the database is concerned.
Breaking the fields out by some intrinsic logical grouping instead of application might also make more sense from a pure schema perspective. For instance LocationItInfrastructure and LocationBuildingOperations or somesuch rather than LocationApp1 and LocaationApp2.
Overall, I think whichever approach personally makes the most sense to you and is easiest for you to work with will be the best solution.
这些桌子有多大？如果表包含 1000 行或更少，没问题。但如果是 100K 或更多，您需要考虑如何查询这些表，因为表连接可能会变得很慢。
例如，您想要显示 SERVERNAME = HPSERVER3 和 LOCATIONNAME = RENO 的所有行以及事务表，如果 HPSERVER3 有很多行并且 RENO 也有很多行，则查询计划程序可能会执行嵌套联接，并且查询将是慢的。索引会有所帮助，但与将所有列合并到一个表中相比，它的设置更加棘手。
How big those tables are going to be? If the tables will contain 1000 or less rows, no problem. But if it is 100K or more, you need to consider how you are going to query those tables because table joins can become slow.
For example you want to show all rows with SERVERNAME = HPSERVER3 and LOCATIONNAME = RENO plus your transaction tables, if HPSERVER3 has lots of rows and RENO has lots of rows too, it is possible the query planner will perform nested join and the query will be slow. Index will help but it is trickier to set up compared to combining all columns into a single table.