The great primary-key debate

http://builder.com.com/5100-6388-1045050.html

I'm just wondering what your opinions are?

Meaningless values all the way - I think using a "natural key" will get you into trouble should your DB dramatically increase in size.

I prefer surrogate DB generated primary keys, but I will be happy for more arguments for both views. This because I in the near future will have to defend my choice of using surrogated primary keys in a article in information management I wrote at school. (I'm studying marketing management on my "old days", not IT).

In this article I recommended using surrogate primary keys when creating a fictive solution for an insurance company, and I know the teacher doesn't agree (he doesn't do programming and build databases himself, but is using the book written especially for this education as the "truth"). Since there are many things in this book that is totally wrong, I just had to go against the book on this area also. :-)

What do you mean by surrogate PK's?

(PS - Good topic)

Surrogate PK's is explained at page two in the article:
http://builder.com.com/5100-6388_14-1045050-2.html

Ahh - auto-incrementing id's - yup - I was always taught to use them (never heard the term "surrogate key" before though) and hadn't even considered using anything else. Like the article mentions - if for some reason your business rules change, you could definately be up the creek if you were using a natural key.

The problem found with meaningless keys results in a database where all records are added to the end of it. This reduces efficiency when accessing sets.

A lot of database software, can organise data into the database by primary key so that adjacent primary keys are adjacent in the database. Using surrogate keys prevents this from happening,.

It is even worse for some DBMS, where they use chained blocks (always inserted at the end of the database) to allow for record expansion (use of VARCHAR). This means that to read a row, it may have to do a full seek from the beginning to the end of the database to get the first record. The second record would have a similar problem as that would have been inserted at the end, because of the surrogate key.

(I know that only two records is a bad example and that there woudl be no beginning and end, but if you consider it is the first row of a result set and the second row of a result set, then the description makes more sense).

I know of one system where surrogate keys are used exensively and it uses 3 IBM z/Servers to manage a subset of a billing system. and has 5 times the operating costs of similar (legacy) systems which dont use surrogate keys.

Normalisation is fundamental to RDBMS performance, it is how the software and the entire DBMS has been designed. If you violate normalisation rules you will suffer from severe performance problems once the database begins to reach any sort of size.