The ORM Foundation

Hi. I'm just learning ORM and have gone through the NORMA tutorials (they were really great, BTW).

So now I'm experimenting with my first model and am hitting some snags. Here's what I want to express: a User must have one or more unique Email addresses, one of which must be flagged the the "primary address". Each Email can only be associated with one User.

NORMA model is here: http://minus.com/lbdWXnKzTP155f

The verbalization currently reads:

User has Email.
  Each User has some Email.
  For each Email, at most one User has that Email.
  This association with Email provides the preferred identification scheme for UserHasEmail.
  It is possible that some User has more than one Email.

I'm not clear on how to handle the "primary address" constraint. I objectified the UserEmail role thinking I need to attach the primary flag to that role. Since the Email is unique to each User I guess Email could also be converted to an Object with a AddressText() value and a unary isPrimary role?

But either way, I'm not sure how to impose the constraint that each User must have one and only one Email flagged as the primary address.

Any pointers?

Thank you.

Maurice

Try #2: is this closer? http://minus.com/lbvcm4zsLC8imX

Hmmm. I think a problem there is the primaryEmail could be connected to a UserEmail that's associated with a different user, right?

Hi,

Yes, that's right. But this second approach can work by re-directing the right-hand role in the 1:1, to be played by Email (change predicate text to "User has primary- Email"), and then putting an exclusion constraint between the two roles played by Email. You might also change the predicate reading in the upper fact type, to "User has other- Email".

To make the earlier approach work, all you need is to add an external uniqueness constraint, spanning two roles: the unary-fact-type role, and the "user" role in the nested fact type. (An external uniqueness constraint spanning a unary is called a "unique-where-true" constraint.) The verbalization of that U.Constraint in my NORMA installation is:

For each User, at most one UserHasEMail involves that User and is primary.

Cheers,

Andy

Thank you, Andy.

Here's the updated second model: http://minus.com/lbkpM9VidLBj5r
I removed the objectification of the role, since it didn't seem needed. I need to work on my naming to improve the verbalizations ;/

And here's the verbalization for that:

Fact Types:
User has other Email.
User has primary Email.
User has other Email.
For each Email, at most one User has other that Email.
It is possible that some User has other more than one Email.
User has primary Email.
Each User has primary exactly one Email.
For each Email, at most one User has primary that Email.
For each Email, at most one of the following holds: 
some User has other that Email; 
some User has primary that Email.

I had a problem fixing the first model though: http://minus.com/lxsmzXKP5mQHH

That's giving me an error of "Constraint 'ExclusionConstraint1' in model 'ORMModel4' has role players with incompatible types."
Is that what you meant for the exclusion constraint or am I doing something wrong there?

Thanks again,

Maurice

Hi,

For your second model, what the verbalizations need is for you to "bind" the predicates' adjectives ("primary" and "other") to the noun they qualify ("EMail") by placing hyphens immediately after them (but leave a space after each hyphen):

"User(.id) has primary- Email()"
"User(.id) has other- Email()"

You can add these hyphens in the fact-editor (select each fact type in turn). That will fix the constraint verbalizations.

For your first model, it's not an exclusion constraint you want, it's an external Uniqueness constraint. Like the exclusion, this is available from the Toolbox.

Cheers,
Andy

That's got it. Very helpful, thank you Andy.

I've now changed the rule a bit: I'm now allowing any e-mail to be used as an alternative email (even if used by other user(s) as well), but the primary email must be unique across all users.

Here's my model: http://minus.com/lArKkM5na0yJa

The verbalization for the exclusion constraint isn't what I want though:

For each Email, at most one of the following holds: 
some User has that alternative Email; 
some User has that primary Email.

I don't actually care if a given Email plays both roles between different Users. What I want to prevent is one Email being both a primary and the alternative for the same User; it should be one or the other.

It seems like what I need there is an external uniqueness constraint that spans both the User and Email of both roles. Is that right? I haven't figured out how to do that.

Also, I experimented with generating DDL for PostgreSQL and I'm not seeing some of the external constraints being enforced. Is that expected currently?

I managed to get an external exclusion constraint across both roles of each predicate: http://minus.com/l4R7zsPu8j3uH

The verbalization looks right:

For each User and Email, at most one of the following holds: 
that User has that alternative Email; 
that User has that primary Email.

But adding sample populations, it's allowing me to have the same email as both the primary and the alternative for the same user.

maubrey:

I managed to get an external exclusion constraint across both roles of each predicate:

That is incorrect, because it allows an email to be alternative for one user, but primary for another. It also allows the same email to be alternative for any number of users. You need the exclusion constraint to cover the email roles only, not the user as well, and the alternative-email UC should span just the email role. The constraint spanning the role pairs only says that the (user, email) combinations should be exclusive.

