Re: Help, newbie question on data structure and atomicity

On 14 Feb 2002 06:41:06 -0800, webjunk4me_at_hotmail.com (Chi) wrote:

>Hi,
>
>I have a table that must facilitate English and French salutations such
>as Mr, Mrs, Monsieur, Madame, etc. I have two methods in which I can do
>this:
>
>method 1:
>
>SALUTATION
>----------------
>SALUTATION_ID (PK)
>SALUTATION_ENGLISH
>SALUTATION_FRENCH
>
>===================
>
>Method 2:
>
>SALUTATION
>----------------
>SALUTATION_ID (PK)
>SALUTATION
>LANGUAGE (FK)
>
>In this case I would have a separate table named "LANGUAGE" that would
>look like:
>
>LANGUAGE
>--------------
>LANGUAGE_ID (PK)
>LANGUAGE
>
>What I am wondering is which way is better to go and why? (please make
>reference to normalization if necessary, I wanna learn this!). Note that
>I need the Language table for something else so it's not just ceated for
>this.
>
>Also, I have read a little on atomic data (breaking data down to it's
>simplest form) and don't understand how far to go. Example, a standard
>North American phone number is made up of NNN-NNN-NNNN. Should I create
>one field to hold the entire number or 3 separate fields to hold the 3
>parts? Why would I do one or the other?
>
>Thanks for any help with this,
>Terri

The first method is a solution with a so-called 'repeating group'. According to normalization theories in order to have the 1NF (First Normal Form) there should be no repeating groups in the data model. Your approach might be acceptable when the number of repeating groups is constant and doesn't increase over time. If that's not true your design is inflexible because every time a new lanaguage surfaces in your system you will have to add a new column. So for the sake of normalization, transparency (the end-user can add a new language without involving you) and flexibility the Method 2 is to be preferred.

Your second question depends on how meaningful the constituent parts are for you. If you constantly want to know the number of subscribers by area code, you should break the number up, because the area code is meaningful for you and breaking it up in all your queries will take more resources than having to concatenate it for the few instances you require it concatenated.

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Thu Feb 14 2002 - 11:51:38 CST