Re: Normalization, Natural Keys, Surrogate Keys

In your example you have perfectly good natural keys, that don't change often.

Also state codes are pretty much all you need for US and Canada states. Don't know for India, South Africa and other places.

In general, if you are confortable with an already existing schema with surrogate keys the best path to follow is to make sure you have the necessary constraints (unique indexes or unique constraints, they're almost the same thing, a unique index automatically enforces the constraint and the unique constraint automatically creates an unique index).

What happen if country code change ?
Well, this is extremely unlikely. You won;t be alone in having to deal with the problems, tons of other organization will suffer:) So, it's not going to change.

But even if it changes, because country code is a natural key there have to be more consideration before propagating the change to the database. Is the new country with the new country name and the new country code,
THE SAME as the old one? Maybe, maybe not. You wouln't want to make it as easy as to a mere user to change the data for the same surrogate key, and be happy that he solved the problem so easily :)

For example Soviet Union (SU) dissapeared and Russia (RU) is the legal inheritor. But maybe business systems at that time reflected better the two entities as two different states. Also other new countries appeared like Ukraine. So if you're database contained business partners from SU, some of them would be now from RU, others from Ukraine. These situations are inherently complex, a business decision has to be made and some stored procedures need to be written when something as fundam,ental as country and country codes change.

If only had was the surrogate key and the user changed from SU to RU for the same surrogate, then probably some companies from Ukrain would get moved to Russia. This example shows that when a natural key changes, business rules have to be in place to decide if the new entity is the "same" entity with another identification, or is something different.

Even if you find situations where these changes need to be legitimately propagated, these situations are rare. And after all, it is not the end of the world to write a stored procedure that takes care of it. So it's not worth for these rare things to sacrifice the quality of design for the whole.

"serge" <sergea_at_RemoveThis_ehmail.com> wrote in message news:<cuhI8.548$Sn2.394391_at_news20.bellglobal.com>...
> Sorry to jump in like this.
>
> I've been reading all the posts here and i keep reading some favoring
> Natural Keys and some favoring Surrogate Keys.
>
> I also read that some people who were favoring Natural Keys say that
> Surrogate Keys could be used in some instances. I read a number of
> posts saying Surrogate Keys should be used appropriately and not
> all the time.
>
> Me, i've been using only Surrogate Keys and I'm not here to argue
> whether it's good to use SKs or not since i'm not very experienced
> in this area.
>
> I have the following situation where i am thinking about switching a couple
> of tables to using Natural Keys instead of Surrogate Keys.
>
> (SQL Server 7 with Access 2000 Access Project .adp as the Front End)
>
> I have a table for Countries. I have about 240 country names in there.
> I have :
> - CountryNo (Surrogate Key that uses Identity-AutoNumber),
> - Country varchar 45
> - CountryCode char 2
>
> Should i delete the CountryNo and make the Country the PK of this table?
> If i do so, that means i will modify all references to CountryNo in other
> tables
> to Country? For example, i will store the Country name and not the CountryNo
> in my Customers, Suppliers tables?
>
> If I do switch from SK to NK in this example, what do i do when someone
> renames a country name for example? Do i write a SP that will go to
> each table where Country exists, update any references to the renamed
> country?
>
> Ok, i know Country names are not supposed to be renamed often, but for the
> sake
> of argument, let's say the user wants to rename "United States of America"
> to "USA"
> for example? This is a valid reason to rename a country. What do i do in
> this case
> if i have no Surrogate Key. I thought that if i was using a SK, i wouldn't
> worry
> about the country being renamed.
>
> What is your opinion on this?
>
>
> Another scenario is my States table.
> - StateNo (Surrogate Key, Identity)
> - State varchar 25
> - StateCode char 2
> - CountryNo tinyint
>
> I was using StateNo as the PK. I have an Acces form where a user chooses
> the country from the list of countries and sees all the states for the
> country
> in a subform.
> This is where i am confused on whether to switch to NK or stay with SK.
> If I allow a user to modify/add the states for the country, how do i prevent
> the user from entering the same State or the same StateCode for the same
> country? Currently, what i did so far is added two indexes on my tblStates:
> - CountryNo and State as Unique Constraint Index
> - CountryNo and StateCode as Unique Constraint Index
>
> This seems to be working when i try to add the same state for the same
> country
> or the same state code, i get an error message from SQL Server:
>
> "Violation of UNIQUE KEY constraint 'IX+tblStates'. Cannot insert
> duplicate key in object 'tblStates'."
>
> "Violation of UNIQUE KEY constraint 'IX+tblStates_1'. Cannot insert
> duplicate key in object 'tblStates'."
>
> My problem here is that i am unable to intercept this message in my Access
> form in order to modify the message into a more friendly message to the
> user.
>
> This started me thinking especially after following this thread for the last
> couple of weeks. Is it better to use a NK here also:
> - Company
> - State
> as a composite PK?
>
> Again, if i do this, how do i address the issue of updating all values of
> states
> in other tables when a state is renamed for example?
>
> Also, if Company+State are the PKs here, how do i address the issue
> of Company and StateCode should also be unique, meaning I can't allow
> the user of entering the same State Code more than once for the same
> country?
> Do i create a Unique Constraint Index for this?
>
> Another question I have is : should i think about using Constraints in this
> case
> rather than creating Indexes for Country and StateCode?
>
> I would really appreciate it if someone could tell me if i should switch to
> NK
> for these two tables and simply rely on code and SPs to update all renaming
> of countries and/or states throughout the database?
>
> My wish is that both people who favor SKs and people who favor NKs
> would comment here.
>
> Thank you very much
Received on Mon May 27 2002 - 22:05:38 CDT