Good Relationship Design

Each customer's family name must be registered. A customer has only one family name. In reality, a family name (especially names like "Smith" or "Patel") is used by multiple customers, but this is not interesting for the application. For the application, the relevant association between Customer and Family Name is therefore 1:1.

Implement this association not by a USoft relationship but by a FAMILY_NAME column in the CUSTOMER table. The CUSTOMER primary key holds an ID that identifies the customer uniquely.

A car rental company specialising in car rental for air travellers has branches at different airports. If it has multiple rental desks at the same airport, these desks are all considered to be part of the same airport branch. Exceptionally, the company has branches at non-airport locations. The relevant association between Branch and Airport is therefore 1:0.

Implement this association not by a USoft relationship, but by an AIRPORT column in the BRANCH table. Make this column non-Mandatory so that is it possible for a Branch to be located in another place than an airport.

A customer can make multiple orders. Each order is made by exactly 1 customer who is actually known to the application. The relevant association between Customer and Order is 1:n (one‑to‑many).

Implement this association as a USoft relationship with Parent Table = CUSTOMER and Child Table = ORDER. The Role of the relationship could be "MAKES". The Co-Role could be "is made by". The relationship has a Parent Column CUSTOMER corresponding to a Child Column MADE_BY.

The Child Column or set of Child Columns in a relationship is also referred to as the foreign key of the relationship. The MADE_BY foreign key is mandatory. It corresponds to the CUSTOMER primary key in the parent table. It is not part of the primary key of the child table.

There are also cases where the foreign key is part of the primary key of the child table.

An order, for example order 3449876, can have multiple numbered orderlines identified by sequence numbers: 1, 2, 3... The relevant association between Order and Orderline is 1:n (one‑to‑many).

Implement this association as a USoft relationship with Parent Table = ORDER and Child Table = ORDERLINE. The Role of the relationship could be "HAS". The Co-Role could be "of". The relationship has a Parent Column ORDER corresponding to a Child Column ORDER.

The ORDER foreign key in the child table is mandatory. It corresponds to the primary key in the parent table. It is also part of the primary key of the child table.

There are also cases where the key spans more than one column. A tour in the catalogue of a tour operator is identified by a combination of destination and tour type (the SUNTANNER tour to AUSTRALIA). A tour is regularly scheduled on the calendar. The relevant association between Tour and Scheduled Tour is 1:n (one‑to‑many).

Implement this association as a USoft relationship with Parent Table = TOUR and Child Table = SCHEDULED_TOUR. The Role of the relationship could be "SCHEDULED AS". The Co-Role could be "occurrrence of". The relationship has a Parent Column DESTINATION corresponding to a Child Column DESTINATION, and a Parent Column TOUR_TYPE corresponding to a Child Column TOUR_TYPE.

The two key columns are mandatory in both tables. Together they are the primary key of the parent table. They are also part of the primary key of the child table.

Stakeholders can be involved in contracts: they are signatories of contracts. A stakeholder can sign multiple contracts. A contract can be signed by multiple stakeholders. The relevant association between Stakeholder and Contract is m:n (many‑to‑many).

USoft Definer requires that your data model is in third normal form. Such a model contains 1:m relationships and 1:1 relationships between objects but not n:m relationships.

Implement this association by creating an extra table SIGNATORY that contains foreign keys to both Stakeholders and Contracts (an intersection table). This allows you to implement the assocation as 2 USoft relationships. One has Parent Table = STAKEHOLDER, Child Table = SIGNATORY, and Role = "SIGNS AS" (or: "IS"). The other has Parent Table = CONTRACT, Child Table = SIGNATORY, and Role = "SIGNED BY".

The primary key of the SIGNATORY child table spans the 2 foreign keys referring to the parent tables.

Parts of a machine are contained by other parts. A container part may contains multiple other parts. Each part is contained by exactly one other part, except for top-level containers. The relevant association is between Part and Part and it is 1:n (one‑to‑many). It is self-referential because the association is between a type (Part) and that same type.

Implement this association by creating a foreign key in the same table (PART) as the primary key that it refers to. Define a USoft relationship that has Parent Table = PART and Child Table = PART. The Role of the relationship could be "CONTAINS". The Co-Role could be "contained by". The relationship has a Child Column CONTAINED_BY corresponding to the primary key of the PART table.

Self-referential foreign keys allow you to store tree structures. To let end users visualise and control tree structures, you can create specialised tree view controls at GUI level (in Windows Designer or Web Designer) but not in USoft Definer. The relationship definition in USoft Definer only governs the storage of self-referential data.

To create a relationship where Parent Table and Child Table are the same, you must set the Alias Parent attribute to a value that describes the parent's role in the relationship. Here, you could set Alias Parent = CONTAINER. This allows USoft to generate aliases in SQL statements that are evocative to developers.