[EMAIL PROTECTED] wrote:
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):
Ok, I guess, but isn't tit true now that you can insert a new address
row which doesn't belong to any valid network??
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
--
Daryl
"We want great men who, when fortune frowns, will not be discouraged."
-- Colonel Henry Knox, 1776
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
