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.