relationship good practice

Posted by jmls on 19-Aug-2011 07:46

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.

All Replies

Posted by Peter Judge on 19-Aug-2011 08:00

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

Posted by jmls on 19-Aug-2011 08:25

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

Posted by jankeir on 19-Aug-2011 08:33

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.)

Posted by jmls on 19-Aug-2011 08:40

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

Posted by Thomas Mercer-Hursh on 19-Aug-2011 11:08

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.

This thread is closed