SQL规范化
我有 4 张桌子、法庭、分区、地址和电话号码。
每个法院可以有一个或多个分庭，民事分庭、刑事分庭、家庭分庭等。这些组合中的每一种都可以有一个或多个地址，即特定法院的民事分庭可能位于也可能不位于与刑事法院不同的实际地址。电话号码也同样，各部门或地址可以相同，也可以不同。
我考虑过几种方法，但不知道最好使用哪种？
a) 我可以有一张大表，它可以多次复制大部分数据。
b) 我考虑过在分区表中保留法院的FK，在地址表中保留分区的FK。这意味着在分区表中复制每个球场的分区值。然后我不知道如何处理电话桌。
c) 我的最终想法是使用一个链接表来保存 FK 的所有其他 4 个表。这意味着不会复制数据，但意味着需要进行大量联接才能获得任何有意义的内容，并且可能难以使用。
我确信我忽略了这里显而易见的事情，但是对这些表进行建模的最佳方法是什么？
这些表将通过 CakePHP 访问，但也许我们暂时将其放在一边。
I have 4 tables, courts, divisions, addresses and telephone_numbers.
Each court may have one or more divisions, civil, criminal, family etc. Each of these combination's may have one or more addresses i.e. the civil division for a particular court may or may not be at a different physical address to the criminal court. The same applies to the telephone numbers, they may be the same for each division or address, or they may be different.
I have considered several approaches to this, but do not know the best to use?
a) I could have one big table, which would replicate much of the data many times.
b) I have considered holding the FK of the court in the division table, and the FK of the division in the address table. This means duplicating division values for each court in the division table. I'm then not sure what to do with the telephone table.
c) My final though was to have a single link table holding the FK's all all 4 other tables. This would mean no replication of data, but would mean a lot of joins to get anything meaningful out, and would probably be unwieldy to use.
I'm sure I'm overlooking the obvious here, but what is the best way to model these tables?
The tables will be accessed through CakePHP, but perhaps we'll leave that to one side for the moment.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
如果计算四个表的所有组合的行数少于 10,000 行，我建议仅使用平面单表结构。如果行大小约为 8K，则数据大小约为 80 meg。大多数免费或低成本数据库都会在内存中保存这么多数据，并使您的 I/O 成本接近于零。
如果您觉得需要多个桌子，那么这里的设计应该可以让您灵活地扩展您的设计。您可以将应用程序特定的列添加到显示的表中。
TypeList 表可以保存法院类型，例如州、联邦、地方法院的类型，条目由 TL_Table 键入“法庭”的价值。部门列表将存储在此处，TL_Type 为“部门”。使用 TL_Type 可以填充数据输入的下拉列表。电话号码的类型列表可能是随叫随到、传真、主要、前台、文员、警长等。
无论您决定采用哪种方式，我相信都会有人尖叫负面评论，但如果它有效，他们只会尖叫着加入空间。
If you will have less than 10,000 rows counting all combinations of the four tables I would suggest just a flat single table structure. The data size would be around 80 meg if the row size was about 8K. Most free or low cost databases will hold that much in memory and place your I/O cost to near zero.
If you feel you want multiple tables then here is a design that should give you flexibility to expand your design. You would add application specific columns to the tables shown.
The TypeList table could hold the types of courts such as State, Federal, Local with the entries being keyed by the TL_Table value of "Court". The list of divisions would be stored here with the TL_Type of "Division". Using the TL_Type one could populate the drop down list for data entry. The type list for telephone numbers might be on-call, fax, primary, front desk, clerk, sherrif, etc.
Which ever way you decide I am sure someone will have a screaming negative comment but if it works they will just be screeming in space.
你所描述的是一个星型模式，并且是建议的方法，
我认为你应该看看你需要做什么查询来获取数据，尽管第一步只是描述而不是sql
what you describe is a star schema and is the advised way to do it
i think you should look at what queries you need to do to get the data out as you need it although just a description not sql would be the first step