How to create an asset table for CMS (two-to-many relationship? ..)

Question

I am working on a database for a simple CMS such as a web application. It basically consists of bits of content that is part of a content group. Like parts of text that are part of a page. For this, I was thinking of a page table and a text table with a 1: n ratio.

Thing - I also want the assets to be associated with either the page or the text. Thus, a piece of text can have a thumbnail associated with it, but a page can also have a thumbnail associated with it. Somehow I just can't figure out how to do this. Should I make an assets table with pageId and textId and make sure that only one of them is used in each row? I find it a little strange .. Or do I just need to make two different asset tables?

Hope you guys can help me!

Answer 1

Assets are autonomous and can potentially have life completely outside their use on any page. Think of a graph that has been uploaded to your server, for example.

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

Therefore, your problem does not have mutually exclusive foreign keys in your asset table. Your problem is having any foreign keys in your asset table.

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

Answer 2

Typically, there are two ways to deal with this situation:

• Or make exclusive FK (as you already opened),
• or use inheritance and only one FK like this ...

... however, this is probably brute force with two child tables. OTOH, as the number of tables grows on either side of the relationship, this scheme can avoid "multiplying the relationship", for example:

For some tips on how to implement inheritance in a relational database, check out this post .