create table dbo.parts (
    part varchar(10) not null,
    constraint pk_parts primary key clustered (part) )

create table dbo.warehouses (
    warehouse varchar(10) not null,
    constraint pk_warehouse primary key clustered (warehouse) )

create table dbo.partlocations (
    part varchar(10) not null,
    warehouse varchar(10) not null,
    quantity int not null,
    fax varchar(20) not null,
    constraint pk_partlocatoins primary key clustered (part,warehouse),
    constraint fk_partlocations_part foreign key (part) references parts(part),
    constraint fk_partlocations_warehouse foreign key (part) references warehouses(warehouse) )
go

• The fax number for the warehouse is repeated in every row that refers to a part stored in that warehouse.


• If the fax number of the warehouse changes, every row referring to a part stored in that warehouse must be updated.


• Because of the redundancy, the data might become inconsistent, with different records showing different fax numbers for the same warehouse.


• If at some point in time there are not parts stored in the warehouse, there may be no row in which to keep the warehouse’s fax number

create table dbo.parts (
    part varchar(10) not null,
    constraint pk_parts primary key clustered (part) )

create table dbo.warehouses (
    warehouse varchar(10) not null,
    constraint pk_warehouse primary key clustered (warehouse) )

create table dbo.partlocations (
    rowid int identity(1,1) not null,
    part varchar(10) not null,
    warehouse varchar(10) not null,
    quantity int not null,
    fax varchar(20) not null,
    constraint pk_partlocatoins primary key clustered (rowid),
    constraint fk_partlocations_part foreign key (part) references parts(part),
    constraint fk_partlocations_warehouse foreign key (part) references warehouses(warehouse) )
go

quote:

---

Well,

It's been a while since I have had "academic" discussions like this, so I am liable to get my Normal Forms wrong. However, If you put fax in the warehouse table is it not then in (at least) 3NF?

Because quantity is not dependant on anything (except the combination of warehouse and part).

-Chad

---

create table dbo.parts (
    i int identity,
    part varchar(10) not null,
    constraint pk_parts primary key clustered (i),
    constraint uk_parts unique (part) )

create table dbo.warehouses ( 
    i int identity,
    warehouse varchar(10) not null,
    fax varchar(20) not null,
    constraint pk_warehouses primary key clustered (i),
    constraint uk_warehouses unique (warehouse) )

create table dbo.partlocations (
    i_parts int not null,
    i_warehouses int not null,
    quantity int not null,
    constraint pk_partlocatoins primary key clustered (i_parts,i_warehouses),
    constraint fk_partlocations_part foreign key (i_parts) references parts(i),
    constraint fk_partlocations_warehouse foreign key (i_warehouses) references warehouses(i) )

create table banner_clicks (i int identity,
                            i_advertisers int NOT NULL,
                            i_sites int NOT NULL,
                            when datetime DEFAULT(GETDATE()))

quote:

---

Plus, I'll never believe you can't find a natural key. If two rows are truely identical, then either you have poorly designed your database or you don't have two things to store ... you only have one.

---

quote:

---

that way, part names and warehouse names can change without forcing an update of every partlocation row

---

The problem with that statement is that the ability to change a non-key attribute like warehouse name is considered to be a convenience or necessity, and the usual screaming argument I'd make is "but it's NOT THE SAME WAREHOUSE THEN!!!!" While that may not strictly be true, the greater problem is illustrated in the OTHER thread recently dredging up identity, about having an engineer or consultant be able to change their name too.

Well, here's a problem: Elizabeth Taylor can change her name to Captain Kangaroo, but doing so will not MAKE HER Captain Kangaroo. That's a huge problem! Is it just a name change, or is Captain Kangaroo taking over Liz's sales of White Diamonds? These are two completely different situations, and the database NOT ONLY ALLOWS IT but CAN'T TELL THE DIFFERENCE BETWEEN THEM. It's one thing to permit the changing of address, phone number, email address and such, but there needs to be some greater consideration of which attributes/columns SHOULD BE ALLOWED to change.

An even greater illustration of how that could really FUBAR your data: suppose Captain Kangaroo already was in the database under another ID? What's the scoop then? Did he clone himself? Is Liz still Liz but calling herself Captain? Did they both achieve such a level of spiritual union that they are now blissfully one entity, one mind, one soul, one spirit, and the stupid DBA just forgot to delete the dupe row? You can't tell, and it's all because you allowed the name column to be updated. And let's not talk about what happens to Liz's sales and commission history if cascade update is turned on...hell, even if it's off...the commissions now go to Bob Keeshan, and always HAVE.

