[Zope-DB] Modelling & presentation question
Fri, 06 Dec 2002 17:32:00 +0100
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=
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
desc: Arzt (Doctor)
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.