Relating multiple tables to each other in SQL

Question

Hi!

This is more or less a best-practice question:

I'm working on a site that requires me to relate some tables to several different tables. For instance, I have a Comments-table, that I would like to relate in a way such as one might comment on entities in Table A, but also in Table B separately.

Similarly, I have a rating-table that I would like to do the same with, so that one could rate different aspects in the site.

I've touched upon one method that implies having a loose relation with a Relationships-table with a PrimaryKey(Guid) and ForeignKey(Guid), and then using Guids as Primary key in both the Comments-table and the Rating-table - However this implies I'm gonna need Guid as primary key in the other tables as well.

Anyone have any great ideas here? Greatly appreciated :)

PS. If it's interesting, I'm creating an ASP.NET MVC2 app with an ORM (either EF, Linq2Sql or NHibernate.. doesn't really matter :))

Answer 1

You could use a single comments/ratings table, but use a separate table for each related entity to maintian the comment to entity relationship. If you had Posts and Pages, each with comments, it would look something like this:

Comments
 - CommentID
 - CommentText

Posts
 - PostID
 - Other fields

Pages
 - PageID
 - Other fields

PostComments
 - PostID
 - CommentID

PageComments
 - PageID
 - CommentID

Answer 2

One way you can have a strong relationship by using a Many-to-Many table in between the tables... Assuming you have the tables 'TableA', 'TableB' and 'Comment, you would have two additional tables, one to track table A's comments, one to track table B's comments, but only using their ids.

TableAComment
    TableAId
    CommentId


TableBComment
    TableBId
    CommentId

The other thing to ask yourself is "do I need to have only one comment table?". Depending on the design of the rest of the app, trying to force all your comments into one table may mean more work than having one per "parent". And it could cause performance issues later if this table ends up very large.

Answer 3

If possible I suggest to put some inheritance into the database - create base tables for rateable and commentable entities and reference this base tables from your comments and ratings table. That would be a usual table per type inheritance schema. See this post from the ADO.NET team blog for a basic overview on inheritance mapping with the entity framework (but the same will work with most other O/R mappers).

Answer 4

Yes, as you point out a 'loose relationship' is probably the easiest option if you're willing to forego some SQL features such as FK integrity. However, for the sake of a query writer/db admin I would also add a column with the table name that the row is relating on. E.g. Contact (ID guid), Employee (ID guid) and then Address (ID whatever, EntityID guid, EntityType string/int).

This will give you the ability to attach your Address to any table without maintaining many-to-many tables (where you perhaps attach a check constraint to enforce one-to-many). Plus this design will also let you write some reusuable code in your client - e.g.

public class Address
{
    public void Attach(IEntity entity)
    {
        // do stuff
    }
}

public interface IEntity
{
     Guid ID { get; }
}

I first recall seeing this in action in the MS CRM db schema.

Your difficulty will lie in enforcing referential integrity. This could be done by a few check constraints on the EntityID column to enforce existence of the id based on the EntityType column.

Answer 5

I try to avoid to use a single column to link to different tables.

I'd go with one of those 3 possibilities - in order of my personal preference:

Multiple comments tables

If there is no specific reason to keep all comments in a single table, I make a comments table whenever I need one: one with a foreign key for TableA, an other one with foreign key for TableB, etc. Referential integrity is assured, and the datatype of the foreign key column may change from table to table. Also, this allows for each table to evolve differently in the future.

One comments table, multiple (nullable) foreign keys

If there is a reason to keep all comments in a single table, I add a column for each foreign key, but I allow nulls. Referential integrity is assured. I sometimes add an other column which indicates the type of comment (TableA, TableB, ...), with a lookup table - this can be helpful for queries.

One comments table, x many-to-many tables

A many-to-many table for each table which needs to be linked to the comments. No unnecessary columns, but can be some more work when creating queries. Two inserts when creating a comment - if available I'd use a stored procedure and views. Definitely more work than the other solutions...