How to design assets table for CMS (two-to-many re

Question

I'm working on a database for a simple CMS like web-app. Basically it consists of bits of content that are part of a contentgroup. Like pieces of text that are part of a page. For that I was thinking of a page table and a text table with a 1:n relationship.

Thing is I also want assets to be linked to either a page or a text. So a piece of text could have a thumbnail linked to it, but also a page could have a thumbnail linked to it. Somehow I just can't figure out how to do that nicely. Should I make an assets table with the collumns pageId and textId and make sure only one of them is used each row? Feels a little weird to me.. Or do I just have to make two different asset tables?

Hope you guys can help me with this!

Answer 1

There are generally two ways to handle such situation:

1. Either make exclusive FKs (as you already discovered),
2. or use inheritance and just one FK, like this...

...however, this is probably an overkill with just two child tables. OTOH, as the number of tables grows on either side of the relationship, this scheme can avoid "relationship multiplication", for example:

For some hints on how to implement inheritance in a relational database, take a look at this post.

Answer 2

Assets stand alone and could potentially have a life completely outside of their being used on any page. Think of a graphic that has been uploaded to your server, for example.

Similarly, you might have assets that are reused on multiple pages. Graphics come to mind for sure, but also some boiler-plate text, ads, or any number of other common elements.

Therefore, your problem is not having mutually-exclusive foreign keys on your asset table. Your problem is having any foreign keys on your asset table.

Instead, you should have an intersection table (many-to-many) that maps assets to pages. If you have more than one thing that could use an asset, have one intersection table for each thing that uses assets.