How to implement multi relationship in SQL Server?

Question

I’m trying to design a database to use with ASP.net MVC application. Here is the scenario: There are three entities and users can post their comments for each of these different entities. I just wonder how just put one table for Comments and link all other entities to it. Obviously, Comments table needs 3 references (foreign key) to those tables but as you know these foreign keys can’t be null and just one of them can be filled for each row. Is there any better way than implementing three different tables for each entity’s comments?

Answer 1

Either: One comment table per Entity type

Or: one master Entity tables with child Comments and EntityType specific tables.

• EntityMaster: EntityID, foo, bar
• Comments: EntityID, CommentID, UserID, ... PK is (EntityID, CommentID etc)

For the 3 Entity tables, PK is EntityID

• EntityOne: EntityID, EntityTypeID (check constraint = 1), ...
• EntityTwo: EntityID, EntityTypeID (check constraint = 2), ...
• EntityThree: EntityID, EntityTypeID (check constraint = 3), ...

There is no shortcut or elegance in having one comment table for 3 parents: it's wrong in database design terms.

Personally, I'd probably go for option 1...

Edit, on reflection:

Sometimes you have to look at the usage of the data.

If the 3 entities are used separately, on separate screens, don't link to each other then it'd be option 1.

If the 3 entities are used and displayed together, then option 2 makes more sense because you can pull data together more easily.