Other or not? (again)

From: Felix E. Klee <felix.klee_at_inka.de>
Date: Tue, 7 Sep 2004 18:44:52 +0200
Message-ID: <20040907184452.1fb81690.felix.klee@inka.de>

Hi,

first note that I already posted the following text on comp.databases but got no reply.

I wonder how to set up a table that stores certificates of persons.

PERSON_CERTIFICATES
person_certificate_id (PK)
person_id             (FK)
certificate_id        (FK)

PERSON_CERTIFICATE_TYPES
person_certificate_type_id (PK)
person_certificate_id      (FK)

certificate_type: A type of certificate that a person can possess.

    Examples: "MSc", "Driver's license", "Highschool diploma".

Now, imagine the problem that a person wants to use a certificate type that's not in PERSON_CERTIFICATE_TYPES. I envision two possible solutions.

1. PERSON_CERTIFICATES
   ...
   other: a freeform field to be used if certificate_id is 5 (the number is just an example).
2. Just let the person append the certificate to PERSON_CERTIFICATE_TYPES which is modified as follows.

   PERSON_CERTIFICATE_TYPES
...

   added_by_user: True, if the certificate type was added by an         
       ordinary user. In this case the certificate is not shown to 
       other users since it may contain junk.

I tend to prefer solution B. It allows the DBA to easily see which certificates are missing and clean up the list. I don't know, however, which solution is easier to implement (I most likely will be using PostgreSQL).

Opinions? Which solution do you prefer?

Felix Received on Tue Sep 07 2004 - 11:44:52 CDT