Message-ID: <433950E8.2050805@brandywine.com>
Date: Tue, 27 Sep 2005 10:02:16 -0400
From: Daryl Richter <daryl@brandywine.com>
Reply-To: daryl@brandywine.com
Organization: Brandywine Asset Management
User-Agent: Mozilla Thunderbird 1.0.6 (Windows/20050716)
MIME-Version: 1.0
To: Michael Fuhr <mike@fuhr.org>
Cc: Axel Rau <Axel.Rau@Chaos1.DE>, pgsql-sql@postgresql.org
Subject: Re: Updating cidr column with network operator
References: <ffaabb4676ab075beb4b6d041ec280c4@Chaos1.DE>
	<4332C38E.5040807@brandywine.com>
	<72c14f47f1dca0ae99e559cef56416a3@Chaos1.DE>
	<43331359.1020703@brandywine.com>
	<bbbf1059080c058d91cb869bf4cf750a@Chaos1.DE>
	<20050923173204.GA80995@winnie.fuhr.org>
	<4a95b442a12d5b4f3cc508868a13aba5@Chaos1.DE>
	<20050926000541.GA11071@winnie.fuhr.org>
	<f10bbbc30411862e1691fc7a905a8e5e@Chaos1.DE>
	<20050926213309.GA37147@winnie.fuhr.org>
In-Reply-To: <20050926213309.GA37147@winnie.fuhr.org>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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:
 >>
 >>>On Fri, Sep 23, 2005 at 09:19:25PM +0200, Axel Rau wrote:
 >>>
 >>>>I'm sure this would be the cleanest solution but remember networks
 >>>>change.
 >>>
 >>>Yes, which is why it's a good idea to automatically propogate those
 >>>changes to tables that 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 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
);

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