AutoNumber vs. Key

Hi,

I am always confused about what to use as the key for a table.

Let's say I have a company-employee table.

[company]---*[employee]

My co-worker likes to use an AutoNumber or Counter as the key for the
[employee] table (and everything).
I personally use an actual key set  for the [employee] table.

So, his table will have one (Autonumber or LONG) column as the key.  The
company_id is just another attribute.
Mine, on the other hand, has 2 columns as the key.  (i.e.) company_id +
employee_id

So, what is the deal?



--
> There is no answer.
> There has not been an answer.
> There will not be an answer.
> That IS the answer!
> And I am screwed.
> Deadline was due yesterday.
>
> There is no point to life.
> THAT IS THE POINT.
> And we are screwed.
> We will run out of oil soon.

always use an autonumber identity for everythign you do

single-column primary key

"For everything" is a pretty obscure answer. Other keys are important
too. In a joining table containing only two foreign keys for example
what purpose would IDENTITY serve?

Tom, for the debate you can Google for "Natural Key" and "Surrogate
Key" in this group and in microsoft.public.sqlserver.programming.

--
David Portas
SQL Server MVP
--

Hi,

Is there a reason for using one and only one column as the key?
I find it hard to enforce the integrity of the database with this design.

for example,
let's say the [company]-[employee]-[sale] tables.
company A has an employee B.
employee B makes a sale C.

If employee B moves to another company, the sale C in the [sale] table w
ill
move along with employee B to the new company.
This doesn't look correct to me.





<dbahooker@hotmail.com> wrote in message
news:1129059631.679988.51060@g47g2000cwa.googlegroups.com...
> always use an autonumber identity for everythign you do
>
> single-column primary key
>

Thanks for the actual terms for these key thingies.

I've searched google but found a lot of different opinions.
Some like autonumber but some like... huh... natural key.

To sum it up from all the posts I've read:
1)
The people who use autonumber said that it performed better and easier to
update the record without affecting the database integrity.  (imagine
someone needs to change his or her social security number!!!! all hell broke
lose!!!)

2)
The people who use natural key said that people who use surrogate were just
lazy and not experience with database design.  And using natural key could
better model database to the business model.

So... I don't know... that's why I am so confused.
One has physical advantage.  The other has logical advantage.



"David Portas" < REMOVE_BEFORE_REPLYI
NG_dportas@acm.org> wrote in message
news:1129060967.267313.134080@z14g2000cwz.googlegroups.com...
> "For everything" is a pretty obscure answer. Other keys are important
> too. In a joining table containing only two foreign keys for example
> what purpose would IDENTITY serve?
>
> Tom, for the debate you can Google for "Natural Key" and "Surrogate
> Key" in this group and in microsoft.public.sqlserver.programming.
>
> --
> David Portas
> SQL Server MVP
> --
>

Tom (nospam@yahoo.com)  writes:
> I am always confused about what to use as the key for a table.
>
> Let's say I have a company-employee table.
>
> [company]---*[employee]
>
> My co-worker likes to use an AutoNumber or Counter as the key for the
> [employee] table (and everything).
> I personally use an actual key set  for the [employee] table.
>
> So, his table will have one (Autonumber or LONG) column as the key.  The
> company_id is just another attribute.
> Mine, on the other hand, has 2 columns as the key.  (i.e.) company_id +
> employee_id

I would say that the two alternatives describes two different things.
In the one-column case, an employee is rather a person, and if he
moves to another company all data that is tied to him are brought with
him.

In the two-column case, you get a new row when he gets a new job.

The latter appears more useful to me. If we want to know who much sales
did everyone in company X generate last year, we can't tell with the
first setup, since we don't know who worked where that year.

If want to know how much aales Bob has generated over the years in
different company, maybe there should be a person table, to which
employees have a reference.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

I come to the same conclusion.  (see the 10/11/2005 1:03pm post)

As I search Google, this thing is getting very confusing.  All the posts
point to the one dead end conclusion -- "It depends".

I need something solid so I don't have to reinvent the wheel (or think about
it).  The "It depends" thing just doesn't cut it.



"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
 news:Xns96ECF3C9E8AE
7Yazorman@127.0.0.1...
> Tom (nospam@yahoo.com) writes: 
>
> I would say that the two alternatives describes two different things.
> In the one-column case, an employee is rather a person, and if he
> moves to another company all data that is tied to him are brought with
> him.
>
> In the two-column case, you get a new row when he gets a new job.
>
> The latter appears more useful to me. If we want to know who much sales
> did everyone in company X generate last year, we can't tell with the
> first setup, since we don't know who worked where that year.
>
> If want to know how much aales Bob has generated over the years in
> different company, maybe there should be a person table, to which
> employees have a reference.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>

"Tom" <nospam@yahoo.com> wrote in message
news:hiY2f.481$7h7.465@newssvr21.news.prodigy.com...
> I come to the same conclusion.  (see the 10/11/2005 1:03pm post)
>
> As I search Google, this thing is getting very confusing.  All the posts
> point to the one dead end conclusion -- "It depends".
>
> I need something solid so I don't have to reinvent the wheel (or think
 about

> it).  The "It depends" thing just doesn't cut it.
>


"It depends"  .

Basically I tend to agree with Celko's philosophy that wheer possible the
key should model reality.  If your employees already have some sort of
employee number assigned, use that.

That said, even in many of my tables with natural keys I'll drop in an
Identity column as it can be useful for some ad-hoc queries.


>
>
> "Erland Sommarskog" <esquel@sommarskog.se> wrote in message
>  news:Xns96ECF3C9E8AE
7Yazorman@127.0.0.1... 
The 
>
>

Just my .02:

If you have a natural key that is VERY constant (more constant than a
name, because names can change) and is simple, use it.  If you don't,
use a surrogate key, but be sure that your application and database can
handle data verification (avoiding duplicate records, etc).

Stu

Tom (nospam@yahoo.com)  writes:
> I come to the same conclusion.  (see the 10/11/2005 1:03pm post)
>
> As I search Google, this thing is getting very confusing.  All the posts
> point to the one dead end conclusion -- "It depends".

Yes, that is a very common answer in the database world. And that does
not only apply to data modelling, but also how you should apply indexes,
write queries, implement backup strategies, you name it. It all matters
very much on the actual situations, and which trade-offs you want to make.

So in the end, it boils down do common sense and gut feeling for the
actual case at hand. What is bad is to follow a checklist that says
"Always do X".

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp