Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have 3 different types of content for a website, 'Article', 'Video', 'Review'. Each of those three types of content contain common fields and type specific fields. For this, I have created the following 5 tables:

content - being the primary table containing the common fields across all content types
content_type - name, description, and type specific table name of available content types
article - the table that contains all the type specific fields for content of type 'Article'
video - the table that contains all the type specific fields for content of type 'Video'
review - the table that contains all the type specific fields for content of type 'Review'

I am a bit confused as to properly create the relationships between the tables. Realistically one 'content' row relates to one 'article' (type specific table) row. To me this sounds like a one to one - non-identifying relationship. Where I am think I am getting most confused, is the type can be one of many. So should they be One to Many or Many to Many, identifying / non-identifying?

I need to be able to search the content fields and then retrieve the corresponding type specific row based on the search. I also need to be able to search the type specific fields, and retrieve the corresponding content row for search matches.

Also to note, I am using MySQL with Doctrine/ORM, however I am not looking for the SQL statements here I am just looking to better understand what the relationships should actually look like.

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

You have supertype (content) which may be one of different suptypes (article, video,review). One approach to model such relationship in database is :

CONTENT(content_id, content_type_id, PK(content_id), UNIQUE(content_type_id,content_id));

 ARTICLE(content_id, content_type_id, 
  other attributes, 
  PK(content_type_id,content_id), 
  FK(content_type_id,content_id), CHECK (content_type_id = 'article_type_id'));

VIDEO(content_id, content_type_id, 
  other attributes, 
  PK(content_type_id,content_id), 
  FK(content_type_id,content_id), CHECK (content_type_id = 'video_type_id'));

etc.

UNIQUE(content_type_id,content_id) seems redundant, but it's used to enforce storing of the sub-types in proper table, and only once. CHECK constraint restricts proper content type in each of detail tables. As far as I remember, Mysql still doesn't have unique CHECK constraints, but you can use enum to emulate them.

share|improve this answer
    
I am pretty sure with MySQL you can create UNIQUE keys (if I am reading your answer correctly). So to be clear then article would have a one to one relationship with content using 'content_type_id' as the foreign key, and video would have a one to one relationship with content using the same 'content_type_id' as the foreign key. Then 'content_id' and 'content_type_id' would be combined as the primary key (which is unique anyhow?) Or am I misunderstanding? –  Aaron Murray Dec 18 '13 at 20:20
    
let me rephrase that a little, above when I used content_type_id, what I really meant was 'content_other_attributes_id'. 'content_type_id' would be used to link the type table with the content table if that makes sense. –  Aaron Murray Dec 18 '13 at 20:23
    
@AaronMurray : yeah, I was thinking about CHECK constraints, but somehow typed unique. Fixing, thank you –  a1ex07 Dec 18 '13 at 20:37
    
@AaronMurray: Yeah, content_id + content_type id are PK and FK in detail tables. And content_id is unique itself (even without content_type_id). –  a1ex07 Dec 18 '13 at 20:40
    
Thank you for your help, after drawing, erasing, drawing again, I think I ironed out the logic behind the table relationships. If I had a way to link an image here I would show you but I think I have it right now. –  Aaron Murray Dec 18 '13 at 21:02
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.