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
