Re: When does one put data into a separate table?

"Paul" <paul_at_see.my.sig.com> wrote in message news:h808e190r6cm2fbfkm0lgdpgn97jo0bk04_at_4ax.com...
>
>
> This is a sort of vague question.
>
>
> Let's say that you have a db with a "personnel" table.
>
> Personnel_ID INTEGER PRIMARY KEY,
> First_Name VARCHAR(30) NOT NULL,
> Last_Name VARCHAR(50) NOT NULL,
> add1..
> add2..
> add3...
> tel..
> mobile...
> Language1 VARCHAR(25) NOT NULL,
> Language2 VARCHAR(25),
> Language3 VARCHAR(25),
>
>
>
> Now, my question here is when does it become necessary to hive off
> another table
>
> Person_Language
> ID INTEGER NOT NULL
> Language VARCHAR(25) NOT NULL
>
>
> The vast *_vast_* majority of people will only speak one or two, a few
> 3, a couple 4 and very, very few will speak 5 or more. So, let's say
> we allow 5 fields in the Language part of the table, or even 6 or 7
> (just to cover the Einsteins).
>
> I'm wondering when people would both in theory and/or in practice
> start to move an attribute like this off into another table? Is there
> a "Golden Number" above which it automatically gets put into another
> table, or how do IT professionals decide on such things?
>
>
> Paul...
>
Let me start with Language2 and Language3.

What is the meaning of these two columns? In particular, do they mean the same thing? If one person has the code for "Spanish" in Language2, and the code for "French" in Language3, while another person has the same two codes, permuted, does that carry the same message?

If the answer is "yes" then it's already time to normalize the design, by combining those two columns into one column, and putting that new column in a separate table.

If the answer is "no" then I'd like to know what the difference in meaning of Language2 and Language3 is, given that they refer to the same master set.

More details later, if desired. Received on Wed Jul 27 2005 - 14:31:43 CEST