Hello,

I'm trying to figure out how to show a relationship between a parent record and several types of child records.

For instance:

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.


Thank you!

do a search for supertype/subtype

What do you call the Parental Entity?

Media Content(Content Name, Content Type)

Media Content Parts(Content Name, Content Part)


?????

Every Media Content May have 0 or Many Content Parts

A Content Part belongs to 1 and only 1 Content

???

Sound right?


What do you mean by Chrono Order?

Date Entered, Publish Date of the Content? You need to be more specific on that

Thank you! That was EXACTLY what I was looking for.

So just to make sure I got this, would the following be correct using my example above?

tblMedia
- MediaID (Autonumber PK)
- MediaName
- MediaDate
- fkMediaTypeID

tblMediaTypes
- MediaTypeID (AutoNumber PK)
- MediaTypeName
- "TV Show"
- "Music Video"

tblTVShows
- TVShowID (Autonumber PK)
- fkMediaID
- NumberSeasons
- NumberEpisodes

tblMusicVideos
- MusicVideoID (Autonumber PK)
- fkMediaID
- ChoreographerName


There's obviously a lot more data for each subtype, but I just wanted to make sure I got the general concept accurately.

Also, how would these end up joining together in the long run? Would it be a form that listed all the MediaID's and then subforms that render based on the MediaType field, linking the FK's and PK's?

Thanks again!!

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.

No need fro the tables for TV and music Video

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

Understand?

So you're a dancer?

Discount double check!

you're welcome

we've had this question many times before, and i've found that this was the most appropriate search phrase

your examples are perfect

you do need all 4 tables, but the tblTVShows and tblMusicVideos tables should ~not~ have their own autonumber ids, rather, the fkMediaID is also the PK in those tables...

... and there goes your naming convention, eh

you'll haveta rename them pkfkMediaID, won't you

and while we're at it, can i ask you not to stick "tbl" at the front of table names

thank you

I usually don't use the pk/fk/tbl prefixes. When I was looking up the info about the supertype/subtype data, I saw that in everyone else's posts; I thought it was the cool thing to do. Haha.

Thanks again!