I have two tables (A and B) . Both of these tables are separate entitites in their own right (each can exist without the other)
However, there are some times where I need to link these tables on a 1:1 basis.
I also need to be able to find B of A, and A of B
I see two options for this design
1) Create a field called BGUID in A, and AGUID in B
2) Create a Link table with two fields AGUID and BGUID
1) smells because I would have to update 2 records in a single transaction if I were to link or unlink the records
2) smells because I have to create a separate table.
I like the smell of (2) better though.
Just wanting to know what other people thought.
1) smells because I would have to update 2 records in a single
transaction if I were to link or unlink the records
2) smells because I have to create a separate table.
From a db design perspective, why do either of those things matter?
I would ask "will there ever only be a 1-1 relationship between these tables". If not, then a link table allows you do so without any trouble.
-- peter
It just seems wrong to have to update two records to maintain the link.
Option (2) allows a single create or delete to maintain the link -
it's just having to have another table that I don't like.
But then, everyone knows how fussy and fickle I am
Why not put AGUID in table B and add a unique index to it. If it's unknown value it won't complain (the record is still considered unique by OpenEdge.)
Only problem I see with that is that it's probably not allowed to make the index unique in any other database than OE (as far as I know all other databases will allow only one NULL in a unique index.)
my immediate response was going to be a "yeah, but"
but
I can't think of the but
The way round the unique problem would be to create a dummy guid for
all empty fields ....
going to the shops and ponder whilst buying the beer for the bbq
My first reaction was to point out option 3 which is to have an optional identifier in both tables. When the identifier is present in both, those two are linked. I think of this mostly because it seems like such an identifier should exist, i.e., in the problem space, it seems like both should have some property which indicates that both exist.