Re: null as part of key?

From: Henning Follmann <h.follmann_at_gmx.de>
Date: Tue, 23 Oct 2001 07:56:13 -0400
Message-ID: <slrn9tamm8.hl.h.follmann@mobile.follmann-net>

On Mon, 22 Oct 2001 11:40:57 -0400, ed zappulla <zappullae_at_rcn.com> wrote:
> I see. What about this case
>
> country
> state
> city
> some other cols
>
> pk = country, state, city
>
> Some contries don't have states; therefore it would be null for those
> countries.
>

The problems you are just encountering show some flaws in your datamodeling.

do not use production data as a primary key

This is a good rule!

Suggestion:

Create a metadata table:
it includes for every country the meta information about address formats. Use for the countries the iso standard! Sooner or later you will be thankfull you did so!

now the rough design

Party : Party_ID, PartyType_ID

Address: Party_ID, AddressMeta_ID, ...

AddressMeta: AddressMeta_ID, Country_ID, Field1, Field1Flag (Mand, Opt, notuse) ...

Country: Country_ID (Num(3)), Name, A2, A3

I use also Party, because you might have Companies, private Persons ... A Party might have also more than one Address, so you might tweak it for your purposes.
It might also a good idea to place some rules for checking the data depending on the country. If you use MetaData this gives you the flexibility to stor the validation rules (names of procedures) in your table.

Good luck,
dealing with international addresses, currencies, etc. is not easy. I hope this helps.

> does this make sense?
>
>
>
> "Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message
> news:3bd42f49$0$237$ed9e5944_at_reading.news.pipex.net...

>> No. Nor in any version. If you think carefully you will see that allowing
>> this would be a nonsense.
>>
>>

Cheers,
henning

-- 
Henning Follmann      |     8 Jane Road
Tel.: +1 908 656 7061 |     New Providence, NJ 07974
H.Follmann_at_gmx.de     |     USA


-----=  Posted via Newsfeeds.Com, Uncensored Usenet News  =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
 Check out our new Unlimited Server. No Download or Time Limits!
-----==  Over 80,000 Newsgroups - 19 Different Servers!  ==-----

Received on Tue Oct 23 2001 - 06:56:13 CDT