Hi. This is just some thoughts about database design.
I often find my self having to do this
update table_XY set x=..., y=... where x=... AND y=....;
if not found then
insert into table_XY (x,y) values (...,...);
end if;
Is this normal or are there something else I could do so I don't have
to check if it exists?
Or is there some more general problem with the table design?
table_XY is in this case (and most cases) a table like this
create table table_XY (
x int references table_X,
y int references table_Y
);
I could of course add a constraint unique(x,y) to avoid duplicates,
but that would not change the the need to check if it exists before
inserting.
I could also do
delete from table_XY where x=... and y=...
insert into table_XY (x,y) values (...,...);
But that would seem to be very slow.
One idea is to put in dummy records for each x,y combination, as a
default value. But if table_XY has very few records, it seems like a
complete vaste of resources.
If I really wanted table_XY to contain a complete set of records of
all possible pairs of x,y values, how would I do that? The only way I
can think of is that when inserting into table_X, I'd do
insert into table_XY VALUES SELECT xvalue,table_Y.id FROM table_Y;
where table_Y contains a primary key called id.
How would you (you who knows this stuff far better than me) do this? :-)
For the developers: a combined insert/update command would be nice :-)