If anyone thinks that these are minor concerns that rarely happen, I offer the immortal words of Santa Claus: HO HO HO! Stick around the database world another 15 minutes or so, and you'll see how often it really DOES happen, and how hard it is to fix it.

Joe Celko (I think it was him) had quoted an old saying in one of his books about the family ax: it was handed down from one generation to another, and while the handle had been changed several times, and the head replaced too, it was still the same old family ax! It begs the question: if everything else except the key can be changed, what exactly is that key identifying anyway?

Well, so much for the QUICK comment.

quote:

---

suppose Captain Kangaroo already was in the database under another ID? What's the scoop then? Did he clone himself? Is Liz still Liz but calling herself Captain?

---

quote:

---

In this scenario, yes, Liz is still Liz but *is* calling herself captain.

---

Unfortunately, I wasn't actually asking the question, I was pointing out that as illogical as it seems, Captain Kangaroo CAN clone himself, and the CAN merge with Liz into one being, and there's nothing in your data that indicates what happened. I'm not debating whether using a surrogate key makes it easier to do that, I'm questioning the wisdom of using that as a validation to use a surrogate key.

YOU know that when an employee gets married and changes their name that they're the same person, BUT some degenerate database corrupter could simply change the data indiscriminantly, and what looks like a name change is really a personnel reassignment (which may or may not be permitted, according to the "business rules"...they do exist outside of the computer too!)

It has nothing to do with making it easy to change someone's name, that's not the problem; it's that allowing such a feature allows someone to corrupt the data as well. Simple test:

UPDATE Personnel SET FirstName='John', LastName='Smith'

You can't tell me that being able to run that is a desired feature. Everyone has run at least one unwanted UPDATE without a WHERE clause and couldn't restore a backup to fix it. Since name is not part of a key anymore:

quote:

---

So my argument is not that every identity column compromises data integrity, but rather that the use of identity columns can falsely normalize a table and give a false sense of protection against update anomalies and data inconsistencies

---

This last example demonstrates perfectly what Jay is talking about. Most designers simply add the identity column, sing the praises of its ability to allow changes without having to redo everything, and then rip their hair out months or years later when they have tons of dupes and garbage data. Take a look at all the posts on SQL Team for "how can I removed duplicates"? You'll find NOT ONE of them used some kind of natural key, and 99% used identity or GUID.

quote:

---

but there's a lot to be said for *not* tying the primary key to something like a name that can (and will, eventually) change.

---

quote:

---

UPDATE Personnel SET FirstName='John', LastName='Smith'

You can't tell me that being able to run that is a desired feature. Everyone has run at least one unwanted UPDATE without a WHERE clause and couldn't restore a backup to fix it.

---

Oh, I hear ya. However, this is just as strong an argument for only allowign data access through stored procedures as it is for your actual point . Besides, even using the natural key, unwanted updates can happen, just not on the primary key. If you say they key is FirstName,LastName, there's nothing to keep someone from running

update personnel set phone_number='2125551212'

...so I'm not sure that the "natural key as a protection against bad updates" argument holds water .

quote:

---

Key selection is probably the most important part of database design (physical or logical). That's why we have normalization. There are specific documented rules for design. Using an identity column is tantamount to saying, "Codd be damned ... I don't need these stinking rules ... I too smart to make mistakes." That may be true for some, but I think as a field, we should push people to learn and practice normalizatin first, and cowboy design once they understand and can apply the theory.

---

Ok, I strongly agree with your statement about key selection, but I still disagree with you conclusion you get from it. But, hey, I've got an open mind. So please help me see the light by explaining how you'd use a natural key in the banner logging scenario, where the table (minus identity) looks like this:

create table banner_clicks (when datetime defailt(getdate()),
              banner id int not null,
              ip_address varchar(15) not null)

Feel free to add other common logging columns (HTTP_USER_AGENT, etc). The idea is that you need to log banner clicks and have a way to refer back to the click that generated a sale for analysis purposes.

Remember that, especially with AOL, it's completely possible that two different people will click on the same banner at the same millisecond from the same IP address (and potentially the same browser, etc). Most of the time that won't be the case, of course, but on a busy site, it *will* happen.

Even if you change the scenario and add some kind of user ID ("the person has to be logged in"), it's *still* possible that two people will be using the same user ID to do the same thing at the same time. They shouldn't be, of course, but you can't let that kind of funky external behavior cause DB problems or corrupt sales data.

I'm definitely open to seeing another way here, but so far, it just doesn't seem right... at least, not for every case.

Cheers
-b

quote:

---

Therefore to update a primary key you must delete the row and insert a new one.

---