SQL Server Database - How to process a table that MAY be associated with any number of other tables?

Question

To give an idea of ​​what I'm saying, consider an entity (in my case, it is a Task ), which can be associated with any number of other objects in the system. For our purposes, let me say that the task may be related to:

• Project
• Account
• Ticket
• Person
• etc.

All of them are represented by their own tables in the database. Now the task could potentially be associated with any of them, and due to the fact that the system is under active development, the list of potential links will continue to grow relatively quickly. Please note that this relationship is from 1 to many - a task can only be associated with one of them at a time, but one account can have several tasks associated with it.

Now I have considered several options for this, but I do not consider myself an expert in the field of database design, so I decided that I would get to the end. The options I reviewed include:

• The foreign key is for each link in the Task table, and we just need to add columns. However, since the task cannot be associated with more than one of them at a time, this will lead to a large number of FK columns with NULL values. It will also require a new column and an update to our database model in our application whenever we add a new link.

• One column in Task , which acts as a foreign key, but includes another column defining a type link, so when we query it, we can determine which JOINs occur based on the type. Thus, both account identifiers and identities will be in this column for their tasks, but the link type column indicates whether the identifier is a person or an account. This is very risky for me, and obviously the limitations cannot be implemented in the database.

• Other options

I would really like it if someone could point me towards a “cleaner” design, but if not, would there be a few columns that act as FK constraints but allow NULL to be the best choice?

Thanks in advance!

Answer 1

I would use the first option.

Minuses:

• Add a new column when adding a new table. Since you are already editing the database by adding a new table, adding a single column should not be a problem.
• NULL values ​​in many columns. This does not have much impact on performance or anything else. You can use default values ​​instead of NULL if they suit you. See This Question ( SQL Server - Performance / Zero Column Disadvantages ) and answers.

But on the other hand, you get more reliable relationships, understandable connections, much more suitable entity framework mappings, simpler ant queries, etc.

Answer 2

In the past, I have found that with a proper design review this is not necessary. For example, an account can have many projects. An account can have many people. A project can have many tasks. Therefore, tasks apply only to projects.

If this does not really work, you can consider a task table for each type. Project Tasks, Account Tasks, etc. This will improve query performance.

Then you need the domain rule to ensure that all of your task tables adhere to a specific pattern.

I learned about the domain rule in college, but did not implement it in the real world, so I do not know how to do this on the SQL server. In real scenarios, it always worked, as I indicated in the first paragraph.

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

Answer 3

In fact, the accepted standard is the REF or XREF table. For example, between Task and Project, you must have a table with an identifier for the table, a foreign key for the task, and a foreign key for the project.

Basically, you link the project and the task by ID and just add a new record every time you need a new association. If there is information specifically about this relationship, he will live in this table with the relationship.