Relationships

A relationship is the implementation of an association or connection between instances of 2 entities or, occasionally, between instances of the same entity.

A relationship implements an association. An association is the fact that instances of 2 entities (or types), or occasionally instances of the same entity, are connected in some way that is relevant to the business. An association is typically described in English by using a verb, such as places in an association between Customers and Orders:

MR_clip0046

Here, the association is a connection possibility between instances of the Customer entity (ie., individual persons) and instances of the Order entity (ie., individual documents or digital messages representing the fact that a customer places an order). Associations are sometimes referred to as fact types because, each time a customer places an order, this may be seen as a fact, and all the customer-places-order facts are of the same type, a "fact type", represented by the association and implemented by the relationship.

Because associations are typically described in English by verbs, or by a combinations of a verb and a preposition, relationships or associations are also referred to as verb concepts. The grammatical form of the verb is different when you read the association the other way: customers place orders, orders are placed by customers:

MR_clip0047

It is also possible for the 2 instances to be of the same entity (the same type), as in the association between a Team Member and another Team Member who is his or her personal coach:

MR_clip0048

The purpose of Relationships is to implement certain types of association in a USoft application. Exactly how you map an association to USoft Tables, Columns and Relationships is a design choice that mainly depends on the association's cardinality.

For help on mapping cardinality to USoft Tables, Columns and Relationships, click here.

Cardinality is the number of times that an instance is allowed to play a part (or role) in an association. Cardinality is a separate issue for each "side" of the association:

MR_clip0049

A car rental branch may be located at an airport or at some different location. If the company does business from an airport, it only has a single branch there.

If you do not keep any Airport information other than the name, then Airport is said to be a property of Branch, rather than an entity in its own right.

MR_clip0050

Each customer has a name that must be registered. Each time a name is registered, this applies to a single person. Two customers could both be called "Smith", but if one of those names came to be changed in the application (to correct an error, or when the person actually changes his or her name in reality), then the change would apply only to that particular customer.

If you do not keep any family name information other than a customer's name, which is likely, then Name is said to be a property of Customer, rather than an entity in its own right.

MR_clip0051

Each customer registered in the application has placed 0, 1 or multiple orders. Each given order has been placed by a specific customer.

MR_clip0052

Each order contains 1 or more orderlines. A given orderline is part of exactly 1 order.

MR_clip0053

Contracts must be signed by at least one stakeholder (as long as it has not been signed, the document may not be registered as a contract), but there could be multiple stakeholders. Each stakeholder could be involved in multiple contracts.

In USoft, a relationship between Customer and Order is implemented by linking up Customer data in a CUSTOMER table to Order data in an ORDER table:

MR_clip0054

In one of the tables, called the parent table, here the CUSTOMER table, a column value or a combination of column values named primary key, here the CUSTOMER_ID column, identifies an instance uniquely.

In the other table, called the child table, here the ORDER table, a column value or combination of column values named foreign key, here the PLACED_BY column, refers to the instance in the other table by using the same value as the primary key.

The Rules Engine automatically checks several aspects of the data integrity required to implement a relationship successfully:

•Primary key and foreign key data must have the same datatype.

•Primary keys must identify an instance in the parent table uniquely.

•Foreign keys in the child table must refer to primary key values that actually exist in the parent table.

These integrity checks are performed in the USoft layer, away from the RDBMS itself. The RDBMS knows only that tables have columns of specified datatypes.

If your application uses USoft-generated UIs, USoft Definer will automatically create useful navigation paths between connected tables (from a Customer to "related data" such as all the Orders made by that Customer; from a new Order to a "lookup" list of customers from which the ordering Customer may be selected). If you defined that the relationship is mandatory (a Customer must place at least one Order), the UI will automatically present child data in the same window or page as the parent data.

Good relationship design is crucial to the success of a USoft application. Relationships are not only the primary means of connecting data in different tables but they also represent conceptual links that shape your business model. It is only by creating well-chosen relationships that you will end up with tables, columns, logical views and constraints that optimally represent the business operations supported by your application, and that make large applications understandable and maintainable in the long run.