Re: Looking for a way to implement SQL-92 Domain

Following up on Galen Boyer, 25 Mar 2003:

> I guess it depends on one's definition of clutter. :-)

Yup. Last count these guys had one "type" table for EVERY other table in the schema. Bloody military... :)

Now they have ONE type table with quite a few "sub-types"! Much easier. And a LOT less indexes...

> of tables gets really large with lots of lookups but the amount of
> trigger code is increased the same amount

Not really. Depends on how you want to maintain the things. Mine just get an API auto-generated out of Designer (no, not the one that comes with Designer!), with a constant on the discriminator column. As many API calls as there are subtypes. Zero triggers (I've got to maintain compatibility with DB2...). One view for each subtype/lookup.

>that maintaining an RI relationship in a datamodeling tool is much
>easier than maintaining its analogous trigger.

I have one relationship for every subordinate table, off the "domain" table. Still get the normal RI errors, no need for triggers. Errors are handled by the same routine in all APIs, which traps and reacts to the 2291/2292 etc and sends back which table/column caused the problem.

> out there with the lookup values. I don't show these tables in the
> pictures of the model, the developers just know they exist and can
> select from them if needed.

I have to show EVERY single entity in that darn schema...

>
> So then the "clutter" is minimized and I don't have to maintain
> triggers. The RI is maintained by the database.

Mine too. No triggers anywhere. I was suggesting triggers to this fellow because he might not have an API interface like mine. It's easy with one, particularly if auto-generated.

How do you find Erwin? We tried it, but eventually gave up. Much faster than Designer, but it wasn't anywhere as "tuned" to Oracle as Designer is. And it didn't have a SQL compatible meta-dictionary, which meant I'd have to go into its own API to get auto-gen code. With Designer it was easy: just straight forward SQL and PL/SQL.

-- 
Cheers
Nuno Souto
wizofoz2k_at_yahoo.com.au.nospam