Ohio Information Technology

Ok, I have two things to talk about today, neither of which can be modeled directly in an E-R diagram or in UML.  As an application development team member, I tend to see patterns.  For example, a manager is a kind of employee that has subordinates, or vendors and customers are very similar, and have pretty much the same roles, except we buy things from vendors, and sell things to customers.  Wouldn't it be nice to say in your model that a manager is a type of employee, or that both vendors and customers are individuals in my address book.

In this example an Employee is identified by an id, and has a Name.  In addition, a Manager is a type of Employee that has a budget.  Manager is the subtype.

In addition to having a budget, another thing that differentiates a manager from other employees is that the manager has subordinates.  We can model that by saying that Manager supervises Employee. 

There is one last thing to do here.  We want to make sure that a Manager is not his own supervisor.  If we did not put any constraints in place, then any employee could supervise any other employee, including himself, or his supervisor.  To do this comprehensively we would want to make sure the structure did not eventually end up in a ring where A supervises B who supervises C who supervises A.  The constraints that restrict how these structures can be built are called ring constraints, and there are several types with very mathematical names.  Unfortunately each tool represents these ring constraints differently so you need to know the names.  In ORM there are six of them, and they have names that conjure up notions of higher mathematics: 

1. Irreflexive  - means that the two roles cannot be filled by the same instance of an object.  Our Manager supervises Employee fact type is irreflexive meaning a particular manager cannot be his own supervisor. 
2. Symmetric - means that if a relationship exists between two instances of an object, then the same relationship exists in the opposite direction.  If Ohio borders Indiana, then Indiana borders Ohio.  Our Manager supervises Employee fact type is not symmetric.
3. Asymmetric - is mostly the opposite of symmetric.  If a relationship between two instances of an object, then the same relationship does not exist in the opposite direction.  Our Manager supervises Employee fact type is asymmetric.  Note that asymmetry implies irreflexivity.
4. Antisymmetric - is kind of like symmetric with asymmetry in specific instances.  So in the antisymmetric relationship, if the same instance of an object is playing both roles of a fact, then it is symmetric, but if different instances of the object are playing the roles of a fact, then it is asymmetric.  Here is an example of an antisymmetric fact type: Number is greater than or equal to Number.  It is symmetric as long as the numbers are the same, otherwise it is asymmetric.
5. Intransitive - Intransitive has to do with chaining relationships together.  If Object A relates to object B, and Object B relates to Object C, then intransitivity say that Object A may not have that same relation with Object C.  Consider parents and children.  Joe is the father of Mark who is the father of Tom, then Joe can't be the father of Tom unless there are some illegal behavior going on.  That is intransitive.  In most organizations, the Manager -> Employee relationship is intransitive.  Note Intransitivity implies Irreflexivity.
6. Acyclic - Means that for any object, I can't follow the chain far enough to get back to the starting object.  A parent can not be it's own descendant.  This constraint is very expensive to impliment in terms of processing resources, so it is typically not specified.  Usually Acyclic relationships are specified as Asymmetric.  Note Acyclic implies Asymetric which implies Irreflexive.

If your brain now hurts as much as mine did when I was first trying to figgure out these ring constraints, you might find Verbalizing Business Rules: Part 12 by Terry Halpin useful.  Much of this post was inspired by this document.  However, be prepared to spend a lot of time in deep thought, and possibly additional research.  As I said, different tools depict ring constraints differently, so here is what the Manager supervises Employee looks like in Norma (a Visual Studio ORM plugin).

www.orm.net/

and a book by Terry Halpin and others:
Database Modeling with Microsoft Visio for Enterprise Architects