Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2012
    Posts
    4

    Parent records with different types of child records

    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!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    do a search for supertype/subtype
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Jan 2012
    Posts
    4
    Quote Originally Posted by r937 View Post
    do a search for supertype/subtype
    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!!

  5. #5
    Join Date
    Jan 2012
    Posts
    4
    Quote Originally Posted by Brett Kaiser View Post
    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
    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.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by ndallenaz View Post
    for my dance team;
    So you're a dancer?

    Discount double check!
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ndallenaz View Post
    Thank you! That was EXACTLY what I was looking for.
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jan 2012
    Posts
    4
    Quote Originally Posted by r937 View Post
    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!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •