Subject Re: [IBO] Data Integrity
Author Helen Borrie
At 01:41 PM 26-08-02 +1000, you wrote:
>I have a small problem and am not sure if I should be making the change in
>the DB (an issue outside of this list) or use one of the TIB_Query events.

It's a DB issue...

>I have a table which has two particular fields
>I need to make sure that every record with the same IPADDRESS value has the
>same LOCAL value. What's the best way to ensure the value of LOCAL during
>data entry matches existing records with the same IPADDRESS.

put a unique index or key constraint over the two columns.

>If it is the
>first record with a unique IPADDRESS then LOCAL can be any value it likes.

Also, consider normalising these two columns out into another table which
your query objects will use as lookups, e.g.

create table iplookups (
iplookup_id integer not null primary key,
ipaddress char(15) not null,
local varchar(n) not null)

take the columns out of the main table and replace them with iplookup_id
and then you can make use of the lovely client/server tricks native IBO
performs (read up on the Keysource/Lookup relationship).

If the lookup table has very few rows compared to the main tables that use
it, then don't apply a formal FOREIGN KEY constraint to the main tables
referencing it unless you want really rotten performance. (This is known
as the Low Selectivity Problem. <g>)

If you leave these columns denormalised you leave your data exposed to
several kinds of user errors (from redundancy).

It's funny how you manage to sneak in an IBO "twist" to your off-topic
questions. :-)