Re: Oracle NULL vs '' revisited

> No. If the key changes, it is a new book! Keys can not change! (Want a new
> car? Get a new licence plate!)

So, a membership database - my user group (sqlserverfaq.com) for instance; I only capture this information because it's all I need and by law in the UK I can only capture what I need...

Full Name
Email Address
Company
Country of residence

What's the natural key?

Email Address is unique at a point in time.

Are you trying to tell me email address does not change? What happens when you move companies? You'd want to update your email address, in your data model you aren't allowed to do that so I guess you can't use Oracle.

What I do to get round this problem is to create a surrogate key - an auto generating number, that is fixed and never changes.

The natural key is still email address.

What is your solution? Is it an artifical key? But, why? The natural key (email) can only be unique, you cannot have two members with the same email address.

Or, would you create a composite key of start_date, end_date, email_address? Wow, the whole world suddenly gets more complicated then it needs to be.

-- 
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]