Search Postgresql Archives

Re: Composite types for composite primary/foreign keys?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Dec 20, 2007 4:40 AM, Wolfgang Keller <wolfgang.keller.privat@xxxxxx> 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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux