[Zope-DB] Modelling & presentation question

[Dieter Fischer]

Hello Charlie

I do the following (all in Oracle, perhaps you have to modify). All my codes
are also language dependend.

Having a code table:

CREATE TABLE CODE
( DOMAIN       NUMBER(15)                       NOT NULL,
  CODE         NUMBER(15)                       NOT NULL,
  LANGUAGE     NUMBER(15)                       NOT NULL,
  SHORT_DESC   VARCHAR2(25)                     NOT NULL,
  DESC         VARCHAR2(255)                    NOT NULL,
  CREDAT       DATE                             NOT NULL,
  MUTDAT       DATE,
  CONSTRAINT CODE_PK PRIMARY KEY (DOMAIN, CODE, LANGUAGE)
);

All domains with value = 0 are descriptions of itself (you can also create a
master detail, one to describe the domain, another for the code)

Means you have:

domain    : 0
code      : 1
language  : 1
short_desc: Lang
desc      : Language

An Entry for each language:

domain    : 1
code      : 1
language  : 1
short_desc: EN
desc      : englisch
...

Then the entry for the sex domain

domain    : 0
code      : 2
language  : 1
short_desc: sex
desc      : sex

Then the entries for the sex

domain    : 2
code      : 1
language  : 1
short_desc: m
desc      : male
...

Then I create a ZSQL method for the code:

<params>code</params>

SELECT code    lookup_code,
 short_desc    lookup_short_desc,
 desc          lookup_desc
FROM	code
WHERE	domain	= 11
AND	language= <dtml-var "getLanguageCode()">
AND	code	= NVL(<dtml-sqlvar code     type=int optional>,code)

This method does the following:
with the python script getLanguageCode() I get the language of the user,
which is set in index_html into a cookie. I call all variables look_up_x, so
I have no messup in the DTML or whatever method. The NVL() (NULL value) is
Oracle specific. It alows me, to use the code for a lookup, when I want to
see all the codes for a selection, but when I give a parameter, it gets only
the one I want.

Then in an DTML you have only to do that:

       <TD><SELECT name="sex">
           <dtml-in "sql.lookup_sex()">
              <OPTION VALUE="<dtml-var code>"><dtml-var desc></OPTION>
           </dtml-in>
           </SELECT>
       </TD>

Perhaps you will have an empty option, so the user has to choose. So be
aware of null values.

Also a entry

domain    : 2
code      : 3
language  : 1
short_desc: fm
desc      : male/female

will be helpfull, when the job description is for both genders.

Perhaps (when youre db is able to do that), a procedure will help you to
avoid the case (when you use language independency), that a language is
missing an you should have a fallback to a default language.

HTH

Dieter

>-----Original Message-----
>From: zope-db-admin@zope.org [mailto:zope-db-admin@zope.org]On Behalf Of
>Charlie Clark
>Sent: Thursday, December 05, 2002 2:45 PM
>To: zope-db@zope.org
>Subject: [Zope-DB] Modelling & presentation question
>
>
>Dear list,
>
>I'd suggestions as the best way to do the following:
>In German job descriptions are gender specific, in English you only have
>this with actor/actress. I'd like to be able to offer users a pull-down
>with the masculine description. What's the best way of doing this - I'm
>sure it's a common problem.
>
>I've thought about the following solution: an additional column for the
>"alternative" description and doing a gender check to select the
>value from
>the appropriate column. Somthing like
>
>pseudo code:
>if gender == m:
>  job = qJob().[0].value
>else:
>  job = qJob().[0].alternative
>
>I guess this is correct way to do this assuming this only presentation
>information. But seeing as I already have the information in the
>database I
>would like to be able to do this directly in a query.
>
>Ideas?
>
>Thanx
>
>Charlie
>
>_______________________________________________
>Zope-DB mailing list
>Zope-DB@zope.org
>http://lists.zope.org/mailman/listinfo/zope-db
>