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

Try doing the External Predicate Test. Try reading the column names as a sentence with only the word "and" between them, and that will tell you what your table really means. Then you can decide whether that sentence is really what you meant.

For example :

"There is a member of staff identified by Personnel_ID who has a First_Name and a Last_Name and who lives at address and who has landline number tel and has a mobile number and can speak Language1 and speak Language2 and speak Language3."

Is that what you meant ? Or,

"There is a member of staff identified by Personnel_ID who has a First_Name and a Last_Name and who lives at address and who has landline number tel and has a mobile number mobile. Additionally the member of staff can speak some number of Languages."

That "Additionally..." would go to another table.

The appearance of "or" as a conjunction between column names is a *very* bad sign - that means you really are talking about two different things.

Interestingly, although you're concerned about the number of Language items to have, you're not asking about your addresses ... ;)

• Tony

Paul wrote:
> 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 Mon Jul 25 2005 - 14:25:21 CEST