20

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?

Sujal Patel
  • 2,444
  • 1
  • 19
  • 38
ascobol
  • 7,554
  • 7
  • 49
  • 70
  • 1
    Side note: Tables don't have relations, they *are* relations (or at least, they would be if SQL properly implemented the relational model). What they have between them is *relationships*. – Marcelo Cantos Apr 04 '11 at 11:30
  • @Marcelo, interesting point, could you expand it a bit? (although I'm fine with english, it is not my mother tongue). How are tables themselves already relations? – Czechnology Apr 04 '11 at 11:32
  • 2
    It isn't really an *English* thing so much as a question of database terminology. The *relation* is a very specific concept in the relational model (informally, a set of tuples with a header); its counterpart in SQL is the table. When you refer to "relations" between tables, what you really mean is *foreign keys*, which are a special case of constraints between tables, and which may be casually referred to as *relationships*. But you should avoid using the term "relation" so as to prevent confusion with the formal meaning of the word. – Marcelo Cantos Apr 04 '11 at 12:05
  • The word *relation* comes from mathematics. It's a set of ordered tuples. The relational model of data was derived from the mathematics of relations. Using the word relationships for the association between tables helps with clarity. This may work out very differently in other languages. – Walter Mitty Jun 06 '19 at 11:06
  • Possible duplicate of [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) – philipxy Jun 06 '19 at 23:50

5 Answers5

19

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.

Marcelo Cantos
  • 181,030
  • 38
  • 327
  • 365
6

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.

BenMorel
  • 34,448
  • 50
  • 182
  • 322
Czechnology
  • 14,832
  • 10
  • 62
  • 88
  • 1
    Nice, I search semilar solution, But i want to keep my database normalization, Any Idea how to manage the relationsheps between thouse tables? – Stack Overflow May 17 '16 at 06:17
4

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.

Community
  • 1
  • 1
Erwin Smout
  • 18,113
  • 4
  • 33
  • 52
1

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.

Zann Anderson
  • 4,767
  • 9
  • 35
  • 56
0

You don't need a separate comment table for every other, one is enough. Every comment will have a unique ID, so you don't have to worry about conflicts.

Saša Ćetković
  • 885
  • 9
  • 21