Hi Kevin,
I have a User object and a Role object. I pull them from the Object
types view onto my page and pull a predicate from the ORM source
shapes. I connect them up, stating that a User can have 0+ Roles and
Roles can have 0+ Users. However, I don't understand how to describe
how that relationship has Active and Inactive Dates, so I go to my
logical model, build my project and see a table created that has the
two keys for those objects as a composite primary key. I change it so
that it has its own primary key and that those keys are required, and
add my active/inactive dates. Pushing it back to the ORM model, I see
a new object (UserRole), along with the new value objects.
Here's my problem:
1. It appears that it is saying that I should have thought of UserRole
as an object to begin with. However, my understanding of ORM is that
I should NOT think of Table=Entity and Field=Value. Where is my wrong
thinking?
You're not exactly wrong.... In my opinion, you should be thinking about
facts first, but that doesn't mean that facts can't themselves be objects.
This is known as nesting (or objectifying the fact).
Assuming I am understanding what you are trying to do:
The fact User plays Role has a spanning uniqueness constraint (that is, the
combination of User and Role are together unique). These types of facts can
be nested (drawn by encirling the predicate and naming the new object; in
VEA you would fill in the object name on the advanced tab of the fact
editor). Once you do that, you can start adding facts about that new object
such as UserRole starts on Date and UserRole ends on Date.
Another way to represent this is via co-referencing. I wrote a blog about
this very subject recently, so you may want to refer to it (URL below). It
sounds like this is what happened after you altered the model in ER and
syncronized it back to the ORM.
2. What I REALLY want is a restraint that says that I can have only
ONE combination of User and Role for any datepoint (respecting the
Active/Inactive dates). I understand that I cannot do that in ER and
have to rely on triggers on my DB. Is there any way to express that
in ORM?
Now things get a little trickier because you are adding temporal aspects to
the fact. Merely adding the begin and end Date facts as I did above will not
suffice because it doesn't allow the same user to play the same role over
two time periods. Given your constraint above, you really have a ternary
fact type, something like User plays Role beginning on Date. Again, the
uniqueness constraint spans all roles. You'll probably still want to nest
this fact (User Role History) because you still need an inactive date via
User Role History has end Date.
I've attached both of these schema fragments. (That is, I tried to attach it
but was refused. I'll attempt to e-mail it to you.)
A few things to keep in mind:
1) Watch out when you are adding surrogate keys to an ORM model. They are
perfectly ok, but you need to maintain the business keys. I also blogged
about this recently....
2) Assuming time periods are non-overlapping, making facts temporal
generally adds a date to the business key. The date you add can be either
the start or end date; this is an arbitrary choice, but I usually pick the
begin date. A commonly missed constraint here, by the way, is that the
combination of User, Role, and end Date is also unique (see the attached).
3) The end Date role is usually not known to the system for all instances
(in other words, the fact is currently valid and has not ended). You may be
tempted to implement this via having the end Date role be non-mandatory.
This can be a pain when implementing because you have to do special handling
of null values (i.e. using coalesce, isnull functions, adding checks for end
date being null, and the like). I prefer to make the end Date role mandatory
and stuff in a date signifying "the end of time". In SQL Server, for
example, the largest date is 9999-12-31, so I'll use that a lot. Using a
date in this manner makes it easy to do between queries (show me all users
and their roles as of 2003-10-10 is implemented via something like: select
User, Role from Table where '2003-10-10' is between StartDate and EndDate).
It's not conceptually pretty, but given the state of current DBMSes it has
to suffice.
4) You'll still have to add logic to make sure that the time periods are
non-overlapping and (if needed) abutting (that is: no gaps between
assignments for a given person and/or role). There is no way of doing this
sort of constraint in ORM (or most modeling languages, for that matter)
without resorting to annotating it on the model as a textual rule. The only
thing ORM can do is make sure the combination of User, Role and end/start
Date is unique; you'll have to look for the other overlaps in code.
5) Temporal databases are a (difficult) topic of much work recently. I hear
Chris Date has a new book out on the subject (I believe he refers to them as
intervals). I also really like Snodgrass' "Developing Time Oriented Database
Applications in SQL" for a treatment that works in current DBMSes. There are
many options when dealing with temporal databases. Sometimes knowing one
date suffices because you can assume the previous period stopped when the
new one started, sometimes you'll stuff historical data into a different
data store and keep only the current facts in the main store, etc.
Snodgrass' book has a good treatment of these options, including how to
ferret out those offending overlaps.
6) You didn't pick an easy schema for being a (self-described) novice. Don't
fret, however, just take your time and watch your keys. I wouldn't even
worry about surrogate keys yet. Those come in handy when those temporal keys
become foreign keys in child tables (if you don't use surrogates, you end up
with each parent level's start date in each child, which is messy and
confusing, to say the least, especially if the parent key makes up the child
key and that child has children of its own who has children of it's own and
so on). So, first make sure you have the business rules right for each fact
without worrying about the time periods. Then, alter the facts to add the
time periods (basically, adding a start date to the reference mode of most
facts, objectifying those facts, and adding end Dates to those objectified
facts). Finally, add the surrogate keys. If you add the surrogates last, you
are less likely to screw it all up.
7) A friend and colleague is still, I think, psychologically damaged from a
meta-data driven hierarchy tracking system we designed where every fact had
temporal version (past, present, and future). This isn't easy stuff but it
is a lot fun of you enjoy the challenge. I can't wait to implement my next
one....
Hope that helps (and good luck!),
Scot.
................................................
Scot Becker
Orthogonal Software
www.orthogonalsoftware.com
ORM Blog:
www.objectrolemodeling.com