>Walter,

>I had written detailed responses to each of your points but I somehhow closed the browser without sending them! Unfortunately I have a lot to do today, so I can only re-write the conclusion:

Sad, I'd really like to have a detailed response.

>We are all programmers, but I'd guess that less than 10% of us are experts at database design. We can all throw together a 3rd normal database fairly easily, but how many times do we go the extra mile to create a database that is as flexible as the code we write (5th normal form)? We design class libraries with layers of class definitions that we don't necessarily need right now, but our experience tells us we will need those layers in the future. It's the same with databases.

>Ask yourself this question: When was the last time I updated a production database by adding tables without modifying the structure of any of the existing tables?

Well, with intelligent keys this is easier than you think. Since the foreign key for the new table probably already exists. For example, If you want a lookup table for city, I don't have to add fields in another table, because the cityname is the primary key in this table. To directly answer your question: Just last week (see postalcode example. The postalcode already existed in my tables. I had only to add a new table to the database and I was up and running. Try and do the same with surrogates !!

>I know that this has rarely happened for me. I always end up adding a field to tie in the new tables somewhere. But a fully normalized database would not require any changes to the existing tables.

I know, but its impractical to have 100s of tables while a few could do. I don't like to have table with only two fields. Not only for performance reasons, but also for readability, troubleshooting and protection (curruptions, crashes) reasons. The 5th normal form is impractical for most applications. Further I don't see what this would gain me ?

>Sometimes a fully normalized database places unwanted strain on resources.

yeah, in about 99% of the cases.

>Remember that you are supposed to design a fully normalized database FIRST, then remove the normalizations where the resource strain is highest.

Supposed, according to who ? Codd ? ... I'm perfectly capable of designing a database directly in boyce codd directly or another form which is best suited for the job (there is nothing wrong with controlled redundancy). IMO, the normalisation theory shows how databases in theory are the most flexible designed and which design problems there are, and how the can be avoided. However, this theory is not practical in the real world. Can you tell me of what percentage of all production database are normalized to the 5th normal form ? That would be close to zero. Why ? Because it's not practical.

>In this thread there was an inference that blindly using surrogate keys was a poor replacement for critical thinking. I'd say that using intelligent keys is a clear indication that a database is not fully normalized, and therefore has not been rigorously analyzed.

Full normalisation is IMO opinion something that falls in the same category of reaching full optimization of rushmore and blindly using surrogates: Not wise to do so. Think about what it means for your application, not just blindly apply normalisation rules.

>The only time it makes sense to not use a surrogate primary key is when we have not fully normalized the database.

I don't see what surrogates have to do with this. The 5th normal form is never a goal on itself and the 5th normal form can also be reached with a mixed approach use of surrogates and natural keys if, that is what you want (for some reason).

Walter,