multiple tables need one to many relationship

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.

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.