Results 1 to 9 of 9
  1. #1
    Join Date
    May 2008
    Posts
    97

    Poor Database Design or Poor Practice?

    I'm creating a database that looks similar to this

    Code:
    “Actor” Table,
    || Actor Name     ||   ActorID	|| 
    || Russell Crowe  || 	1 	||
    || Jessica Alba    || 	2 	||
    .
    .
    .
    
    Movie table
    || MovieID || Title || RunningTime || CompleteCast ||
    || 1	      || “Gladiator” || 234 || 1:2:34 ||
    .
    .
    .
    However, someone has told me that I have poor database design because inside my movie table "CompleteCast" I'm using delimited fields for my actors.

    I've already got separate tables for both "actor" and "movies" so why is this a bad thing and what can I do to change this problem?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it's a bad thing because it violates first normal form

    why is 1NF important? because without it, your queries will be horrendously complex (e.g. to find all the movies a given actor is in) and slow as cold glue (because they will require table scans)

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

  3. #3
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by Eric the Red
    However, someone has told me that I have poor database design because inside my movie table "CompleteCast" I'm using delimited fields for my actors.

    I've already got separate tables for both "actor" and "movies" so why is this a bad thing and what can I do to change this problem?
    There's nothing fundamentally wrong with storing delimited strings in a column but in this case it's unlikely to be very practical.

    For example: How will you ensure that the actor names in the cast column correspond to those in your Actor table? It's very difficult to enforce such a rule in the database. How will you locate and update every reference to an actor's name if it appears in lots of delimited strings (for example if an actor's name changes or needs correcting)? Finally, and most obviously, how do you expect to answer such queries as "Show me all the films in which Marlon Brando appeared"? It's very hard to do that efficiently if you first have to parse a string on every single row.

    The solution is to create a new table that joins actors to movies, probably with just two attributes (like: ActorMovie {Actor, Movie} where {Actor, Movie} is also the key of the table). This is often called a "joining table" or "association table" although personally I don't find that terminology very useful.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by dportas
    There's nothing fundamentally wrong with storing delimited strings in a column
    Do you consider delimited strings atomic then David?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by dportas
    This is often called a "joining table" or "association table" although personally I don't find that terminology very useful.
    okay, i'll bite -- what do you call it?

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

  6. #6
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by pootle flump
    Do you consider delimited strings atomic then David?
    A string is a string. It's only a single value by definition. The question of whether the same information could be better stored in other ways depends a lot on the circumstances and how the data is used. Although it can be an important design issue I don't believe it's desirable to make a single universal rule about it.

    1NF isn't strictly relevant here because most popular DBMSs (SQL ones) don't allow you to store more than one value in a single column anyway.

    Quote Originally Posted by r937
    okay, i'll bite -- what do you call it?
    The thing is, I'm unsure exactly when a table qualifies as a "association table" anyway. It seems to me that an association table can be any table with more than one foreign key. If that's so then I don't really see why I need a special name for those tables. Having more than one foreign key isn't so special is it?

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by dportas
    A string is a string. It's only a single value by definition.
    but not what it represents. XML is a string. A bitmask is an integer. But what they represent is more than a single "thing".

    Quote Originally Posted by dportas
    The thing is, I'm unsure exactly when a table qualifies as a "association table" anyway. It seems to me that an association table can be any table with more than one foreign key. If that's so then I don't really see why I need a special name for those tables. Having more than one foreign key isn't so special is it?
    I'd add that my internal-used-only-by-myself definition is that an association table has no non-key attributes. As such, it represents an association only and not a distinct and tangible entity.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    In my mind, an association table can also contain a date_begin and a date_end, since many associations are time delimeted.

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

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    I'd agree there. Associations can easily gather attributes, and become entities in themselves. This kind of falls in line with my "There's no such thing as a lookup table" mantra.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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