#1
  1. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,650
    Rep Power
    171

    Is this a better design?


    Hello;

    Am I missing something or is this a bad design?

    These tables show the usage of each plant.

    Wouldn't it be a better idea to

    1 - Add an "id" column to Usage table,
    2 - Replace "usage" column with something like "usage_id" in the PlantUsage table.

    Because that way they dont wont have redundant text. For example here they have to have the list of usages in the Usage table and also in the PlantUsage table.

    Also here if they want to edit a usage they have to update both tables!
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    no, it is not bad design

    the argument about "redundant text" is meaningless -- if you replace the usage names with an id, you will have exactly the same amount of redundancy (with a little extra thrown in)

    using a natural key has advantages

    using a surrogate key has advantages

    it's basically your call

    for more information, google "natural versus surrogate key"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,650
    Rep Power
    171
    In general it looks like surrogate key (int) make table relations faster, and more economic in storage and update speed (even better, foreign keys do not need to be updated when using surrogate keys, in contrast with business key fields, that do change now and then).
    Originally Posted by r937
    if you replace the usage names with an id, you will have exactly the same amount of redundancy (with a little extra thrown in)
    What do you mean by extra thrown in?
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by zxcvbnm
    What do you mean by extra thrown in?
    there's an extra column, where there wasn't with natural keys

    also, surrogate keys are not necessarily more economic -- consider that when you go to retrieve data, you ~have~ to do the join to the lookup table in order to know which one it is

    it's the difference between this --

    john doe, 123 main street, cityname, 23

    and this --

    john doe, 123 main street, cityname, NY

    if you use NY (2-character natural state codes), you don't always have to do the join to the states table

    now that was a trivial example, but extend that concept (you ~have~ to do the join) to more realistic applications of surrogate keys, and you'll soon lose the idea that they're "more economic"
    Last edited by r937; April 15th, 2013 at 12:52 AM.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo