Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2009
    Posts
    7

    Normalized design question

    I've set up a MySQL database in a way that makes sense to me. It has occurred to me that it might not be the most efficient construction/consistent with good database design. It's described below, inviting constructive criticism (also let me know if there is another board I should be posting this to instead).

    Models/Fields below:

    Studio:
    Name
    Short_Description
    Long_Description
    Hyperlink_1
    Hyperlink_2
    Hyperlink_3
    Hyperlink_4
    Hyperlink_5
    (it already occurs to me to put hyperlinks on their own table)

    Genre:
    Name
    Long_Description

    Film:
    Studio (foreign key - one2many)
    Name
    Genre (foreign key - many2many)
    Award (foreign key - many2many)
    Short_Description
    Long_Description
    Hyperlink_1
    Hyperlink_2
    Hyperlink_3
    Hyperlink_4
    Hyperlink_5

    Actor:
    Film (foreign key - many2many)
    Award (foreign key - many2many)
    Name
    Short_Description
    Long_Description

    Award:
    Name
    Short_Description
    Long_Description

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Those many-to-many links are gonna be a problem, but maybe you already understand that. The work-around is simple enough.

    Any time you see enumeration like hyperlink_1, hyperlink_2... there is a problem. Your comment shows that you understand that too.

    Your design is off to a good start, but it isn't ready to code yet.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Aug 2009
    Posts
    7
    Would I be good to go with the following, or are the other many-to-many fields also problematic? And how might I restructure?

    Studio:
    Name
    Short_Description
    Long_Description

    Genre:
    Name
    Long_Description

    Film:
    Studio (foreign key - one2many)
    Name
    Genre (foreign key - many2many)
    Award (foreign key - many2many)
    Short_Description
    Long_Description

    Actor:
    Film (foreign key - many2many)
    Award (foreign key - many2many)
    Name
    Short_Description
    Long_Description

    Award:
    Name
    Short_Description
    Long_Description

    Hyperlinks:
    Table (generic foreign key - could go to Film or Studio)
    ID (primary key of film or studio link applies to)
    Link

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    I don't understand enough to answer your questions. Give it a shot, if you have questions come back and ask them!

    One step at a time, you're on the right track at the very least and you might have everything ready to roll.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    so looking at your model
    ...a film can only have one award?
    ...an actor can only appear in one film?
    ...an actor can only win one award?

    Google

    these are worth a read...
    Fundamentals of Relational Database Design -- r937.com
    The Relational Data Model, Normalisation and effective Database Design
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Aug 2009
    Posts
    7
    No, that's not the case. Sorry if that's unclear. What I mean by many-to-many is that a film can have many awards and an award can apply to many films. An actor can be in many films, and a film can have many actors, etc.

    That's the way I have it set up now actually, in mysql.

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
  •