April 14th, 2013, 12:13 AM
Is this a better design?
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!
April 14th, 2013, 06:10 AM
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"
April 14th, 2013, 10:02 PM
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).
What do you mean by extra thrown in?
Originally Posted by r937
April 15th, 2013, 12:50 AM
there's an extra column, where there wasn't with natural keys
Originally Posted by zxcvbnm
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.