When does one put data into a separate table?

From: Paul <paul_at_see.my.sig.com>
Date: Sun, 24 Jul 2005 22:05:44 +0100
Message-ID: <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...

-- 

plinehan __at__ yahoo __dot__ __com__

XP Pro, SP 2, 

Oracle, 9.2.0.1.0 (Enterprise Ed.)
Interbase 6.0.1.0;

When asking database related questions, please give other posters 
some clues, like operating system, version of db being used and DDL.
The exact text and/or number of error messages is useful (!= "it didn't work!").
Thanks.
 
Furthermore, as a courtesy to those who spend 
time analysing and attempting to help, please 
do not top post.
Received on Sun Jul 24 2005 - 23:05:44 CEST

Original text of this message