Parent records with different types of child records
I'm trying to figure out how to show a relationship between a parent record and several types of child records.
The parent record would be "That One TV Show" and the child records would correspond to each episode within "That One TV Show". But another parent record would be "That Other Music Video". The fields related to a music video would be much different than the fields for a TV show (i.e. no episodes to list, etc.) so I'm assuming they'd require separate tables.
I thought about just making different parent tables for each type of work, but we'd like to have the data displayed in chronological order without being separated by type.
I hope this makes sense. I know it's difficult to explain.
Every Media Content May have 0 or Many Content Parts
A Content Part belongs to 1 and only 1 Content
What do you mean by Chrono Order?
Date Entered, Publish Date of the Content? You need to be more specific on that
Sorry, I didn't have the data structure completely worked out when I posted this because I wasn't sure how it would all fit together properly.
Yes, each Media record could have 1 or Many Media Content Parts. (i.e. a movie/music video would have 1, a television show would have many, etc.)
This is basically coming together to form a resume-type list of information for my dance team; the chronology would be related to when the work was released (music videos/movies/etc.) or when the performance occurred (live tours/etc.) depending on the type of media.
The TYPE in the Media table will tell you what they are
Everything goes into the Media table
All of the Media Parts go into the Parts table
IF you have items that are VERY unique to a type of media...THEN I would add another table for the unique specific things to that media type..so that way you only store the data of those things, and won't have a boatload of nullable columns that don't apply to the other