0

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!

Raapwerk
  • 619
  • 7
  • 15

2 Answers2

1

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.

Joel Brown
  • 14,123
  • 4
  • 52
  • 64
  • As I said above I completely agree. This relation makes way more sense. The inheritance relational database thing does make a lot of sense too for my app, so I'm going for a combination. Having one intersection table feels cleaner also. Thanks! – Raapwerk Jun 10 '13 at 15:22
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...

enter image description here

...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:

enter image description here

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

Community
  • 1
  • 1
Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167
  • Wow! Awesome answer! Thanks. I actually went with something like this. But Joels answer makes me doubt whether there shouldn't be an intersection table to leave open the option of using assets on multiple items. So a combination would be perfect! – Raapwerk Jun 10 '13 at 15:19
  • @Raapwerk This really depends on your requirements and is "orthogonal" to the structure above - you could have junction table, or inheritance or both! – Branko Dimitrijevic Jun 10 '13 at 19:21