Mapping associations to tables, columns and relationships

Previous Next

Exactly how you map an association to USoft Tables, Columns and Relationships is a design choice that mainly depends on the association's cardinality. This help section starts with an overview of typical options, and then goes into each option in more detail.



collapse1:0 or 1:1 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 primary key column must have Mandatory = Yes if at all possible. There are very few good reasons for having non-Mandatory primary 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

Relationship attributes

The RELATE keyword