数据库设计：同一列用于 2 个不同的外键
我正在开发一种连接两个数据源（例如查询）的方法。
我有一个名为 QueryField 的表，其结构如下：
QueryID
FieldID
FieldName
....
如果我在 QueryField 上有 2 条记录，
QueryID FieldID FieldNAme
------------ --------- ----------
1 1 CustomerID
1 2 CustAddress
2 3 CustNo
2 4 CustomerPhone
我想要一个新表 QueryFieldJoin ，它定义了 2 个查询中用于连接的字段。我的想法是具有以下结构
LeftJoinFieldID (FK from FieldID of QueryField)
RightJoinFieldID (also FK from FieldID of QueryField)
JoinType (intersect, outer join).
PrimaryKey 是 LeftJoinFieldID 和 RightJoinFieldID 的组合
LeftJoinFieldID RightJoinFieldId JoinType
-------------- ---------------- --------
1 3 Intersect
这将起作用，但是我认为这不是最好的数据库设计，其具有与另一个表上两个不同列的外部相同的字段。有人可以建议更好的方法吗？
I'm developing a method of joining 2 sources of Data (e.g. Queries).
I have a table Named QueryField with the following structure:
QueryID
FieldID
FieldName
....
If I have 2 records on QueryField
QueryID FieldID FieldNAme
------------ --------- ----------
1 1 CustomerID
1 2 CustAddress
2 3 CustNo
2 4 CustomerPhone
I want to have a new table QueryFieldJoin which defines which fields in the 2 queries to use to join on. My idea was to have the following structure
LeftJoinFieldID (FK from FieldID of QueryField)
RightJoinFieldID (also FK from FieldID of QueryField)
JoinType (intersect, outer join).
PrimaryKey is a combination of LeftJoinFieldID and RightJoinFieldID
LeftJoinFieldID RightJoinFieldId JoinType
-------------- ---------------- --------
1 3 Intersect
This will work, however I feel that this isn't the best DB design having the same field as a foreign to two different columns on another table. Can anybody suggest a better approach?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
数据库设计还取决于您的需求：
1）您需要回答哪些查询？
2) 您需要以多快的速度访问这些数据？
从富有表现力的 POV 来看，您的设计可能是正确的，但可能不是最佳解决方案，具体取决于您需要运行哪些查询。
例如，您可能会考虑使用三个不同的表：一张用于字段，一张用于查询，一张用于操作。
如果您不想执行任何连接，甚至可以使用一张包含所有内容的大表。
The DB Design also depends on what are your needs:
1) Which queries do you need to answer?
2) How fast do you need to access those data?
From an expressive POV, your design can be correct but maybe not the best solution depending on which queries you need to run.
For Instance, you might consider to have three different tables: One for the Fields, one for The Queries and one Operations.
Or even one big table with everything there if you do not want to perform any Join.