Good Relationship Design

Previous Next

USoft relationships provide a way to organise conceptual connections or associations between business objects, but not every association maps straightforwardly to a USoft relationship. Rather, when working with USoft Definer, you need to design the implementation of each association separately, choosing an optimal structure of Tables, Columns and Relationships for each case. Here are some typical examples.


collapse1:1 or 1:0 association
collapse1:n (one-to-many) association
collapsem:n (many-to-many) associations
collapseSelf-referential 1:n associations
expandBest practice in relationship design

Here are some principles of good relationship design.

Principles of key structure:

If a relationship has a single Parent Column, this column must coincide with a key (Key = 1, 2...) in the parent table. That is, it must have Key = 1, 2... (ie., a value other than 'No') and there must be no other columns in the table with the same Key value. Preferably, the Parent Column must be the primary key (Key = 1) rather than a secondary key.

Likewise, if a relationship has multiple Parent Columns, this set of columns must together form a key in the parent table. Again, preferably, they must form the primary key (Key = 1) rather than a secondary key if this is at all possible.

A key column, especially a primary key column, must have Mandatory = Yes if at all possible. There are very few good reasons for having non-Mandatory key columns.

A foreign key column (a Child Column of a relationship) should have Mandatory = Yes if there are no functional reasons for it to have Mandatory = No.

Principles for domains:

Child Columns in a relationship must be based on the same domain as Parent Columns. This rule is enforced by USoft Definer.

For primary key columns in a child table that are not Child Column in the relationship, ie., do not correspond to a Parent Column, it is preferable to select an underlying domain that has Generate Unique Values = No if this is functionally acceptable. For example, if acceptable, it is better to let users create Orderline numbers rather than to let USoft generate these numbers.



See Also


How to Define a Relationship

Setting Relationship Attributes

The RELATE keyword