multiple tables need one to many relationship

Question

I have a SQL database with multiple tables: A, B, C, D. Entities in those tables are quite different things, with different columns, and different kind of relations between them.
However, they all share one little thing: the need for a comment system which, in this case, would have an identical structure: author_id, date, content, etc.

I wonder which strategy would be the best for this schema to have A,..D tables use a comment system. In a classical 'blog' web site I would use a one-to-many relationship with a post_id inside the 'comments' table.

Here it looks like I need an A_comments, B_comments, etc tables to handle this problem, which looks a little bit weird.

Is there a better way?

Answer 1

Create a comment table with a comment_id primary key and the various attributes of a comment.

Additionally, create A_comment thus:

CREATE TABLE A_comment (
    comment_id PRIMARY KEY REFERENCES comment(comment_id),
    A_id REFERENCES A(A_id)
)

Do likewise for B, C and D. This ensures referential integrity between comment and all the other tables, which you can't do if you store the ids to A, B, C and D directly in comment.

Declaring A_comment.comment_id as the primary key ensures that a comment can only belong to one entry in A. It doesn't prevent a comment from belonging to an entry in A and an entry in B, but there's only so much you can achieve with foreign keys; this would require database-level constraints, which no database I know of supports.

This design also doesn't prevent orphaned comments, but I can't think of any way to prevent this in SQL, except, of course, to do the very thing you wanted to avoid: create multiple comment tables.

Answer 2

I had a similar "problem" with comments for more different object types (e.g. articles and shops in my case, each type has it's own table).

What I did: in my comments table I have two columns that manage the linking:

• object_type (ENUM type) that determines the object/table we are linking to, and
• object_id (unsigned integer type that matches the primary key of your other tables (or the biggest of them)) that point to the exact row in the particular table.

The column structure is then: id, object_type, object_id, author_id, date, content, etc.

Important thing here is to have an index on both of the columns, (object_type, object_id), to make indexing fast.

Answer 3

I presume that you are talking of a single comments table with a foreign key to "exactly one of" A, B, C or D.

The fact that SQL cannot handle this is one of its fundamental weaknesses. The question gets asked over and over and over again.

See, e.g.

What is the best way to enforce a 'subset' relationship with integrity constraints

Your constraint is a "foreign key" from your "single comments" table into a view, which is the union of the identifiers in A, B, C and D. SQL supports only foreign keys into base tables.

Observe that SQL as a language does have support for your situation, in the form of CREATE ASSERTION. I know of no SQL products that support that statement, however.

EDIT You should also keep in mind that with a 'single' comments table, you might need to enforce disjointness between the keys in A,B,C and D, for otherwise it might happen some time that a comment automatically gets "shared" between entity occurrences from different tables, which might not be desirable.

Answer 4

You could have a single comments table and within that table have a column that contains a value differentiating which table the comment belongs to - ie a 1 in that column means it's a comment for table A, 2 for table B, and so on. If you didn't want to have "magic numbers" in the comments table, you could have another table that has just two columns: one with the number and another detailing which table the number represents.