Re: doubt - Mailing list pgsql-general

Richard Huxton wrote:

> sathish wrote:
>
>> dear groups,
>>    i have a doubt in sql,
>>    i have a table staff_skills in which i have set a table level
>> constraint to create a primary key of two fields .
>>    so 'name' and 'skill' combinedly forms a primary key.
>>    now the issue is in another table i have only 'name' field and i
>> want to create a foreign key constraint which refers the staff_skills
>> table's 'name' field only.
>>   staff_skills (table) - name,skill,ability  (columns)   ---  primary
>> key(name,skill)
>>   staff_xxxx (table) - name,x,y,z            (columns)  ---   foreign
>> key(name) references staff_skills(name).
>>   how to do the above?
>
>
> You can't. If any occurrence of e.g. "John" in staff_skills refers to
> the same person then you should have a separate "person" table to hold
> their details.
>
> Your staff_xxx tables can then reference table "person".


Hi Sathish,

This is just a thought on style, but in an RDBMS, it's not really a good
idea to use data as a primary or foreign key.  In the example above, I'm
sure that there will not be only one "John" that goes in the database.
Even if the first last and middle names are used, there is still the
chance that it will have to be duplicated.

Indexing on data fields is good, but it's best to use a numeric field as
the primary key.  Then you can have as many John Joseph Smith's as you
want.  Then you can use data or SSN or something else to distinguish
them.  You also don't have to duplicate so much data across the
database. (e.g. if John has 10 skills, you put John in the database once
and the numeric PK might be 158, so you only use that number as the FK
in the skills table.)  Also, if John changes his name to James, you
change the data in one place and no further updates are necessary.
(yes, triggers can be used, but why worry about it if you don't have to?)

Lets say you have more than one John Smith.  Lets also say that 2 of
those John Smiths have the skill of DBA.  That would break the primary
key constraint of the staff_skills table since you cannot duplicate a
primary key.  You may see this as only a remote possibility, but with
Murphy's Law, it will probably happen sooner than you think.... and any
time is too soon for a database design to fail.

Despite the fact that PostGreSQL "can" use data as a primary key, for
smooth design it's best to avoid data as key fields.

Now, to implement the numeric solution could be something like this:
(These are just for examples, they are not SQL code)

table: people (id int not null, name varchar(64), x datatype Y, y
datatype X, primary key (id))
table: skill_codes (id int not null, skill varchar(10), description
varchar(256), primary key (id))
table: skill_log (people_id int not null, skill_id int not null) (set
people_id as FK to people(id) and skill_id as FK to skill_codes(id))

This way you have 2 reference tables, and your 3rd table that matches
them for each person's skills.  This way you can add skills to your
selection list (the code table) and have it ready even if nobody
currently has that skill.

--
Thanks,
Laura Vance
Systems Engineer
Winfree Academy Charter Schools, Data-Business Office
1711 W. Irving Blvd. Ste 310
Irving, Tx  75061
Web: www.winfreeacademy.com