Transform a Pseudo Relation into an Exclusive Relation
Recently I had the need to use a pseudo relation as part of a path evaluation (i.e. as part of a business rule path). But, the path evaluator can’t do this, because it’s not a “real” relation in the Clarify/Dovetail schema.
Pseudo Relations
I covered pseudo relations in my recent Schema 201 webinar, but lets give a quick review.
A pseudo relation is a sort of schema hack that implements a relation using two fields. One field holds the type_id of the object that we want to relate to, and one field holds the objid of the object. These pseudo relations were a bit of a data modeling hack, before exclusive relations.
Pseudo relations are nice, because we don’t have to create a new physical column for a relation, and we can relate to any other object in the system.
The downsides are that they can’t be used in traversals, and we can’t use RelateRecords type methods (in ClearBasic, Dovetail SDK, CBOs) because they’re not real relations.
Lets look at an example.
The participant table has two fields: focus_lowid and focus_type.
The comment for focus_lowid is: Internal record number of the object participating the related event
The comment for focus_type is: Object type ID of the object participating in the event which is recorded in the related act_entry object; e.g., 173=bus_org, 154=email_log, 20=user
This is a pseudo-relation.
So if we wanted to add a participant for an activity, and the participant is a user, we would set the focus_type to be 20 (which is the type_id of the user table), and we would set the focus_lowid to be the objid of the specific user. We can also set the role_code to an appropriate value to indicate the user’s involvement in this activity (were they the owner, originator, were they affected by the activity, etc.).
Business Rule Path
The scenario I ran into this past week was that I wanted to traverse to this user using a business rule path.
I want to traverse from case –> act_entry –> participant –> user. So my path would start with focus_obj2act_entry:fact2participant, and then…uh…I’m stuck. I have the user objid in the focus_lowid field, but there’s no way to get to the user record via a path.
What about transforming that pseudo relation into an exclusive relation? Hmmm……
Exclusive Relation
I covered exclusive relations in my recent Schema 201 webinar, but lets give a quick review.
Exclusive relations are pseudo-relations improved. They still have 2 fields: one for the type_id of the related object, and one for the objidof the related row. Same as a pseudo relation.
But, because they’re a “real” relation (as far as the Clarify schema is concerned), they can be used in traversals, and can be used in RelateRecords type methods. An exclusive relation essentially means that only one out of a set of relations will be populated.
They’re used to reduce the number of physical columns that need to be created (when most of those columns would be sparsely populated).
Transform
So how do we turn a pseudo relation into an exclusive relation?
Pretty easy actually. We create an exclusive relation set, and then add one relation to that set.
Using Dovetail SchemaEditor
Using Dovetail SchemaEditor, a little schemascript does the trick:
<schemaScript xmlns=”http://www.dovetailsoftware.com/2006/10/SchemaScript.xsd”>
<addExclusiveRelationSet foreignKeyColumn=”focus_lowid” foreignTableIdColumn=”focus_type” name=”x_participant” table=”participant” />
<addRelation name=”user2participant” type=”OneToMany” table=”user” inverseRelationName=”participant2user” inverseTable=”participant”>
<exclusiveRelationSet>x_participant</exclusiveRelationSet>
</addRelation>
</schemaScript>
Using ddcomp
Using ddcomp (or DD Editor), we need to edit the schema file.
First, add a new relation to the end of the user table:
user2participant OTM participant
USER_DEFINED
INV_REL=participant2user COMMENT=””
Second, add a new exclusive relation set and exclusive relation to the end of the participant table:
EXCLUSIVE_SET x_participant
TYPE_FIELD = focus_type
OBJID_FIELD = focus_lowid
participant2user MTO user
USER_DEFINED
INV_REL=user2participant COMMENT=””
EXCLUSIVE_SET_END
Apply the changes using your schema editing tool of choice, and we now have an exclusive relation:
Coolio.
Business Rule Path, Take 2
Recall that I wanted to traverse to this user using a business rule path.
I want to traverse from case –> act_entry –> participant –> user. So now I can. My traversal path is: focus_obj2act_entry:fact2participant:participant2user:login_name.
And if I want to limit it to a user with a certain participant role_code: focus_obj2act_entry:fact2participant(role_code=1):participant2user:login_name
Perfect!
Just one exclusive relation?
Doesn’t an exclusive relation set normally have many exclusive relations? We’ve only added one relation here.
Normally, yes. But in my scenario, I only needed a path to get to the user table. So I only added one exclusive relation.
But I could also just as easy add more exclusive relations to this same set – for example, I could add a relation to bus_org, or to contact, or to email_log.
There’s no rule that says an exclusive relation set has to have more than one relation.
Still pseudo?
Can we still treat it like a pseudo relation?
Yes. Because we didn’t change any of the fields or indexes, it will still behave like a pseudo relation. Any existing code that uses this table and those columns will still function just fine.
Impact
What’s happening to the physical columns when we do this?
That’s part of the beauty of this. Nothing!
There are changes made to the ADP tables. Namely, two new relations are added (INSERT INTO adp_sch_rel_info), but that’s it. The participant and user tables aren’t touched.
Real-world usage?
How about an example of where we would use this?
Great question! That’ll be my next blog post. Stay tuned…
