Effectively modelling extended classes

Question

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.

Answer 1

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.