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
