Re: Updating cidr column with network operator

Michael Fuhr wrote:> On Mon, Sep 26, 2005 at 12:34:59PM +0200, Axel Rau wrote:>>>Am 26.09.2005 um 02:05 schrieb Michael
Fuhr:>>>>>OnFri, Sep 23, 2005 at 09:19:25PM +0200, Axel Rau wrote:>>>>>>>I'm sure this would be the cleanest solution
butremember networks>>>>change.>>>>>>Yes, which is why it's a good idea to automatically propogate those>>>changes to
tablesthat maintain redundant data.>>>>I would not call it redundant but normalized, because network has
some>>attributes,common to all addresses in the net, 1st of all the netmask.>>> An attribute is redundant if it repeats
afact that can be learned> without it.  If one table contains IP addresses and another contains> networks, then you can
associateIP addresses and networks with a> join of the two tables; indeed, this is how the "fix the network> column"
updateworks.  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
);

insert into network( id, address, attr1 ) values( 1, '10.1', 'a' );
insert into network( id, address, attr1 ) values( 2, '10.2', 'b' );
go

insert into address( id, network ) values( '10.1.0.1', 1 );
insert into address( id, network ) values( '10.1.0.2', 1 );
insert into address( id, network ) values( '10.1.0.3', 1 );
-- OOPS!
insert into address( id, network ) values( '10.2.0.4', 1 );
go

-- This will "correct" the any addresses put in the wrong network
update address
set network = ( select id from network where address >> address.id )


[additional network stuff snipped]

-- 
Daryl

"We want great men who, when fortune frowns, will not be discouraged."    -- Colonel Henry Knox, 1776