[Zope-DB] Modelling & presentation question

[Jon Erickson]

On Fri, 2002-12-06 at 03:51, Charlie Clark wrote:

> CREATE TABLE job
> (
> id INT SERIAL,
> desc VARCHAR
> )
> 
> CREAT TABLE gender_job
> (
> id_job INT
> job_desc VARCHAR
> id_gender
> FOREIGN KEY id_job REFERENCES job.id,
> FOREIGN KEY id_gender REFERENCES gender.id
> )
> 
> This gives me the mapping I want and seems similar to what you propose but 
> is also possibly redundant in that the "desc" column in "job" essentially 
> being the same as one of the values in "gender_job". Of course, if it 
> becomes an entirely internal, generic value this would allow extenstion for 
> things like other languages.

Yes, this will create redundant data and you'll probably run into
problems keeping the job_desc fields synchronized.  Why not ommit
"job_desc" from the gender_job table and join the two when you need a
job description?  

SELECT job_desc
FROM gender_job 
     JOIN job on gender_job.id_job = job.id
WHERE gender_job.id_gender = <value>

Or, if you want to grab the gender description (male\female) directly
from the database you could join the job, gender_job, and gender tables.

Assuming that you have a table gender(id, desc):

SELECT job.id, job.desc, gender.desc
FROM job 
     JOIN gender_job ON job.id = gender_job.id_job
     JOIN gender ON gender_job.id_gender = gender.id
WHERE <other criteria, if necessary>

The above SQL will join the job table to the gender_job table, which
joins the gender table.  Any field from the three tables may be included
in the SELECT statement.  Is this what you're looking for?

Jon Erickson