0

I currently am working with a database for a social media application. The database is running on postgresql, and I have ran into a logic issue.

I have two different types of content that can be submitted, topics, and posts. Each of these with their own primary key.

All of these have items can have some media attached to them. In my media table I have the columns content_type_id and content_id where content type is a key in a look up table with the different types of content, and the content_id is the primary key in the table where that particular piece of content is stored.

The issue I have ran into is that I cannot create a foreign key on content_id because depending on content_type it could be referring to one of two tables. Is there a way that I can set up a foreign key to look at the proper table depending on the value of the content_type_id column?

Cœur
  • 37,241
  • 25
  • 195
  • 267
Jake
  • 97
  • 1
  • 11

2 Answers2

1

I'm not sure to understand your question, but you have a design problem. If I've interpreted that right, maybe you need a design like this:

enter image description here

but I can't know that if you don't provide your current design.

On this design:

  • CONTENT_TYPE can be a POST or TOPIC.
  • MEDIA can have 1 CONTENT_TYPE (POST or TOPIC).
  • CONTENT_TYPE can be related to N MEDIA.
carexcer
  • 1,407
  • 2
  • 15
  • 27
  • The issue I am having is that the media is something to be attached to content (a picture, document or another type of file). So the content_id would also need to be a forgien key for the table with it being attached to either a post id, or a topic id. As the post table and topic table can have overlapping ids, I was hoping to be able to use the content_type_id as a way of showing that it was either a post or a topic. But I don't know if there is a way I can have a forgien key constraint check its validity based off the value of another column. – Jake Jan 24 '14 at 17:13
  • To distinguish between a post or a topic you can add a column on the `CONTENT_TYPE` to do it. You can use a boolean, or better an integer to be able to amply in the future for more content types (1 for topic, 0 for post). – carexcer Jan 24 '14 at 17:26
0

The issue was resolved. Rather than having each table have it's own sequence for the primary key, a single sequence is used across all the tables, with the entity type lookup table becoming the entity map table mapping the now global id to the type of entity it is (post, topic ect). So that there will no longer be a need for a secondary table to differentiate if the primary key is a post or topic.

For example before when a post is created it is made with a sequental id as a primary key (1,2,3,4...) and when a topic is created the same thing happens sequential keys (1,2,3,4,...).

When media would be stored in the media_table, the media_table would have the issue of duplicate entity keys (both post with id 1 and topic with id 1 have a picture). This was originally designed to be resolved by having an additional column in the media table to differentiate between it being a post or topic.

By having the same sequence used for both posts and topics, they no longer will share any primary keys, so entity type is no longer needed to differentiate between the two, and the primary key in both topic and post will act as a super key pointing to the media table's entity id.

Jake
  • 97
  • 1
  • 11