Uniqueness constraints on a nested predicate

[Rickard Axne]

"warning C1015: '(null)' : Uniqueness constraints on a nested predicate
that do not span all roles are highly unusual."

I have several instances that causes visio to prompt the above. There
are no further explanations why it should be so highly unusual to
objectify roles that are unique on both ends? In the case that the role
isn't mandatory that's the only easy workaround I know -- otherwise
visio generates a uniqueness constraint on a nullable column!

Any thoughts?

Rickard

 

[Scot Becker]

Hi Rickard,

"warning C1015: '(null)' : Uniqueness constraints on a nested predicate
that do not span all roles are highly unusual."

I have several instances that causes visio to prompt the above. There are
no further explanations why it should be so highly unusual to objectify
roles that are unique on both ends? In the case that the role isn't
mandatory that's the only easy workaround I know -- otherwise visio
generates a uniqueness constraint on a nullable column!

You can only nest predicates that have a spanning relationship and one other
case: a binary predicate with a UC over each role (i.e. 1:1).

(Since you got the warning, and not an error, it sounds like you are doing
the latter?)

This makes sense because nesting is the objectification of the relationship.
If the relationship is functional (a UC over only one of the roles) you are
in effect objectifying an attribute and any roles that attribute plays in
turn become attributes of the same entity that contains the attribute you
objectified.

The only reason the 1:1 case is even allowed is to handle facts like "Person
is married to Person" where you want to record facts about the marriage
(e.g. the date it happened) and would otherwise have to make an arbitrary
choice as to which spousal role gets that attribute.

However, it sounds like you are doing this in order to work around a problem
with most SQL databases: they (incorrectly) treat nulls as concrete values
and thus that null value is used when evaluating uniqueness. ORM uniqueness
constraints do not evaluate null values because there are never nulls in any
predicate; an object plays the role or it doesn't, and uniqueness is only
evaluated for when the object(s) play the related role(s).

I have complained about this for years but no one listens so I gave up....

The only work around for this is to drop the constraint from the model and
ensure the real uniqueness constraint (i.e. if a value is present, it must
be unique) via some other means.

Hope that helps,
Scot.
................................................
Scot Becker

Orthogonal Software
www.orthogonalsoftware.com

ORM Blog: www.objectrolemodeling.com

To e-mail me, please use my first name at the domain listed above. All mail
sent to the reply-to address gets routed to a junk e-mail folder where it
will likely be deleted without being read.

 

[Rickard Axne]

The only work around for this is to drop the constraint from the model and
ensure the real uniqueness constraint (i.e. if a value is present, it must
be unique) via some other means.

Thanks Scot, excellent reply. I notice you didn't directly comment on
the approach of objecitying the fact to overcome the nullable unique
constraints, is there a problem doing that (in general) in your opinion?

Rickard

 

[Scot Becker]

Hi Rickard,

Thanks Scot, excellent reply. I notice you didn't directly comment on the
approach of objecitying the fact to overcome the nullable unique
constraints, is there a problem doing that (in general) in your opinion?

I'm not following how nesting is fixing this problem. Could you provide an
example (fact sentences or a model file).

Generally speaking, I don't like tweaking an ORM model to fit a physical
DBMS problem (such as this one) unless I have two different models: one that
represents the business domain and one that represents the solution domain.

Hope that helps,
Scot.
................................................
Scot Becker

Orthogonal Software
www.orthogonalsoftware.com

ORM Blog: www.objectrolemodeling.com

To e-mail me, please use my first name at the domain listed above. All mail
sent to the reply-to address gets routed to a junk e-mail folder where it
will likely be deleted without being read.

 

[Scot Becker]

Hi (again) Rickard,

Sorry, I was having trouble seeing it, but I played with a model and think I
know what you are doing now.

Given the fact A has B, both roles optional, and both roles (individually)
unique you have some options:

1) The mapping algorithm will arbitrarily make B an attribute of the A table
or visa versa.
2) If either role is mandatory, the mapping algorithm will map the
attributes such that the table with the mandatory role gets the attribute,
thus solving your uniqueness constraint problem.
3) You could nest this fact, declare it independent, and thus get an
intersection entity. It sounds like you did this, correct?

Now for the "just my opinion" part:

Option 3 is a good idea if the nested object plays roles itself in some
fact(s). Otherwise, it isn't the worst idea, especially if A and B end up
being distinct tables (i.e. they both play functional roles with other
objects and thus map as tables with some sort of relationship between them).

If B (or A) don't map as their own tables, the independent nested object in
effect creates a table who's sole purpose is to house an attribute such that
the uniqueness constraint can more easily be enforced via a SQL database.
I'm not a fan of this approach. I think simple trigger code enforcing
uniqueness over non-null values could do the same job if B were a nullable,
non unique index bound attribute of A.

I would probably lean towards that (trigger) approach even if A and B were
tables by themselves, but I would have to consider other factors such as:

1) How sure am I that neither role is mandatory?
2) How sure am I about the 1:1 relationship?
3) Are either of the above due to other design time decisions that could be
altered to meet their original requirements and not end up with these
awkward constraints? By this, I mean are A or B some artificial thing that
got added as a physical implementation detail or other non-business related
factor?
4) How stable are these rules (how likely are they to change in the next
year)?

If the root of your question is "am I doing anything that violates some ORM
rule?", then I'd have to say probably not. If it's "is this a best
practice?" then I'd have to say: it depends. ; )

Hope that helps,
Scot.
................................................
Scot Becker

Orthogonal Software
www.orthogonalsoftware.com

ORM Blog: www.objectrolemodeling.com

To e-mail me, please use my first name at the domain listed above. All mail
sent to the reply-to address gets routed to a junk e-mail folder where it
will likely be deleted without being read.