To give an idea of what I'm talking about, consider an entity (in my case, it's a Task) which could be linked to any number of other entities in the system. For our purposes let's say the task could be linked to:
为了解我正在谈论的内容,请考虑一个实体(在我的情况下,它是一个任务),它可以链接到系统中的任意数量的其他实体。为了我们的目的,让我们说这个任务可以链接到:
- Project
- Account
- Ticket
- Person
- etc
All of these are represented with their own tables in the database. Now, a task could potentially be linked to any one of those, and due to the system being in active development, the list of potential links will continue to grow relatively quickly. Note these are 1 to many relationships - a task can only be linked to one of these at a time, but a single Account could have multiple tasks tied to it.
所有这些都在数据库中用自己的表表示。现在,任务可能可能与任何一个任务相关联,并且由于系统处于活动开发状态,潜在链接列表将继续相对快速地增长。请注意,这些是1对多关系 - 任务一次只能链接到其中一个,但是一个帐户可能有多个与之关联的任务。
Now, I have considered a few options for this, however I do not consider myself any kind of expert in database design, so I figured I'd reach out. Options I've considered thus-far include:
现在,我已经考虑了一些选项,但是我不认为自己是数据库设计方面的专家,所以我想我会伸出援手。我迄今为止考虑的选项包括:
-
A foreign key for each link in the
Tasktable, and we just have to keep adding columns. However since a task cannot be linked to more than one of them at a time, this will result in a lot of FK columns with NULL values. This also will require a new column and regeneration of our database model in our application whenever we add a new link.任务表中每个链接的外键,我们只需要继续添加列。但是,由于任务一次不能链接到多个任务,这将导致许多FK列具有NULL值。每当我们添加新链接时,这还需要在我们的应用程序中使用新列并重新生成我们的数据库模型。
-
A single column in
Taskthat acts as a foreign key, but include another column specifying a linktype, so when querying against it we can determine which JOINs happen based on type. So both Account's and Person's IDs would be in this column for their tasks, but the link type column would specify whether the ID is a person or an account. This feels very risky to me and obviously the constraints can't be enforced by the database..Task中的一个列充当外键,但包含指定链接类型的另一列,因此在查询时我们可以根据类型确定发生哪些JOIN。因此,帐户和人员ID都将在此列中显示其任务,但链接类型列将指定ID是个人还是帐户。这对我来说非常危险,显然数据库无法强制执行约束。
Other options??
I would love if someone was able to point me in the direction of a "cleaner" design, but if not, would the multiple columns acting as FK constraints, but allowing NULL be the best bet?
我很乐意,如果有人能指出我的“清洁”设计的方向,但如果没有,多列会作为FK约束,但允许NULL是最好的选择吗?
Thanks in advance!
提前致谢!
3 个解决方案
#1
3
I would use first option.
我会用第一个选项。
Cons:
- Add new column when you add new table - As you are already editing database by adding new table, adding one column should not be problem.
- NULL values in many columns - It does not have big impact on performance or anything else. You can use default values instead of NULL if it fits you better. See this question (SQL Server - Performance/Size Drawbacks of Null Columns) and answers
添加新表时添加新列 - 由于您已经通过添加新表编辑数据库,因此添加一列不应该是问题。
许多列中的NULL值 - 它对性能或其他任何内容都没有太大影响。如果它更适合您,您可以使用默认值而不是NULL。请参阅此问题(SQL Server - 空列的性能/大小缺点)和答案
But on the flip side, you get more robust relations, understandable joins, much more appropriate entity framework mappings, easier queries ant etc.
但另一方面,您可以获得更强大的关系,可理解的连接,更合适的实体框架映射,更容易查询蚂蚁等。
#2
1
I have found in the past that with proper consideration of design that this is not necessary. For example an account can have many projects. An account can have many persons. A project can have many tasks. So tasks only relates to projects.
我在过去发现,通过适当考虑设计,这是不必要的。例如,一个帐户可以有很多项目。一个帐户可以有很多人。一个项目可以有很多任务。所以任务只与项目有关。
If that really does not work then you can consider a tasks table for each type. Project tasks, account tasks, etc. This will improve query performance.
如果这确实不起作用,那么您可以考虑每种类型的任务表。项目任务,帐户任务等。这将提高查询性能。
You would then want a domain rule to ensure that all of your task tables adhere to a specific schema.
然后,您需要域规则以确保所有任务表都遵循特定架构。
I learned about the domain rule in college but never implemented it in the real world so I don't know how it could be done in SQL server. In real world scenarios it has always worked out as I specified in the first paragraph.
我在大学里学到了域规则但从未在现实世界中实现它,所以我不知道如何在SQL服务器中完成它。在现实世界的场景中,它总是像我在第一段中指出的那样成功。
Hope this helps. Otherwise the other two answers here make sense.
希望这可以帮助。否则,其他两个答案在这里是有道理的。
#3
0
Actually, an accepted standard is a REF or XREF table. So, for example between Task and Project, you'd have a table that has an ID for the table, a Foreign Key for a Task, and a Foreign Key for a Project.
实际上,可接受的标准是REF或XREF表。因此,例如在Task和Project之间,您将拥有一个表,该表具有表的ID,任务的外键和项目的外键。
Basically, you're associating the project and task by ID, and just will add a new entry every time you need a new association. If there's information specifically about that relationship, it will live in this table with the relationship.
基本上,您通过ID关联项目和任务,并且每次需要新关联时都会添加新条目。如果有关于该关系的具体信息,它将与该关系一起存在于此表中。
#1
3
I would use first option.
我会用第一个选项。
Cons:
- Add new column when you add new table - As you are already editing database by adding new table, adding one column should not be problem.
- NULL values in many columns - It does not have big impact on performance or anything else. You can use default values instead of NULL if it fits you better. See this question (SQL Server - Performance/Size Drawbacks of Null Columns) and answers
添加新表时添加新列 - 由于您已经通过添加新表编辑数据库,因此添加一列不应该是问题。
许多列中的NULL值 - 它对性能或其他任何内容都没有太大影响。如果它更适合您,您可以使用默认值而不是NULL。请参阅此问题(SQL Server - 空列的性能/大小缺点)和答案
But on the flip side, you get more robust relations, understandable joins, much more appropriate entity framework mappings, easier queries ant etc.
但另一方面,您可以获得更强大的关系,可理解的连接,更合适的实体框架映射,更容易查询蚂蚁等。
#2
1
I have found in the past that with proper consideration of design that this is not necessary. For example an account can have many projects. An account can have many persons. A project can have many tasks. So tasks only relates to projects.
我在过去发现,通过适当考虑设计,这是不必要的。例如,一个帐户可以有很多项目。一个帐户可以有很多人。一个项目可以有很多任务。所以任务只与项目有关。
If that really does not work then you can consider a tasks table for each type. Project tasks, account tasks, etc. This will improve query performance.
如果这确实不起作用,那么您可以考虑每种类型的任务表。项目任务,帐户任务等。这将提高查询性能。
You would then want a domain rule to ensure that all of your task tables adhere to a specific schema.
然后,您需要域规则以确保所有任务表都遵循特定架构。
I learned about the domain rule in college but never implemented it in the real world so I don't know how it could be done in SQL server. In real world scenarios it has always worked out as I specified in the first paragraph.
我在大学里学到了域规则但从未在现实世界中实现它,所以我不知道如何在SQL服务器中完成它。在现实世界的场景中,它总是像我在第一段中指出的那样成功。
Hope this helps. Otherwise the other two answers here make sense.
希望这可以帮助。否则,其他两个答案在这里是有道理的。
#3
0
Actually, an accepted standard is a REF or XREF table. So, for example between Task and Project, you'd have a table that has an ID for the table, a Foreign Key for a Task, and a Foreign Key for a Project.
实际上,可接受的标准是REF或XREF表。因此,例如在Task和Project之间,您将拥有一个表,该表具有表的ID,任务的外键和项目的外键。
Basically, you're associating the project and task by ID, and just will add a new entry every time you need a new association. If there's information specifically about that relationship, it will live in this table with the relationship.
基本上,您通过ID关联项目和任务,并且每次需要新关联时都会添加新条目。如果有关于该关系的具体信息,它将与该关系一起存在于此表中。