[Zope-DB] Modelling & presentation question

[Charlie Clark]

Dear Dieter,

thank you very much for posting. I found the posting interesting but it 
doesn't seem to be quite what I'm after. This is partly down do to my lack 
of experience in SQL and relational modelling.

On 2002-12-05 at 15:39:06 [+0100], Dieter Fischer wrote:
> 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)
> );
So the primary key is a compound? 
 
> All domains with value =3D 0 are descriptions of itself (you can also 
> create a master detail, one to describe the domain, another for the code)
I'm a bit stuck on "domain" and "code". How are they incremented?
 
> 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   =3D 11
> AND   language=3D <dtml-var "getLanguageCode()">
> AND   code   =3D NVL(<dtml-sqlvar code     type=3Dint optional>,code)

I was hoping to avoid additional calls in the SQL: I would like to see a 
purely relational model if possible. I'm using PostgreSQL but I would like 
to avoid DB specific stuff where possible and stored procedures.

I've worked out that I have to have a table matching job and gender and 
have come up with the following:

CREATE TABLE gender_job
(id int SERIAL,
job_desc
id_gender int
FOREIGN KEY id_gender REFERENCES gender.id)

This would allow me to create a list of descriptions:
SELECT job_desc FROM gender_job
WHERE id_gender =3D 1

But leaves me with the problem of a primary key for searching. An 
alternative seems to require a table of job descriptions and an 
intermediary table, thus:

CREATE TABLE job
(
id INT SERIAL,
desc VARCHAR
)

CREAT TABLE gender_job
(
id_job INT
job_desc VARCHAR
id_gender
FOREIGN KEY id_job REFERENCES job.id,
FOREIGN KEY id_gender REFERENCES gender.id
)

This gives me the mapping I want and seems similar to what you propose but 
is also possibly redundant in that the "desc" column in "job" essentially 
being the same as one of the values in "gender_job". Of course, if it 
becomes an entirely internal, generic value this would allow extenstion for=
 
things like other languages.

ie.
job.id : 1
desc : doctor

gender_job.id_job : 1
desc : Arzt
id_gender: 1

and

gender_job.id_job : 1
desc =C4rztin
id_gender: 2

Charlie