Re: Updating cidr column with network operator

Am 27.09.2005 um 16:02 schrieb Daryl Richter:

> > An attribute is redundant if it repeats a fact that can be learned
> > without it. If one table contains IP addresses and another contains
> > networks, then you can associate IP addresses and networks with a
> > join of the two tables; indeed, this is how the "fix the network
> > column" update works. Having a network column in the address table
> > simply repeats what could be learned through the join.
> >
> >
>
> I agree with Michael here. I think the fundamental problem with your
> schema is that it is possible to have contradictory data between the
> network and address table, always a bad situation.
>
> I would replace network.id with a serial type value and make the cidr
> a separate column, for example:
>
> CREATE TABLE network (
> id int not null PRIMARY KEY,
> address cidr not null,
> attr1 varchar(10) null
> );
>
> CREATE TABLE address (
> id inet PRIMARY KEY,
> network int NOT NULL
> REFERENCES network
> );

I agree with Michael too, but I understand him differently: What he
says is:
"Get rid of the redundancy",
which means to me:
"remove the fk from address to network completly".
The attribute "network" is not realy needed because we can always join
address.id << network.id
This reduces the necessary logic to keep things consistent. I still can
have
my cascaded delete in network, have to do it with a trigger.
I'm currently looking at performance issues.

Introducing a synthetic pk in network does not really make things
easier.
Instead I introduced an insert/update trigger which prevents from
overlaps
in network (which is not as a matter of course for cidr columns, I
have learnt):

CREATE OR REPLACE FUNCTION dd.ids_network_update_insert_check() RETURNS
TRIGGER AS $$
BEGIN -- check if new net overlapps with existing one
PERFORM N.id FROM network N WHERE NEW.id << N.id OR NEW.id >>
N.id;
IF FOUND THEN
RAISE EXCEPTION '?Attempt to insert overlapping network %',
NEW.id;
RETURN NULL;
END IF;
RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';

Axel

Axel Rau, Frankfurt, Germany +49-69-951418-0