Re: Designer 2000, use of foreign keys

Release 2.0.10.0.0 of Des/2K ERD help files contain the following process description:

/** ** **
For one-to-one relationships, the Database Design Wizard decides where the foreign key is to be implemented as follows:

1. If there is already a foreign key implementing the relationship, that key is used.
2. If one end of the relationship is optional, and the other is mandatory, the foreign key is created in the tables that implement that mandatory end.
3. If the Average Volume property of one of the entities is greater than that of the other, the foreign key is created in the tables that implement the entity with the greater average volume.
4. The foreign key is created in the tables that implement the entity (and relationship end) that precedes the other in alphabetical sequence. ** ** **/

So, Andy, it seems that you were right!!

A larger question remains for me, regarding the intent and use when modelling one-one relationships.

I have seen models that indicate one-one where there can logically exist a one-many, but a business rule (and this might be a loose use of that term) would like to keep the maximum cardinality at one instead of many (in an insurance company it was one vehicle per policy, which does not reflect the reality of the real world neccesarily).

Any ways, my inclination in modelling such entities is to (initially) ignore the (sometimes) arbitrary "one"ness of the one-one, and reflect the more realistic external nature of the relationship as a true one-many, with the understanding that some type of "processing constraint" will enforce the one-one reflected by the business rule. This way when the enterprise decides to better reflect reality, the data designs are ready for the task.

Following this approach lets the foreign key be placed in the appropriate table (no ambiguity or process to follow - JUST DO IT).

I would be interested in hearing other views on this, and about solid one-one designs (I have to admit I have only used one-one tables for physical implementations, not at the logical design level).

Thanks for the chance to share ideas.

• --- --- --- On 1997/10/07 Jeff Jacobs (jmjacobs_at_ix.netcom.com) wrote:

<snip>
For 1:1, the product has never been consistent. At one time, it created foreign
key columns for both tables!

Andy Hardy <Andy_Hardy_at_camk.demon.co.uk> wrote:

>In article <6130dk$suv_at_drn.zippo.com>, Dan Peacock <?@?.?> writes
>>In article <6WCUXDA30PN0MwmY_at_camk.demon.co.uk>, Andy says...
 [snip]
>>>I'm still having a little trouble with this - I don't know how to
>>>determine which of the entities will get the foreign key added to it
>>>during the conversion to the database schema. I *think* that the entity
>>>which appears first in the alphabet is given the extra columns during
>>>the conversion. Surely this can't be right?
>>>
>>Right. The way it works is the entity on the "from" end of the realation
 ship
>>gets the added column. For example:
>Hm...
>
>Doesn't work for me :(
>
>I'm just using a simple one-to-one relationship and no matter which
>entity I select as to or from, the entity whose name appears first
>alphabetically seems to get the extra columns.
>
>On 1-to-many, the extra column always seems to be added to the many side
>of the relationship?
>
>Andy
 

-- 
Bill Bunke
Data Administrator
Pleasant Co.
bill_bunke_at_pleasantco.com

The ideas expressed here represent my personal position only.