[Zope-DB] Modelling & presentation question

[Charlie Clark]

On 2002-12-06 at 16:49:56 [+0100], Jon Erickson wrote:
> 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?  
You're right there but I couldn't think of the right way to do this.

> SELECT job_desc
> FROM gender_job 
>      JOIN job on gender_job.id_job =3D job.id
> WHERE gender_job.id_gender =3D <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 =3D gender_job.id_job
>      JOIN gender ON gender_job.id_gender =3D 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?

This seems more like it. But I see the following problem:

In "job" I would have
id: 1
desc: Arzt (Doctor)

and
id: 2
desc =C4rztin (Doctor)

now these two jobs are equivalent, ie. a search for "=C4rzte" should find 
people who have either they need to have the same key in some table. This 
is where my head starts to hurt.

Charlie