Relationships

Copyright 2008-2009 ManyDesigns srl. All rights reserved.

Contents

  1. 1 Purpose
  2. 2 One-to-many
    1. 2.1 Zero-or-one cardinality
  3. 3 One-to-one
  4. 4 Many-to-many
    1. 4.1 N-ary relationships
    2. 4.2 Attributes on the intersection class
  5. 5 Recursive relationships
  6. 6 Autoconnect to user

Purpose

An important part of data modeling is to identify the relationships that exist between the classes. Relationships express the natural associations between units of information (e.g., between an objects and its parts), are the basis for navigation between objects, and enable database normalization.

All relationships in ManyDesigns Portofino are implemented using relationship attributes, conceptually very similar to foreign keys. Like for foreign keys, different combinations and configurations of relationship attributes can produce different types of relationship: one-to-many, one-to-one, and many-to-many.

One-to-many

One-to-many relationships are the most straightforward application of a relationship attribute. Let's suppose for example that we have two classes (Factory and Product) and a one-to-many relationship between them ("factory makes products").

To create the relationship:
  • visit the class on the "many" side of the relationship, in this case Product;
  • add a relationship attribute to it;
  • choose a name for the attribute, e.g. "factory_product";
  • think how a product sees its factory: the factory is the "producer" of the product. Use that ("producer") as the pretty name;
  • choose the class on the "one" side of the relationship (Factory) as the opposite end class;
  • think how a factory sees its products: this can simply be "products"; use that as the opposite end name;
  • make sure you leave the field one to one unchecked;
  • fill in the rest of the fields according to your needs.
It is important that you familiarize with the way pretty name and opposite end name affect the user interface at the downstairs level. A common mistake is to invert them. With a little practice, the correct use of the relationship attribute becomes natural.

In Portofino there is no difference between one-to-many and many-to-one relationships from a functional and implementation point of view. What matters is what classes you choose for the "one" and "many" ends of the relationship.

Zero-or-one cardinality

Depending on the value you choose for the field required, you can have two situations:
  • required is checked: the relationship is one-to-many;
  • required is unchecked: the relationship is zero-or-one-to-many;
A zero-or-one-to-many means that the object on the "many" side of the relationship can exist even if not related to any object on the "one" side. In the factory/product example, this would mean that a product can exists even if no factory produces it.

One-to-one

A one-to-one is very similar to a one-to-many. In fact, the field one to one on the relationship attribute is what makes the difference between the two: if unchecked, you get a one-to-many; if checked, you get a one-to-one.

A frequent question is: since one-to-one's seem symmetrical, where should the relationship attribute be put?
Let's see an example. We have two classes (Partner and Partner_agreement), related through a one-to-one relationship. Think: in temporal terms, which comes first, the partner or the partner agreement? In our view, the partner comes first and is somehow a longer-lived object than the partner agreement. We try to move away from it any information that is secondary or transient. The conclusion is that the relationship attribute should belong to the Partner_agreement class.

Behind the scenes, Portofino enforces one-to-one relationships through a UNIQUE constraint in the database.

Many-to-many

If you are familiar with database design, you know that a many-to-many relationship is implemented, at a physical level, through an intersection table. This table's purpose is to hold the two foreign keys which reference the tables at the two ends of the relationship.

In Portofino things work similarly. Let's suppose that we have two classes (User and Server) and that we want a many-to-many relationship between them, representing the idea that a user can access many servers and a server has many authorized users.

The steps to create the relationship are:
  • set up the intersection class
  • set up the first end of the relationship
  • set up the second end of the relationship
These are discussed in greater detail here:

Setting up the intersection class
Create the intersection class just like any other class, but notice the following:
  • Choose a name that fits the purpose of the relationship: e.g., user_server in the example.
  • The pretty name and pretty plural are not particularly important as we'll make sure they are not used in the user interface. You can use the same value as for name.
  • Check the relationship flag. This is very important as it marks the class as an intersection class and affects the way Portofino presents it to users.
  • In almost all cases you will want to leave tab unchecked.
Setting up the first end
Add a relationship attribute on the intersection class:
  • Use the name of the class on the first end (User) as the name and pretty name of the attribute: e.g., user in the example.
  • Select the class on the first end (User) as the opposite end class.
  • Think about how a user sees the servers connected through the relationship, e.g.: "authorized servers". Use this as the opposite end name.
  • Check the immutable flag. This a recommendation, not a rule, but it generally works well with many-to-many relationships.

Setting up the second end
Follow the same instructions used for the first end but this time refer to the class on the second end (Server in the example).

Once you have set up the relationship you can go downstairs and test it. Visit a user's details page. In the relationships section, notice the link "connect to server" instead of "add user_server".

N-ary relationships

The intersection class can have as many relationship attributes as you want. This effectively implements an n-ary relationship (a many-to-many-to-many-... relationship).

Attributes on the intersection class

The intersection class, just like a regular class, can have as many attributes as you need, other than the two relationship attributes.

In the User/Server example, if you want to track the start and end dates when a user is authorized to access a server, you can add two date attributes (start date and end date) to the user_server class.

Recursive relationships

A one-to-many or a one-to-one relationship is "recursive" if it relates a class to itself. More formally, a relationship attribute is recursive if the class that owns the class is the same as the opposite end class or a superclass of it.

A recursive one-to-many relationship allows you to model "trees" of objects.
A recursive one-to-one relationship allows you to model "sequences" of objects.

When you create a recursive relationship attribute, Portofino also creates:
  • three columns with suffixes "_left", "_right" and "_depth";
  • two views with suffixes "_up" and "_down".
These extra columns and views allow you to query the tree/sequence of objects in a very efficient way using SQL, even on databases that do not support recursive queries. You may find this useful when you deal with reports.

For more information on this technique, see Joe Celko's excellent book "Trees and Hierarchies in SQL for Smarties" published by Morgan Kaufmann.

Autoconnect to user

If a relationship attribute has the class User as the opposite end class, you can set the autoconnect to user flag. In this way, the value of the attribute is automatically set to the id of the currently logged in user.

A pre-requisite to this function is that user management has been configured.


Previous: Attributes

Next: User management