CODEWITHSUNDEEP
sqlrelational-database
Jimmyt1988
Jimmyt1988

Reputation: 21126

What should a relationships table look like - Need confirmation of my technique

Lets say I have 3 models:

I asked a question based on if I should have each model keep track of its relationships: SQL relationships and best practices

an example of this would be a "Pages" table that states who its author was... The problem seemed to be that if 2 users were the author of the one page, you'd have to add a new specific table called PageRelationshipsWithUsers that might have a reference to the PageID and the UserID that created it and a separate row for the co-author.

Understandably this sounds a bit naff. I would end up with a heck load of relation tables and most likely, it could be replaced with just the one multi-purpose relationship table... So I decided to come up with a relationships table like the following:

Relationships Table New

RelationshipID | ItemID        | LinkID     | ItemType    | LinkType | Status
-----------------------------------------------------------------------------
1              | 23(PageID)    | 7(UserID)  | ("Page")    | ("User") | TRUE
2              | 22(CommentID) | 7(UserID)  | ("Comment") | ("User") | TRUE
3              | 22(CommentID) | 23(PageID) | ("Comment") | ("Page") | TRUE

however, I would very much appreciate some input as to how good of an idea laying out my relationships table like this is.

Any thoughts?

Answer was told to me by a work colleague:

Imagine the above relationships table for the model "Book"

A User can Rent a book, so the relation is User -> Book... But what if he can buy a book too: User->Book....

Ooops, we need a new relationship... and considering this relationship table was supposed to be the 1 size fits all, we now have a requirement to add a new separate table... whoops.

So the answer is NO NO NO. don't, it's naughty. Keep your relationship tables separate and specific.

Upvotes: 0

Views: 59

Answers (1)

Dan
Dan

Reputation: 10680

Your suggestion for a relationship table is not optimal for several reasons:

  • It's difficult to write queries that join tables through the relationship table, as you will need filters on the ItemType and LinkType columns, which is not intuitive when writing queries.
  • If a need arises to add new entities in the future, that use different datatypes for their primary keys, you cannot easily store ID's of various datatypes in your ItemID and LinkID columns.
  • You cannot create explicit foreign keys in your database, to enforce referential integrity, which is possibly the best reason to avoid the design you suggest.
  • Query performance might suffer.

When normalizing a database, you should not be afraid to have many tables. Just make sure to use a naming convention that makes sense and is self-documenting. For example, you could name the relation table between authors and pages "PageAuthors", instead of "Pages".

Upvotes: 2

Related Questions