Meaningless vs Meaningful Primary Keys

[essaytee]

Also adding some fuel to the fire. Meaningless keys all the way with me, especially the junction tables, so much easier to reference one field.

My problem with natural keys is that the uniqueness is out of the hands of the developer.

Out of curiosity, were the findings of this thread published somewhere?

 

[isladogs]

What findings?
I know there was a strong suspicion that the OP was just using this thread to promote his website/book. He stopped posting here back in 2007.

 

[ButtonMoon]

isladogs,

Thanks for correcting my syntax. You are right.

I've not heard that referred to as composite alternate keys before

You said you understand the terms alternate and composite. What else would you call a key that is both alternate and composite? Do you believe those two terms are mutually exclusive? Oddly the author of this thread seems to think that only primary keys can be composite but I can't imagine why any one would think that. Textbooks on relational database basics are full of examples and concepts like Boyce-Codd Normal Form and join dependency can only be appreciated by understanding that keys consist of sets of attributes.

Keys fundamentally have nothing to do with indexes. A key is usually indexed and Microsoft Access happens to index keys automatically but in other software that isn't essential. What matters is that these are sets of columns where uniqueness is enforced and the key is irreducibly unique.

 

[isladogs]

I use the phrase 'composite index' to describe what that code does.
As you say its purpose is to ensure uniqueness.

From the responses by other 'seasoned' developers who've also responded, your phrase is not familiar to several of us.

 

[ButtonMoon]

I use the phrase 'composite index' to describe what that code does.
As you say its purpose is to ensure uniqueness.

From the responses by other 'seasoned' developers who've also responded, your phrase is not familiar to several of us.

Not my phrase. It was used by Mike Smart at the start of this thread. In fact I try to avoid the term "alternate" and I would usually just say composite key. According to Google Books there are 69 database and data modelling books containing the phrase "composite alternate key" and 8780 books containing "composite key".

Composite index isn't necessarily accurate because not all composite indexes contain composite keys. In any case an index obviously isn't a key: an index is a performance optimisation feature and a key is a set of attributes (columns).

 

[isladogs]

OK not your phrase either.
However I stick by the point that composite index perfectly describes what the create table code did.
I didn't use key in my explanation and I never use the word alternate with reference to access tables.

 

[gemma-the-husky]

Also adding some fuel to the fire. Meaningless keys all the way with me, especially the junction tables, so much easier to reference one field.

My problem with natural keys is that the uniqueness is out of the hands of the developer.

Out of curiosity, were the findings of this thread published somewhere?

I also posted a long while ago on this thread.

It's a tautology, but the trouble is that a meaningless key is by it's very definition meaningless. The one side of a join simply cannot be meaningless. At the very least it must be a congruent synonym for a corresponding set of values (fields) that are unique within the table.

So pragmatically, you have one customer to many orders. You may choose to use a surrogate autonumber key for the customer record, but there absolutely must also be a "real" value in the record that is unique. For a customer, probably the customer name, or customer account number, generally both.

Now the orders on the many side will be related to order lines in a one to may relationship. You may add a surrogate autonumber key to the orders table, but the truth is that there will also be some other unique field, such as the order number. It doesn't have to be there to make the database relationship work, but it definitely has to be there to model the real world.

 

[aaronkempf]

Proposed argument FOR meaningless key:

4) Sometimes, it is impossible/difficult to find a meaningful key that is GUARANTEED to be unique. I have encountered several cases where an assumed-to-be-unique key turned out out to be non-unique when more data was merged/linked in from other sources, completely messing up an otherwise good design. I suspect that this will be quite common in dbs that need to track the history of stuff.

5) Meaningless keys remove the temptation to use the PK field to contain multiple non-atomic fragments: "Since we have to have it, lets combine this concept and that concept and perhaps the first three characters of a third concept to 'guarantee' (see above) uniqueness." Violates normalization rules and/or duplication of data, but I have seen it done repeatedly.

It could be called a 'factless fact table'. I can't remember I think that is Ralph Kimball's invention. It might be Bill Inmons. they are collectively the 'godfaters of datamarts / datawarehousing'.

I've seen a ton of legit situations where a PK *COULD* be comprised of a half dozen columns, all at the same time. But on the MSSQL Server side, any additonal indexes have to include ALLLLLL six columns in order to uniquely identify a row. So I'm prone to use a PK on a surrogate (autonumber) field to make indexes skinnier, and thus smaller and faster.

Indexes are a LOT more important in MSSQL than they are in Access. I mean, in MSSQL, you can have an included column on an index. In Access, you have to COVER the column by adding another level. An index with 2 levels, that includes an additional 4 columns will almost ALWAYS be faster than the same index with 6 levels deep of columns. One method makes a freeway, and then it just stores additonal data in warehouses along the way. The Access method is more like building SIX roads. a highway is always faster than having to choose SIX different side streets to navigate.

 

[Pat Hartman]

aaron, In the situations where there are business rules to enforce, you cannot enforce them with a meaningless autonumber. You probably do want to use the autonumber as the PK and FK but you still need an index on the multiple field natural "key" to enforce business rules.