Month Year Issue

December 1999 Issue: 11

Journal of Conceptual Modeling
www.inconcept.com/jcm

Detecting the Need for Paired Subsets
by Dick Barden and Pat Hallock

Paired subset constraints are not used as often as many other constraints in ORM. However, there are certain situations that reveal the necessity for paired subset constraints. Recognizing these situations can be more challenging if fragments of a large pattern are scattered across different pages. At times you may not detect the need for this constraint until you get to the logical model. For example if this fragment is on one page:

Figure 1

And the following fragment is on another page:

Figure 2

Mapping these two fragments yields the following tables:

Figure 3

Notice that in the table Subscription Value that Product ID appears twice. This is the correct mapping for the given object role model. In this UoD the business needs to prevent features offered by a product from being selected for a subscription that subscribes to an entirely different product. This obviously would cause problems. But also note in the logical model that 'Product ID' and 'Subscribes to Product ID' could have different values. In fact there really isn't anything in this model to enforce the requirement that the Product IDs be equal. In this model a textual rule would have to be put in place to fully enforce this business rule. With this pattern you have a table with Product ID in it twice and it should have the same value. So is there another way to deal with this pattern and achieve the desired constraint but without the extra column?

First let's get both patterns on the same page and take a look at the consolidated ORM fragment:

Figure 4

Review the facts:

Fact 1: Feature has Description
Fact 2: Interface is owned by Company Name
Fact 3: Product offers parameter driven Feature
Fact 4: Product has release Description
Fact 5: Product Feature was entered on DateTime
Fact 6: Product Feature used by Subscription specifies Value
Fact 7: Subscription subscribes to Product
Fact 8: Subscription enrolls Interface
Fact 9: Subscription starts on DateTime
Fact 10: Subscription Value was updated on DateTime

Note Fact 6: Product Feature used by Subscription specifies Value. The desire is to prevent features offered by a product from being selected for a subscription that subscribes to an entirely different product. The preceding ORM pattern gets to the point of putting the two Product IDs in the same table for comparison by a textual rule that can make that they equal each other but it doesn't quite finish the job. However, now that the pattern fragments are on the same page the flaw in the pattern is clearly exposed. There is a more succinct approach.

Now consider the following modified pattern:

Figure 5

Now review the facts:

Fact 1: Feature has Description
Fact 2: Interface is owned by Company Name
Fact 3: Product offers parameter driven Feature
Fact 4: Product has release Description
Fact 5: Product Feature was entered on DateTime
Fact 6: Feature used by Subscription specifies Value
Fact 7: Subscription subscribes to Product
Fact 8: Subscription enrolls Interface
Fact 9: Subscription starts on DateTime
Fact 10: Subscription Value was updated on DateTime

Note, once again, the modified Fact 6: Feature used by Subscription specifies Value, now this fact simply states that the subscription selects a feature and provides a value for the feature. When this maps out it creates the following tables:

Figure 6

Note that the Subscription Value table has only one Product ID in it now. In the modified ORM a paired subset constraint is used to prevent features offered by a product from being selected for a subscription that subscribes to an entirely different product. In a physical model the selection of a valid Feature Code referenced by the Subscription Value table is enforced with a foreign key and the rest of the business requirement is completed with the paired subset constraint which could be implemented using a database trigger. Using the Verbalizer in VisioModeler here are the three facts involved in the paired subset constraint and then the paired subset constraint itself.

Feature used by Subscription specifies Value
It is possible that more than one Feature used by more than one Subscription
specifies more than one Value.

Subscription subscribes to Product
Each Subscription subscribes to some Product.
Each Subscription subscribes to at most one Product.

Product offers parameter driven Feature
It is possible that some Product offers more than one parameter driven Feature and
that more than one Product offers some parameter driven Feature.

For each Feature f and Product p over their join paths, the following holds:
if Feature f used by some Subscription specifies some Value … some Subscription subscribes to Product p
then Product p offers parameter driven Feature f.

The constraint now enforces the business requirement that is needed to associate the correct feature with a subscription based on the mutual product they share and the pattern does so without the extra Product ID column that was produced with the first pattern.

When you are dealing with very large models that have hundreds of facts you may make refinements to the conceptual model after you have mapped the conceptual model to a logical model and examined the logical model. Sometimes patterns can reveal themselves more clearly by looking at them from a different perspective. Grouping facts by subject area is very useful but be careful as it can also hide certain relationships between facts. In order to preserve the usefulness of 'paging' for grouping facts by subject area and at the same time capture constraints that might span subject areas, it would be necessary to have some sort of mechanism to drop a 'copy' of a fact from one subject area page to another subject area page as you would an object. Consider the original patterns that had separate subject area groupings:

Figure 7

One possible scenario would be to use a 'Ù' placed in the fact as a way to signify that the fact was being used on more than one page. This way the crucial fact 'Subscription subscribes Product' could be placed in both subject area groupings. This would keep the two subject area groupings intact and provide a way to place the critical fact in another subject area grouping so that the paired subset constraint (or some other constraint) can be captured. This avoids the problem of having to re-organize the model so that all constraints can be captured diagrammatically. In fact it may happen that a fact might have to participate in several constraints each in different subject area groupings. Here is the second subject area grouping using the replicated fact from the first subject area grouping:

Figure 8

Finally, perhaps a warning to assist the modeler in checking for these kinds of problems could be provided. A check of a logical mapping for duplicate columns that appear to be redundant may be possible. Of course there any number of cases where multiple columns using the same object is perfectly acceptable, as with a date object. Where it may be possible to raise a warning is when the columns were part of nested and/or composite objects coming from different paths. These kinds of advanced problem detection assistants would be a great help in checking models, especially large models that have fragments spread out across subject area groupings.

Dick Barden is a Senior Partner and Principal Consultant for InConcept. He has over 15 years of ORM/NIAM experience and is a certified ORM consultant and trainer and a certified Visio trainer.

Contact Information:

Dick Barden
Vice President and Co-Founder
InConcept
8171 Hidden Bay Trail N
Lake Elmo, MN 55042
dickb@inconcept.com
(651) 777-8484
fax: (651) 777-9634
http://www.inconcept.com

Patrick Hallock is a Senior Partner and Principal Consultant for InConcept. He has over 20 years of ORM/NIAM experience and is a certified ORM consultant, trainer/train the trainer and a certified Visio trainer.

Contact Information:

Patrick Hallock
President and Co-Founder
InConcept
8171 Hidden Bay Trail N
Lake Elmo, MN 55042
path@inconcept.com
(651) 777-8484
fax: (651) 777-9634
http://www.inconcept.com