Mysql – Effectively modelling extended classes

MySQLnormalizationormrelational-theory

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.

Best Answer

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.

Related Question