A definition of terms. Note that all of the following are Data Models.
[list=1][*]An Entity Relationship Diagram (ERD) only shows entities and relationships between entities. It is the simplest logical model.[*]A Key-Based Model shows the entities, their primary keys, alternate keys, foreign keys and the relationships between entities. It is the intermediate logical model.[*]A Fully Attributed Model shows entities, keys, non-key attributes and the relationships between entities. It is the final logical model.[*]A Physical Model converts entities to tables, attributes to columns and applies the constraints of the host RDBMS to the Logical Model.[*]The Database Schema is the implementation of the Physical Model on the host RDBMS.[/list=1]
I hope the survey can guide our discussion somewhat.
my understanding is that surrogate keys are assigned only in step 4, physical model
could be wrong, though (wouldn't be the first time)
note surrogate key is not the same thing as alternate key
Is that to ensure that all of the attributes required to uniquely identify a tuple are present? Why can't surrogate keys be assigned at this point?
also sometimes to reassure DBAs afraid of primary keys that might change values
That's what I was thinking of. My thought was that we should plan for the possibility of primary keys changing values, and use surrogate key(s) with unique constraints on the primary key attribute(s) if they could possibly change.
Surrogate Keys can be left for the physical model phase 4.
During the key based modeling phase 2 surrogates can be introduced if you want. Some advocates of surrogates recommend making all primary keys for all entities surrogate keys from the start. There are advantages to this as well as risks. It can alleviate you of composite keys, but it can also make certain relationships untenable in large models and hide the composition of the natural keys.
I like surrogates first. I use them alot and I have been happy with the results.
The draw back of using natural keys in the logical model and introducing comprehensive surrogate primary keys in the physical model is the effort required to rewire all the relationships from the natural to surrogate keys. If you have more than one hundred tables its like swallowing a lump of oatmeal.