Posted by Ed Prochak on 03/07/06 20:40

Bill Karwin wrote:
> "Ed Prochak" <edprochak@gmail.com> wrote in message
> news:1141752316.933161.102260@i39g2000cwa.googlegroups.com...
> > My point is too many people immediately jump to ID fields as the PK.
>
> That's certainly a good point. However, I've worked on projects in which
> the decision-makers wouldn't commit to _any_ combination of attributes that
> would uniquely identify the entity. There were always cases where the value
> in any column could be either non-unique, or else have no value specified
> (i.e. NULL). Neither would they commit to any attributes that could be
> reasonably stable and unchanging (though I understand that this is not
> strictly necessary for a key).

It's not necessarily something the End users or even managers should
make the final decision upon. I one really big project I had to good
fortune of working with a real Data Modeler. No she did not
programming, only modelling. She interviewed the users to find out what
data they had, what data they wanted and how they used it. She
organized it and created the ER model. The model included enough
information to design the DB model, which is when the PK's were
determined. Almost all of the cases you mentioned were encountered.
Each was handled by the DB designers. There are times when you create a
pseudokey and times when you split the logical table
maybe to model a more generic entity you create a pair of
parent/child tables where the parent lacks one of the fields that might
be NULL if only the Child table existed, or
maybe the entity really was 2 entities that looked similar, as an
address that represents the customer's location and the adddress that
represents the billing address. A DB that application that needed both
would be poorly served by one ADDRESS table.
What happens internally to the DB does not matter as long as you can
present the right data in the right combinations.

It is our job to engineer the software, not the end users and managers.

>
> So in those kinds of situations, I felt I had to create pseudokeys to have
> any chance of the application working. Even if we know the best practices
> for database modeling, the project on which we are working may have
> constraints that don't allow us to follow those best practices.
>
> Regards,
> Bill K.

Sounds like you take pseudokeys as the last resort, which IMO is
exactly what they are. That approach is best practices. Keep it up.

Ed

[Back to original message]