Re: Composite types for composite primary/foreign keys?

On Dec 20, 2007 4:40 AM, Wolfgang Keller <wolfgang(dot)keller(dot)privat(at)gmx(dot)de> wrote:
> I'm not sure whether I am violating some copyright, so I didn't want to
> post the SQL script here. But the script is publicly downloadable at
> www.mimosa.org, and I only need a part of it to explain the basic
> concept. So this is the "complex" schema.
>
> CREATE TABLE enterprise_type(
> ent_db_site cris_string16_type NOT NULL,
> ent_db_id cris_uint_type NOT NULL,
> ent_type_code cris_uint_type NOT NULL,
> name cris_string254_type NOT NULL,
> user_tag_ident cris_string254_type,
> gmt_last_updated cris_datetime_type,
> last_upd_db_site cris_string16_type,
> last_upd_db_id cris_uint_type,
> rstat_type_code cris_ushort_type,
> PRIMARY KEY (ent_db_site, ent_db_id, ent_type_code)
> )
[snip]

I have general suggestions here. First of all, I do not advise using
domains for every table type automatically. This is actually not
terrible, but domains have some downsides, for example they are not
usable directly in arrays...this can byte you down the line. The best
case for domains is when you have a constraint that needs to be
applied across many tables (like validating a well formed email
address)...basically a light weight trigger. Just be aware that
modifying domains in such a way that requires dropping them first can
be a nightmare, plan accordingly. Also, the domain names seem
unnecessarily verbose, and over specialized. 'cris_string254_type'
can probably be defined as 'text' with no ill effects.

Secondly, you did not provide foreign keys...this makes it hard to
figure out the relationships which ISTM is the heart of the question.
Some of the primary keys look suspicious, but it's hard to tell
without knowing more (I didn't follow the link).

I think designs using composite, natural keys are generally good and I
encourage you to go with it...just be aware this is probably the #1
most controversial topic in database design. Nevertheless, the main
advantage of natural key designs is it encourages good key selection.
Hard to say if you are leveraging that here....

merlin