AutoNumber vs. Key

[Tom]

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?



--[color=blue]
> 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.[/color]

[dbahooker@hotmail.com]

Re: AutoNumber vs. Key

always use an autonumber identity for everythign you do

single-column primary key

[David Portas]

Re: AutoNumber vs. 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.publi c.sqlserver.pro gramming.

--
David Portas
SQL Server MVP
--

[Tom]

Re: AutoNumber vs. Key

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 will
move along with employee B to the new company.
This doesn't look correct to me.





<dbahooker@hotm ail.com> wrote in message
news:1129059631 .679988.51060@g 47g2000cwa.goog legroups.com...[color=blue]
> always use an autonumber identity for everythign you do
>
> single-column primary key
>[/color]

[Tom]

Re: AutoNumber vs. 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_ REPLYING_dporta s@acm.org> wrote in message
news:1129060967 .267313.134080@ z14g2000cwz.goo glegroups.com.. .[color=blue]
> "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.publi c.sqlserver.pro gramming.
>
> --
> David Portas
> SQL Server MVP
> --
>[/color]

[Erland Sommarskog]

Re: AutoNumber vs. Key

Tom (nospam@yahoo.c om) writes:[color=blue]
> 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[/color]

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@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Tom]

Re: AutoNumber vs. Key

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@sommars kog.se> wrote in message
news:Xns96ECF3C 9E8AE7Yazorman@ 127.0.0.1...[color=blue]
> Tom (nospam@yahoo.c om) writes:[color=green]
>> 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[/color]
>
> 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@sommarsk og.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp
>[/color]

[Greg D. Moore \(Strider\)]

Re: AutoNumber vs. Key


"Tom" <nospam@yahoo.c om> wrote in message
news:hiY2f.481$ 7h7.465@newssvr 21.news.prodigy .com...[color=blue]
> 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[/color]
about[color=blue]
> it). The "It depends" thing just doesn't cut it.
>[/color]


"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.

[color=blue]
>
>
> "Erland Sommarskog" <esquel@sommars kog.se> wrote in message
> news:Xns96ECF3C 9E8AE7Yazorman@ 127.0.0.1...[color=green]
> > Tom (nospam@yahoo.c om) writes:[color=darkred]
> >> 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.[/color][/color][/color]
The[color=blue][color=green][color=darkred]
> >> company_id is just another attribute.
> >> Mine, on the other hand, has 2 columns as the key. (i.e.) company_id +
> >> employee_id[/color]
> >
> > 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@sommarsk og.se
> >
> > Books Online for SQL Server SP3 at
> > http://www.microsoft.com/sql/techinf...2000/books.asp
> >[/color]
>
>[/color]

[Stu]

Re: AutoNumber vs. Key

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

[Erland Sommarskog]

Re: AutoNumber vs. Key

Tom (nospam@yahoo.c om) writes:[color=blue]
> 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".[/color]

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@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Steve Jorgensen]

Re: AutoNumber vs. Key

On Tue, 11 Oct 2005 20:44:08 GMT, "Tom" <nospam@yahoo.c om> wrote:
[color=blue]
>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.[/color]

Unfortunately, all any of us can do is add more opinions to the pile, which is
not likely to generate any final closure, just give you more to ponder. I'll
try to offer what I think is useful, though.

- A table may have one key or more than one "key". It's not so much a
quesiton of whether a table should have a logical or surrogate key as whether
it should have a surrogate key in addition to one or more logical keys, and
use the surrogate key for joins.

- When multiple levels of 1-to-many relationship exist, surrogate keys can
prevent adding additional key fields at each level and coupling fine levels of
detail too closely to the models at courser levels.

- When a table has a surrogate key that is always of the same type and
following the same naming convention, it's easier to generate reusable
client-side code that can do things like keep track of a record, requery it,
save updates after an off-line editing session, etc.

- Surrogate keys tend to be 32-bit numbers that are fairly small, and easy for
the database engine to process.

- Using cascading updates to handle logical key change propagation can result
in very expensive updates with wide-ranging run-time side effects.

- Using surrogate keys to identify record links across subsystems can lead to
excessively tight coupling and interdependency . If 2 subsystems should be
relatively autonomous, they should very likely -not- reference each other's
records by internal surrogate key. Instead, they might use the logical key,
or they might use some kind of globally unique identifier. They should also
be built to tolerate broken links.

