Re: Q: Historical primary key

Barry Johnson wrote:
>
> Roger Wernersson (roger.wernersson_at_adra.se) wrote:
> > ...I want to be able to schedule
> > address changes...
> > ...I want a scheduleable phone number
> > table...
> Based on requirements as stated, just as you suggest...except I
> might add the Customer_Valid_Date in your original draft to the
> Customer shown above if that is still required....and I might
> rename Customer_H and Phone_H to something like Customer_Address
> and Customer_Phone respectively. And maybe remove ph# from the
> primary key if I only wish to have one effective at a time? Or
> remove the first table and make Phone point to Address if you
> don't wish to know of someone without an address...etc., etc.
>
> A question with which I have wrestled for years: whether to
> unambiguously name such fields (i.e., Address_Valid_From,
> Phone_Valid_From) or assume a column definition must always be
> contextual (i.e., Address.Valid_From, Phone.Valid_From).

I prefer to rely on context, i.e. I have columns like NAME.

>
> By the way, what you are tackling here is 4th Normal Form: if 2
> non-key attributes do *not* have the *same* relationship to the
> key, split them into separate tables. I find this is usually
> dictated as soon as one adds validity timeframe information in
> the way discussed here...or it makes it easy to add such later
> if one did the 4NF thing based on proper data definitions
> anyway :-)

OK, but how do I handle foreign keys? Is primary key only tables recommended?

-- 
Sport radio: people listening to people watching people having fun
Mailto:roger.wernersson_at_adra.se
BTW: All opinions are mine, all mine, and nobody's but mine.