在几乎每个表中使用相同外键的替代方案
我正在使用一个数据库，其中“几乎”数据库中的每个表都具有相同的字段和相同的值。例如，几乎所有的表都有一个名为GroupId的字段，而现在数据库中只有一个组id。
优点
- 所有数据都与该字段相关，并且可以通过该字段进行标识
- 创建新组时，将正确标识该组的数据
缺点
- 所有表都有此字段
- 所有存储过程都需要此字段作为参数
- 所有查询都有按此字段过滤
这有什么大不了的吗？这种方法还有其他选择吗？
谢谢
I am working with a database where "almost" every table in the database has the same field and same value. For example, almost all tables have a field called GroupId and there is only one group id in the database now.
Benefits
- All data is related to that field and can be identified by said field
- When a new group is created data will be properly identified for the group
Disadvantages
- All tables have the this field
- All stored procedures need to have this field as a parameter
- All queries have to filtered by this field
Is this a big deal? Is there an alternative to this approach?
Thanks
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
如果您将来需要能够识别多个组的数据，那么使用外键是一种很好的做法。然而，这并不意味着所有表都需要有这个字段，只有与组直接相关的表才需要有这个字段。例如，具有状态值的查找表可能不需要它，但客户表可能需要它。当您尝试删除一条记录并且必须检查 579 个表（其中只有 25 个相关）时，随意将其添加到所有表中可能会导致不好的结果。所有这一切在很大程度上取决于这些组的含义是什么。我们的大多数表都与客户端表有关系，因为它们包含与特定客户端相关的数据，并且因为我们不希望各个客户端能够查看其他客户端的数据。不包含此类数据的表则不包含此类数据。
是的，大多数查询可能需要该字段，并且许多存储过程希望将其作为输入变量，但如果您确实需要过滤此信息，那么就应该如此。
然而，如果只有一组并且永远不会超过一组，那就是浪费时间、精力和空间。
If you need to be able to identify data by more than one group in the future, having foreign keys is a good practice. However, that deosn't mean all tables need to have this field, only the ones directly related to the group. For instance a lookuptable with state values may not need it, but the customers table might. Adding it to all tables willy-nilly can lead to bad things when you try to delete a record and have to check 579 tables (only 25 of which are pertinent). All this depends greatly on what the meaning of the groups is. Most of our tables have a relationship to the client table, because they contain data related to specific clients and because we don't want various clients to have the ability to see data for other clients. Tables which do not contain that kind of data do not.
Yes most queries may need the field and many stored procs will want to have it as an input variable, but if you truly need to filter on this information, then that is as it should be.
If however there is only one group and will never be more than one group, it is a waste of time, effort and space.