Re: Using ID numbers in a database to relate to a different table

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Drew (dlaing_at_NOswvtc.state.va.SPAMus)
Date: 07/30/04 

Date: Fri, 30 Jul 2004 13:04:50 -0400

Is there any performance gain when using surrogate keys? I think I am going
to have to do it her way for now, but with the restructuring of IT employees
in my state, she will only be my boss until March 2005. After that time, I
will just switch everything over. I totally agree with the answers given,
and appreciate the posters for helping me out, but I believe it may be a
lost cause. I wish she would just let me do it my way, and if it ever
screws up (which it won't) she can say, "I told you so!"... but she won't...
she thinks she knows what is going on, but I think it is just for show.

Thanks,
Drew

"Adam Machanic" <amachanic@hotmail._removetoemail_.com> wrote in message
news:eMOFoXldEHA.1652@TK2MSFTNGP09.phx.gbl...
>
> "Drew" <dlaing@NOswvtc.state.va.SPAMus> wrote in message
> news:%232XBZQldEHA.3944@tk2msftngp13.phx.gbl...
> >
> > doesn't listen. Can you all give me some good, valid reasons why I
should
> > use ID numbers?
>
> First of all, just to get your terminology straight, the ID in this
case
> is referred to as a "surrogate key". The key is a surrogate for the
actual
> department name in the Departments table, and there are a few reasons that
> this is generally a useful thing to do, all having to do with the central
> goal of a DBMS: Data integrity.
>
> First we have the issue of decentralized management of a type. If you
> store the department name on every row with the employee, there is a
chance
> that they could get out of sync with one another. One employee might be
in
> the "Accounting" department, while another is in the "Accounts"
department.
> Oops, someone made a typo. Now how do we figure out how many employees we
> have in accounting? Storing all of the known, correct names in a single
> table does away with this issue.
>
> Which brings us to the second problem. We could create a table with
> only one column, DepartmentName, and then create a foreign key in the
> Employees table to make sure that the name is always a correct name from
> that table, and then we would not have name disprepencies. However, this
> would create an update anomoly: Let's say that you wanted to change the
> name of the "Software Development" department to "Software Engineering".
> You'd have to first create a new row in the Departments table for Software
> Engineering, then update every referencing row in the Employees table,
then
> finally delete the old row from the Departments table. Quite a bit of
work!
> But if you had a surrogate key in place, you'd only need to update the
> "Software Development" row to say "Software Engineering", and the change
> would propagate automatically based on the key relationships.
>
> This creates a very clean design; the Employees table knows about
> employees, and the Departments table knows about departments. The tables
> are loosely coupled and the data is independantly managed.
>
>