[Zope-DB] Modelling & presentation question

[Jon Erickson]

On Fri, 2002-12-06 at 10:32, Charlie Clark wrote:
> This seems more like it. But I see the following problem:
>=20
> In "job" I would have
> id: 1
> desc: Arzt (Doctor)
>=20
> and
> id: 2
> desc =C4rztin (Doctor)
>=20
> now these two jobs are equivalent, ie. a search for "=C4rzte" should find=
=20
> people who have either they need to have the same key in some table. This=
=20
> is where my head starts to hurt.

Sorry that I misunderstood your question ;-)  I have never had to deal
with such a problem (thank gawd).  I have another idea for you... take a
look at the following:

--------------------------------------------------------
job(id, occupation_id)
   PK id
   FK occupation_id -> general_job

occupations(id, desc)
   PK id

gender_occupations(occupation_id, gender_id, desc)
   PK id
   FK gender_id -> gender

gender(id, desc)
   PK id
--------------------------------------------------------

We have the table that maps jobs to their respective occupation.  The
occupation table holds the general description for the occupation (ex.
"doctor").  The gender_occupations table maps the general occupations to
genders, giving the proper occupation name.  The gender table provides
the description for the two genders.  Here's an example of it in action:

------------------------------------------------------------------------
job(id=3D1, occupation_id=3D2)

occupation(id=3D2, desc=3Ddoctor)

gender(id=3D1, desc=3Dmale)
gender(id=3D2, desc=3Dfemale)

gender_occupation(occupation_id=3D2, gender_id=3D 1, desc=3D"male doctor")
gender_occupation(occupation_id=3D2, gender_id=3D 2, desc=3D"female doctor"=
)
-------------------------------------------------------------------------

We could select all jobs for doctors by:

SELECT job.id, occupation.desc
FROM job
     JOIN occupation ON job.occupation_id =3D occupation.id
WHERE job.occupation_id =3D 2

We could select proper female occupation name for job id "1" by:

SELECT job.id, gender_occupation.desc
FROM job
     JOIN gender_occupations=20
          ON job.occupation_id =3D gender_occupations.occupation_id
WHERE job.id =3D 1 AND gender_occupation.gender_id =3D 2
------------------------------------------------------------------------

This db schema would add some complexity to the application logic, but
it may be what your looking for.  Also, the above example could be
extended to support other languages by adding a lang_id column to the
gender_occupations table, and create a table that maps lang_id to the
name of the language.  Sorry, once again,  for misunderstanding your
original post.

Jon Erickson