[Steve Jorgensen]

Re: AutoNumber vs. Key

On Tue, 11 Oct 2005 20:03:49 GMT, "Tom" <nospam@yahoo.c om> wrote:
[color=blue]
>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 will
>move along with employee B to the new company.
>This doesn't look correct to me.[/color]

How does this problem relate to the use of logical vs surrogate keys? The
real, problem is that this model does not track what company the sale is made
for, and traversing the employee doesn't tell what company that is, it tells
what company the employee who made the sale -currently- works for. [sale]
needs its own direct link to [company] if that information needs to be
retrieved later.

[DA Morgan]

Re: AutoNumber vs. Key

dbahooker@hotma il.com wrote:[color=blue]
> always use an autonumber identity for everythign you do
>
> single-column primary key[/color]

And this advice is based on what computer science class taught where?
--
Daniel A. Morgan

http://www.psoug.org

damorgan@x.wash ington.edu
(replace x with u to respond)

[--CELKO--]

Re: AutoNumber vs. Key

Your whole mental model of RDBMS is wrong.

Exposed locators such as IDENTITY are not surrogate keys. A surrogate
is totally hidden from uers and maintained by the system -- think of
how an index works. If you change a natural key, the DRI actions will
cascade it for you. Hell does not break loose. But if your autonumber
and real key are out of synch,then you are screwed. Just enter the
same record several times to get different autonumbers on duplicate
rows. Drop all but one of the dups and then try to find all of the
referenced rows in other tables.

If you maintain the exposed locator by hand, you have extra work, extra
disk seeks and will eventually make a mistake, thus destroying your
data integrity. How would you validate and verify your data?

The UPC code just went from 10 to 13 digits. It does not matter if you
used an autonumber or the UPC, the UPC has to be updated. If you had
used a proper RDBMS design and SQL-92, you would do an ALTER DOMAIN and
a single update with CASCADE. This is one of the MANY reasons that
rows are not records. This is a problem in SQL Server because it is
still based on a contigous storage model, but other products are not,
so wind up using tools to write scripts.

Finally have you bothered to time the two approaches with small and
large tables? It does not make a difference until the table has to do
a lot of paging and redundant autonumbers accumulate. This is the
21-st century; we have 32 and 64 bit hardware, nano-second speeds,
multi-word instructions, parallel processing and really good disk
drives.

[Tom]

Re: AutoNumber vs. Key

thanks for the tips.

But I need to follow the specs. The specs says that cascade update or
delete is not supported (along many other things like joinning tables is not
allowed). And it comes to a point that I have to say screw this. I am
going to use autonumber.

I am trying to look for any sounded and proven pattern. But searching
Google gives me only the "it depends" solution.



"--CELKO--" <jcelko212@eart hlink.net> wrote in message
news:1129224960 .345778.104210@ z14g2000cwz.goo glegroups.com.. .[color=blue]
> Your whole mental model of RDBMS is wrong.
>
> Exposed locators such as IDENTITY are not surrogate keys. A surrogate
> is totally hidden from uers and maintained by the system -- think of
> how an index works. If you change a natural key, the DRI actions will
> cascade it for you. Hell does not break loose. But if your autonumber
> and real key are out of synch,then you are screwed. Just enter the
> same record several times to get different autonumbers on duplicate
> rows. Drop all but one of the dups and then try to find all of the
> referenced rows in other tables.
>
> If you maintain the exposed locator by hand, you have extra work, extra
> disk seeks and will eventually make a mistake, thus destroying your
> data integrity. How would you validate and verify your data?
>
> The UPC code just went from 10 to 13 digits. It does not matter if you
> used an autonumber or the UPC, the UPC has to be updated. If you had
> used a proper RDBMS design and SQL-92, you would do an ALTER DOMAIN and
> a single update with CASCADE. This is one of the MANY reasons that
> rows are not records. This is a problem in SQL Server because it is
> still based on a contigous storage model, but other products are not,
> so wind up using tools to write scripts.
>
> Finally have you bothered to time the two approaches with small and
> large tables? It does not make a difference until the table has to do
> a lot of paging and redundant autonumbers accumulate. This is the
> 21-st century; we have 32 and 64 bit hardware, nano-second speeds,
> multi-word instructions, parallel processing and really good disk
> drives.
>[/color]