SQL Server database - How to handle a table which COULD be linked to any number of other tables?

Question

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.

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 Task table, 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.

• A single column in Task that acts as a foreign key, but include another column specifying a link type, 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..

• 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?

Thanks in advance!

Answer 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.

Hope this helps. Otherwise the other two answers here make sense.

Answer 2

I would use first option.

Cons:

1. 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.
2. 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

But on the flip side, you get more robust relations, understandable joins, much more appropriate entity framework mappings, easier queries ant etc.

Answer 3

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.

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.