Well, my vacation-related hiatus morphed into a longer delay between posts than I had anticipated. Sorry about that...

I thought I would write up a quick post about my favorite ORM constraint. I like this one because it expresses non-trivial business rules, comes up frequently, and is mostly impossible in the (non-note) constraints supported by ER/UML.

Regular subset constraints compare the population of a role (or roles) in one fact to the role(s) of the corresponding object type(s) in another fact. They act as a conditional mandatory constraint (if Y is supplied, then X must be supplied) or, said the other way, a conditional applicability constraint (Y is only applicable to be supplied if X is supplied).

For example, consider the facts: "Person is employed at Company" and "Person has been employed since Date". If both facts are optional, we could add a subset constraint from the Person role of the latter fact to the Person role of the former fact thereby constraining the system such that the date of employment can only be supplied for a person if we know what company that person works for. The date of employment, in other words, is only applicable if the person is actually known to be employed at some company. This could also be phrased as: if we know the employment date, we must also know the company that is causing said employment (i.e. conditional mandatory)

Note that if the Person role in the "Person is employed at Company" fact is mandatory, then the subset constraint is implied because every person must have a company recorded for them regardless of whether or not an employment date is also supplied.

Also note that an equality constraint is merely two subset constraints (over the same roles) in opposite directions. This then constrains the system such that if either piece of information is supplied, the other must be supplied as well.

Subset constraints can involve combinations of roles. For example, consider the facts "Person is assigned to Project" and "Person manages Project". If we add a constraint on the Person-Project role combination of each fact we can say a Person can only manage a project to which they are assigned.

A join subset constraint works in a similar fashion, except that it involves 3 or more facts. Consider the facts: "Person works in Department", "Department sponsors Project", and "Person works on Project". Now, let’s say we have a business rule that states a Person can only work on a Project that is sponsored by the Department that they work in.

We have to add a join subset constraint from the Person-Project role combination of the last fact (Person works on Project) to the Person-Project role combination of the other two facts – via a join through the Department object type.

This is another situation where the sample data used by ORM in fact instance populations is immensely helpful. We can enter sample data for the first two facts such as in the tables below. We can then join the two data sets on department, and that will show us the allowable population of People and Projects for the Person works on Project fact.

Given:

These are the possible Person-Project assignments:

Assuming that Person, Project, and Department have other functional roles they play, they each would get generated as an entity/class when mapping this model to ER/UML. However, there is no way to implement this constraint using the stock ER constraints because subset constraints on relationships are typically not supported. UML does have a subset constraint on associations (although you can argue about its visual clarity – which role end is being constrained?). However, UML has no way of implementing these as a join subset constraint (i.e. across more than 2 associations).

Finally, although I have discussed join path subset constraints, you can do a similar constraint pattern with any of the set population constraints: subset, equality (discussed briefly above), and exclusion (if X is supplied Y cannot be supplied and visa versa). However, in my experience, the join-path subset constraint seems to be much more common than the other two.

Tool-Specific Note: Unfortunately, Visio for Enterprise Architects (VEA) currently doesn’t display join path constraints on the diagram (I had to manually add the constraint symbol in the above example) nor will it generate the code in the DDL to enforce this constraint. It will, however, report the constraint in the various reports and it does get exported by your favorite ORM XML export tool.