database design - keys

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
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...
> 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
> 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
>|||There are no general rules or norms which recommend a specific datatype for
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|||You may also want to consider this:
Do you have any manufacturers that manufacture more than
one piece of equipment found in the Equipment table?
This is usually the case. You may want to use some
unique identifier per Manufacturer, and add the
maufacturer's ID field to the Equipment table.
This could get even trickier since you may have mulitple
manufacturer's supplying multiple types of equipment, in
which case you may want to create a third table which
would have fields for the Manufacturer ID and the
Equipment ID to implement the many to many relationship.
Just a couple of thoughts.
Matthew Bando
matthew.bando@.CSCTGI(remove this).com
>--Original Message--
>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
>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
>
>.
>|||You may also want to consider this:
Do you have any manufacturers that manufacture more than
one piece of equipment found in the Equipment table?
This is usually the case. You may want to use some
unique identifier per Manufacturer, and add the
maufacturer's ID field to the Equipment table.
This could get even trickier since you may have mulitple
manufacturer's supplying multiple types of equipment, in
which case you may want to create a third table which
would have fields for the Manufacturer ID and the
Equipment ID to implement the many to many relationship.
Just a couple of thoughts.
Matthew Bando
matthew.bando@.CSCTGI(remove this).com
>--Original Message--
>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
>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
>
>.
>|||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...
> 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
> 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...
> > 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
> > 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
> >
> >
>|||Thanks for your input Anith.
Gerry
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:ex9OK3q5DHA.1596@.TK2MSFTNGP10.phx.gbl...
> There are no general rules or norms which recommend a specific datatype
for
> 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 Matthew.
At this point, there will only be one equipment code for one manufacturer.
Although I am having meetings next week which will confirm this. The old
database had one-to-one but anytime you update a program or database then
that is the time to think about such things. Perhaps they don't know the
possibilities after having used a paradox database for 12 years!
Gerry
"Matthew Bando" <anonymous@.discussions.microsoft.com> wrote in message
news:740c01c3e738$180f8140$a401280a@.phx.gbl...
> You may also want to consider this:
> Do you have any manufacturers that manufacture more than
> one piece of equipment found in the Equipment table?
> This is usually the case. You may want to use some
> unique identifier per Manufacturer, and add the
> maufacturer's ID field to the Equipment table.
> This could get even trickier since you may have mulitple
> manufacturer's supplying multiple types of equipment, in
> which case you may want to create a third table which
> would have fields for the Manufacturer ID and the
> Equipment ID to implement the many to many relationship.
> Just a couple of thoughts.
> Matthew Bando
> matthew.bando@.CSCTGI(remove this).com
>
> >--Original Message--
> >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
> >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
> >
> >
> >.
> >