[Zope-DB] Modelling & presentation question

[Dieter Fischer]

Hello Charlie

Make your life simpler. Just have a table profession:

id int
description CHAR

Then write into description "Arzt/=C4rztin". This will be a list of value=
 in
the Form. So you can (I suppose you have somthing like a table open_jobs)
access directly via the id .

Regards

Dieter

>-----Original Message-----
>From: zope-db-admin@zope.org [mailto:zope-db-admin@zope.org]On Behalf Of
>Jon Erickson
>Sent: Friday, December 06, 2002 6:57 PM
>To: Charlie Clark
>Cc: zope-db@zope.org
>Subject: RE: [Zope-DB] Modelling & presentation question
>
>
>
>
>
>
>On Fri, 2002-12-06 at 10:32, Charlie Clark wrote:
>> 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 f=
ind
>> people who have either they need to have the same key in some
>table. This
>> 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 doct=
or")
>------------------------------------------------------------------------=
-
>
>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
>          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
>
>
>_______________________________________________
>Zope-DB mailing list
>Zope-DB@zope.org
>http://lists.zope.org/mailman/listinfo/zope-db
>