maubrey:

But adding sample populations, it's allowing me to have the same email as both the primary and the alternative for the same user.

I think that is a bug in the enforcement of the constraint across the sample population. Matt can enlarge, but not all constraints are correctly enforced over the sample population.

I tend to model this as "User uses Email for Purpose" and provide a list of purposes (primary, work, home, alternative, etc). At most one email per user per purpose.

 I agree with Clifford's comments.

Two other points to consider: The term "User"  and the reference mode ".id".

User
If by "User" you mean "Person", then you might consider using Person instead of "User".
If you are going to have "Person" somewhere in your model then you might as well not use "User" at all.

.id
The .id reference mode will generate an autonumber in the database. (At least it does for SQL Server and will probably do the same for other RDBMS's)
If you are sure that this is what you want then that's fine.

However, if you use .id a lot then there is no code in NORMA that prevents it from generating two ".id" columns in one table.
This causes errors in SQL Server and I suspect that it will do so for other RDBMS's as well.
To work around this potential problem, I always use ".nr" as a reference mode insetad of ".id" 

Clifford Heath:

That is incorrect, because it allows an email to be alternative for one user, but primary for another. It also allows the same email to be alternative for any number of users.

That's actually what I was going for, I think. In the previous mail I mentioned "I've now changed the rule a bit: I'm now allowing any e-mail to be used as an alternative email (even if used by other user(s) as well), but the primary email must be unique across all users."

Clifford Heath:

I tend to model this as "User uses Email for Purpose" and provide a list of purposes (primary, work, home, alternative, etc). At most one email per user per purpose.

All three of User, Email, and Purpose are entities then, with two binary roles, and an external uniqueness constraint across user/email/purpose? I'll experiment with modeling that approach.

Your work on CQL looks pretty cool, BTW. I started exploring that last night.

Thanks for your thoughts, Ken.

Ken Evans:

User
If by "User" you mean "Person", then you might consider using Person instead of "User".
If you are going to have "Person" somewhere in your model then you might as well not use "User" at all.

I did get bitten by using "User," which I guess is a reserved word in SQL so I was having to quote it once I generated the DDL.

Does anyone use plural names in the DDL and singular in the conceptual model? There are some environments where the convention is to use plural table names and singular columns in the relational view.

Ken Evans:

.id
The .id reference mode will generate an autonumber in the database. (At least it does for SQL Server and will probably do the same for other RDBMS's)
If you are sure that this is what you want then that's fine.

However, if you use .id a lot then there is no code in NORMA that prevents it from generating two ".id" columns in one table.
This causes errors in SQL Server and I suspect that it will do so for other RDBMS's as well.
To work around this potential problem, I always use ".nr" as a reference mode insetad of ".id" 

Ah, yes, I did notice that it mapped to a "serial" (autonumber) column in PostgreSQL.

In some environments almost all tables are given an artificial key (like ruby on rails) which is what I was going for there. But maybe .nr is better. I'll have to play around with that

Also, do you guys tend to include "auditing" columns in your conceptual models? e.g., again, referring to ruby-on-rails, it's common to have a "created_at" column on every table. Is it a good practice to exclude such facts from the conceptual model unless the model itself uses the creation information within a role?

maubrey:

Does anyone use plural names in the DDL and singular in the conceptual model? There are some environments where the convention is to use plural table names and singular columns in the relational view.

 

I don't know about "anyone else". You can edit the table & column names in relational view.
Just click on a table and press F4. This also works for column names. There is also a "reorder column" feature.

maubrey:

In some environments almost all tables are given an artificial key

I follow relational principles and only use surrogate keys when the natural keys are compound and large.
For example, many organisations assign a "personnel number." to each employee, so using "Person(.nr) fits this paradigm. (i.e. it is a natural key whereas the autonumber ".id" isn't)

I suspect that some people (and some frameworks) use artificial keys so that they can use "object id's " in the database and so can then use cursors to manipulate the data. Personally, I stick as closely as I can to relational principles. Amongst other things, this helps the RDBMS query optimisers to do a good job. The OO paradigm is fine for user interface code but is not very good for databases. 

maubrey:

do you guys tend to include "auditing" columns in your conceptual models?

Well, that's up to you as the database designer.
This topic has been discussed before, for example in this thread:  https://www.ormfoundation.org/forums/p/597/1798.aspx#1798

You can find out quite a lot by using the search feature at the top right of your screen. 

 Ken

maubrey:

Does anyone use plural names in the DDL and singular in the conceptual model? There are some environments where the convention is to use plural table names and singular columns in the relational view.

You want to look closer at Ruby's ActiveFacts generators for CQL. I have Ruby on Rails surrogate-key transform (adds an ID where needed), pluralisation, generation of a schema.rb, and also generation of ActiveRelation modules for inclusion into each table's model. This work was done for a large Rails app, which grew to 120 tables. There are 50 CQL modules that build it.

maubrey:

Also, do you guys tend to include "auditing" columns in your conceptual models? e.g., again, referring to ruby-on-rails, it's common to have a "created_at" column on every table. Is it a good practice to exclude such facts from the conceptual model unless the model itself uses the creation information within a role?

I have modelled them, but it would also be possible to write an auditing_column transform like the surrogate key transform. Both these transforms apply after the relational structure has been generated.

Ken Evans:

maubrey:

In some environments almost all tables are given an artificial key

I follow relational principles and only use surrogate keys when the natural keys are compound and large.

When I have a choice, I include the natural key as it is, but almost always use an identity column as merely unique, and enforce foreign keys against that. The choice of clustering in MSSQL is also important, because it affects not just the ordering of row storage, but also the de-allocation behaviour. It's essential to use clustering, and though it's preferable to cluster by the primary key so partial scans cover related key values, that adds a significant performance hit when enforcing foreign keys.

Ken Evans:

I suspect that some people (and some frameworks) use artificial keys so that they can use "object id's " in the database and so can then use cursors to manipulate the data. Personally, I stick as closely as I can to relational principles. Amongst other things, this helps the RDBMS query optimisers to do a good job. The OO paradigm is fine for user interface code but is not very good for databases. 

Although some do it for that reason, there are other good reasons for using surrogate keys than the object orientation rationale. You need to know the details of how your DBMS engine works to understand the performance and space implications. Bashing a framework or avoiding it because you disagree with its designer's opinions is just a bit childish, and is not a scientific way to get good results.

Ken Evans:

I follow relational principles and only use surrogate keys when the natural keys are compound and large.
For example, many organisations assign a "personnel number." to each employee, so using "Person(.nr) fits this paradigm. (i.e. it is a natural key whereas the autonumber ".id" isn't)

Although, philosophically, you could argue that the "personnel number" is a surrogate key -- it's just an externally allocated surrogate key. But it serves the same purpose and was probably introduced for the same reason by the "original" modelers. ;)

I think you're right though that a lot of OO approaches use surrogate keys as a simplifying assumption and maybe aren't as rigorous in their modeling. For this case, however, with a table like User/Person, I think the main reason is the stability of the key. In a web service environment, for example, what would be a good natural key? It's common to require unique e-mail addresses, but those change fairly often. It's common to have a unique username or screen name, but those need to be changed occasionally too. I realize with cascading FKs that's simple to enforce, and maybe I'm too embroiled in implementation details, but in my experience, the User table is by far the most commonly referenced entity. So it seems all-around simpler to have an artificial key for that purpose. Not that that should eliminate the natural constraints, of course.

As an aside, and I know I'm new here and probably naive, but I've been reading the forums and had a few reactions:

There are discussions about how to raise awareness of ORM and to get more widespread adoption. And certainly I think stuff like NORMA and CQL are key to that (I'm kind of blown away by how amazing they are, actually).

But also, I see the "Contributed Models" forum and there's almost nothing there. I know there's the Talent Agency, IT management, Bird classification models, etc. in the library, but those seem largely geared toward pedagogical use.

What I would like to see is smaller sub-schemas that can be reused in projects (and I know from reading some of Clifford's stuff that his work on CQL attacks that problem to some degree).

I'm just saying, look at it from my newbie perspective: I certainly want to become a better modeler. But I also have a job to do. In the same way we use design patterns in software development, it seems to make sense to use information patterns for modeling. If I could pick up an ORM model -- with the imprimatur of world-class analysts -- and adopt it into my own project, why wouldn't I? Maybe more importantly, why wouldn't my boss require me to?

In the ruby-on-rails world, for example, there's a bunch of plugins for common use-cases. A plugin for users, roles, attachments, blogs, etc. Each framework builds on the other, reuses certain design ideas, etc. Wouldn't it be cool to have them all using the same fundamental information model regardless of framework or database technology?

It just strikes me that perhaps focusing on ground-up influence of ORM may be as- or more effective than top-down approaches -- similar to how the strength of ORM lies with starting from the facts. Clearly there's enough commonality of use-cases, at least for web services, that the ruby-on-rails community rallies around certain plugins. It doesn't seem any more far-fetched to me that everyone can rally around a given sub-schema model, which is then implemented in a dozen different frameworks, all referencing the ORM model as proof that it was done right (tm).