Database Mapping - Multiple Foreign Keys

Question

I want to make sure this is the best way to handle a certain scenario.

Let's say I have three main tables I will keep them generic. They all have primary keys and they all are independent tables referencing nothing.

Table 1

PK
VarChar Data

Table 2

PK
VarChar Data

Table 3

PK
VarChar Data

Here is the scenario, I want a user to be able to comment on specific rows on each of the above tables. But I don't want to create a bunch of comment tables. So as of right now I handled it like so..

There is a comment table that has three foreign key columns each one references the main tables above. There is a constraint that only one of these columns can be valued.

CommentTable

PK 
FK to Table1
FK to Table2
FK to Table3
VarChar Comment
FK to Users

My question: is this the best way to handle the situation? Does a generic foreign key exist? Or should I have a separate comments table for each main table.. even though the data structure would be exactly the same? Or would a mapping table for each one be a better solution?

Answer 1

My question: is this the best way to handle the situation?

Multiple FKs with a CHECK that allows only one of them to be non-NULL is a reasonable approach, especially for relatively few tables like in this case.

The alternate approach would be to "inherit" the Table 1, 2 and 3 from a common "parent" table, then connect the comments to the parent.

Look here and here for more info.

Does a generic foreign key exist?

If you mean a FK that can "jump" from table to table, then no.

Assuming all 3 FKs are of the same type¹, you could theoretically implement something similar by keeping both foreign key value and referenced table name² and then enforcing it through a trigger, but declarative constraints should be preferred over that, even at a price of slightly more storage space.

If your DBMS fully supports "virtual" or "calculated" columns, then you could do something similar to above, but instead of having a trigger, generate 3 calculated columns based on FK value and table name. Only one of these calculated columns would be non-NULL at any given time and you could use "normal" FKs for them as you would for the physical columns.

But, all that would make sense when there are many "connectable" tables and your DBMS is not thrifty in storing NULLs. There is very little to gain when there are just 3 of them or even when there are many more than that but your DBMS spends only one bit on each NULL field.

Or should I have a separate comments table for each main table, even though the data structure would be exactly the same?

The "data structure" is not the only thing that matters. If you happen to have different constraints (e.g. a FK that applies to one of them but not the other), that would warrant separate tables even though the columns are the same.

But, I'm guessing this is not the case here.

Or would a mapping table for each one be a better solution?

I'm not exactly sure what you mean by "mapping table", but you could do something like this:

Unfortunately, that would allow a single comment to be connected to more than one table (or no table at all), and is in itself a complication over what you already have.

All said and done, your original solution is probably fine.

---

¹ Or you are willing to store it as string and live with conversions, which you should be reluctant to do.

² In practice, this would not really be a name (as in string) - it would be an integer (or enum if DBMS supports it) with one of the well-known predefined values identifying the table.

Answer 2

Thanks for all the help folks, i was able to formulate a solution with the help of a colleague of mine. Instead of multiple mapping tables i decided to just use one.

This mapping table holds a group of comments, so it has no primary key. And each group row links back to a comment. So you can have multiple of the same group id. one-many-one would be the relationship.