列表项目表的SQL DB模式最佳实践 [英] SQL DB schema best practice for List item table
问题描述
我有一张桌子说 Table1 ，其中有以下列
I have a table say Table1 which has following columns
1. Id
2. Name
3. TransportModeId
4. ParkingId
5. ActivityId
第3、4、5列是外键，所有这三个都是简单的列表表，具有以下列
Column 3,4,5 are the foreign keys and all three are simple list tables which has following columns
1. Id
2. Item
为简单起见，我显示了3个表，否则我的实际模式几乎包含25列表。
For simplicity I have shown 3 tables otherwise my actual schema contains almost 25 List table.
最佳做法是什么
选项1。
将所有列表表分开，这将创建25个表，但另一方面，我将拥有一个干净的模块化架构
Option 1. Keep all list table separate which will create 25 tables but on the other hand i will have a clean modular schema
选项2。
创建一个具有自我联接的表，并添加该表中的所有项目，其中 ParentId null将代表该表的名称，并且它可以有多个如上所述在其他表格中的引用，并且必须将其保存在某种通用模块中
Option 2.
Make a table with self join and add all the items in that table in which ParentId null will represent the name of the table and it can have more than one references in other tables as described above and it has to be kept in some kind of common module
谢谢
推荐答案
选项1是设计最终用户/实现者不应该非常配置的系统时通常如何完成的方式。它具有几个重要的优点，其中两个：
Option 1 is the way how it is normally done when designing a system that is not supposed to be very configurable by end user/implementator. It has several important advantages, two of them:
-
当您需要为任何枚举添加额外属性时（例如停车位置到停车枚举），它非常简单，不会产生额外的问题。
when you need to add an extra attribute to any of the enumerations (e.g. parking location to the Parking enumeration), it is quite simple and does not produce extra problems.
它使用关系数据库引擎的本地算法进行链接以优化速度记录。
It is optimized for speed using relation database engine's native algorithms for linking records.
至于选项2：
这就是所谓的通用化。您可以采用具有相似属性（方法）的更多类型，并创建具有适合不同目的的结构的类/表。
As for Option 2: It is something called Generalization. You take more types with similar attributes (methods) and create a class/table with a structure that fits different purposes.
如您所言，自引用不是选项2的好主意，而是引用另一个 EnumerationType 表包含类型名称，如 Parking ， Activity 等，其ID为ID。
The self reference, as you speak about it, is not a good idea for Option 2, rather make a reference to another EnumerationType table containing type names like Parking, Activity etc. with id.
使用这种方法可能很有意义，以防您需要使最终用户能够自己在应用程序中配置属性。但是，否则，当您发现不同的枚举表需要具有不同的结构时，可能会导致您遇到问题。
Using this approach could make sense in case you need to enable end user to configure the attributes himself within your app. But otherwise it could cause you problems when you find out, that different enumeration tables need to have different structures.
这篇关于列表项目表的SQL DB模式最佳实践的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
