I am designing a database, or rather redesigning a very old database and
have a question regarding setting up key fields. The old database has a
table called Equipment with two fields:
Equipment Code - text 8
Equipment Description - text 50
In the new design I will have a table called Manufacturers that will have an
Equipment Code field, which will link to the Equipment table to get the
description. My question is, should I make a new Integer key field called
say EquipID which is what would get stored in the Manufacturers table or
should I simply use the Equipment Code field as the key? What are the
advantages/disadvantages to each method?
I have several other tables with a similar situation where the 'Code' is
unique but there are more fields in these tables.
Thanks,
GerryThis is a subject that has spawned many heated debates. Here's my take on
it:
In your case, just use the codes that exist.
In general, if your data has a natural key, use it. If the natural key is a
composite key of sufficient length/complexity (a totally subjective
determination) there might be a good reason to use a surrogate. However,
you MUST also enforce the uniqueness of the natural key! I am not against
the use of surrogate keys at all, but they should be used only after much
careful thought and consideration. Surrogate keys tend, in the hands of the
inexperienced, to lend a false sense of security ("Of course I don't have
any duplicates, my surrogate key assures that!")
Since you have a simple natural key, there is really no reason not to use
it. Adding a surrogate key just creates more data...
"News" <gerrydyck@.shaw.ca> wrote in message
news:Q7cSb.328534$X%5.134270@.pd7tw2no...
quote:
> Hello All
> I am designing a database, or rather redesigning a very old database and
> have a question regarding setting up key fields. The old database has a
> table called Equipment with two fields:
> Equipment Code - text 8
> Equipment Description - text 50
> In the new design I will have a table called Manufacturers that will have
an
quote:|||There are no general rules or norms which recommend a specific datatype for
> Equipment Code field, which will link to the Equipment table to get the
> description. My question is, should I make a new Integer key field called
> say EquipID which is what would get stored in the Manufacturers table or
> should I simply use the Equipment Code field as the key? What are the
> advantages/disadvantages to each method?
> I have several other tables with a similar situation where the 'Code' is
> unique but there are more fields in these tables.
> Thanks,
> Gerry
>
a key.
The considerations to select a good key are often misunderstood. They
include stability (column values rarely change), simplicity (so that
relational operations can be effective), familiarity (meaningful or commonly
understood by the user) and irreducibility (no proper subset of key column
be another key). A good design can tradeoff certain characteristics in favor
of others to tackle specific issues with regard to key selection.
In a precisely modeled system, a key is chosen only based on the rules
defined at the business model & key selection involves only logical
considerations.
However, the implementation of databases using popular SQL DBMSs, generally
favors the usage of narrow keys for query efficiency, due to their smaller
size at the physical level. This may often fall under the criteria of
simplicity (mentioned above), but shuffling keys just for performance sake
is not always a good idea.
Anith|||Thanks Don. This is my first SQL database and sometimes with new programs I
tend to overthink a solution. For this one, I will be sticking with a
natural key.
Gerry
"Don Peterson" <no1@.nunya.com> wrote in message
news:uVsFYAq5DHA.2556@.TK2MSFTNGP09.phx.gbl...
quote:
> This is a subject that has spawned many heated debates. Here's my take on
> it:
> In your case, just use the codes that exist.
> In general, if your data has a natural key, use it. If the natural key is
a
quote:
> composite key of sufficient length/complexity (a totally subjective
> determination) there might be a good reason to use a surrogate. However,
> you MUST also enforce the uniqueness of the natural key! I am not against
> the use of surrogate keys at all, but they should be used only after much
> careful thought and consideration. Surrogate keys tend, in the hands of
the
quote:|||Thanks for your input Anith.
> inexperienced, to lend a false sense of security ("Of course I don't have
> any duplicates, my surrogate key assures that!")
> Since you have a simple natural key, there is really no reason not to use
> it. Adding a surrogate key just creates more data...
> "News" <gerrydyck@.shaw.ca> wrote in message
> news:Q7cSb.328534$X%5.134270@.pd7tw2no...
have[QUOTE]
> an
called[QUOTE]
>
Gerry
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:ex9OK3q5DHA.1596@.TK2MSFTNGP10.phx.gbl...
quote:
> There are no general rules or norms which recommend a specific datatype
for
quote:
> a key.
> The considerations to select a good key are often misunderstood. They
> include stability (column values rarely change), simplicity (so that
> relational operations can be effective), familiarity (meaningful or
commonly
quote:
> understood by the user) and irreducibility (no proper subset of key column
> be another key). A good design can tradeoff certain characteristics in
favor
quote:
> of others to tackle specific issues with regard to key selection.
> In a precisely modeled system, a key is chosen only based on the rules
> defined at the business model & key selection involves only logical
> considerations.
> However, the implementation of databases using popular SQL DBMSs,
generally
quote:
> favors the usage of narrow keys for query efficiency, due to their smaller
> size at the physical level. This may often fall under the criteria of
> simplicity (mentioned above), but shuffling keys just for performance sake
> is not always a good idea.
> --
> Anith
>
No comments:
Post a Comment