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