At 11:04 AM 21/10/2003 +0200, you wrote:

> >
> >Can you explain the behaviour you want? writing your own referential
> >triggers is a piece of cake.
> >
> >Actually, for stable lookup tables, my argument is that, if you design them
> >properly, you will never delete or update a key and so there is no
> >referential integrity to protect, no need for any foreign keys at all for
> >lookups.
>
>OK - what I have done (to allow the user to update values in lookup tables)
>is to write the original value into the primary table when the value is
>inserted into the primary table.
>
>In other words, the primary table now has a "taxrate" field. This field
>holds the tax rate at the time that the record was inserted. In this case,
>it will allow the user to update the tax rate field without changing the
>value in records in the primary table.
>
>Is this the kind of thing you mean, Helen?

Um, no, about as far away as you could go, actually.

That kind of fiddle-de-dee is exactly the reason I recommend keyed lookup
tables, especially control tables that need to be static but are appended
to periodically - like tables for calculating various taxes and stuff. You
don't want users modifying them _or_ entering ad hoc data in many
cases: tax rate records should be read-only for everyone except the
accounts person who is responsible for tax.

Consider
create table taxrate (
taxid integer not null primary key,
tax_type char(3) not null default 'REG',
tax_year varchar(15),
tax_period_start date,
tax_period_end date,
threshold numeric(18,2),
tax_rate numeric(6,3));

Records in this table would *never* be updated. You would add a new set of
records for each tax year, not update the same records from year to year.

Let's say you had 4 thresholds in your tax system plus, say two or three
separate rates each year for calculating tax on depreciation (or whatever),
that's still only a handful of records each year.

But, even after, say 25 years, when you have a couple of hundred tax rates
there, you *still* wouldn't put the user tables under referencing
constraints because, though you have more choices *in the table*, the
distribution will still be a problem for indexes, because most taxing would
be the default type. And, of course, unless you have bent accountants, the
tax rate data remains stable. It doesn't need any referential checking
because it aint ever going to change.

heLen