The ORM Foundation

In many existing database tables (especially for entities) there is often the same bunch of fields to log insertion or update of information (CreationDate, LastUpdateDate, CreationUserID, LastUpdateUserID, CreationOrganizationID, LastUpdateOrganizationID).

Is there an easy an elegant way to model such information for many different entities? I thought of subtyping a kind of 'loggable' entity but I'm not sure if it is conceptually right. Any suggestion? Thanks

Giacomo

Quite often, this kind of information is specific to a given table, and not directly related to any Entity. Indirectly related, yes, since often an entity maps into a table. If it is the case that you'd want one of these logging records on any table, regardless of why the table is there, then it wouldn't make much sense to have it at a conceptual level.

 However, if your goal REALLY IS to represent this logging information at a conceptual level, and to do it uniformly in many places, here is a potential way to do it.

Introduce an entity for this logging information, and attach the elements you wish included, with a spanning uniqueness constraint across all of them as the primary identifier. You'll only need this shown once in the model. Then, wherever you come across an entity that you wish to have logging information, connect it to that logging information in the diagram.   When it is mapped to a relational view,all of the elements you've got the primary identifier spanning for the logging information will be included, without having to repeat them on the diagram each and every time.

This type of audit information is very difficult to conceptually model and also difficult to map to generated artifacts. Basically, you want a way to do this that has no side effects in the relational mapping, which is quite difficult to do.

If you're willing to write a small amount of XSLT on your own, you can add your own audit columns directly to the table when you generate the DDL. See http://www.ormfoundation.org/forums/p/549/1612.aspx#1612 for more information on this approach. You can also refer to the thread on using custom properties to generate schema names as discussed in http://www.ormfoundation.org/forums/p/573/1706.aspx#1706. This would let you use custom properties to provide further information to your audit column generator.

-Matt

Thanks, I agree that the real point is if it makes sense to represent this information at a conceptual level.

Anyway your solution is simple and effective, thanks.

Giacomo

Maybe this audit info should be simply ignored in a conceptual model and taken into account only during the mapping.

Anyway at the moment I'm using ORM only for documentation purposes but I'm glad to hear that the tool has such fine grained customization capabilities.

Thanks,

Giacomo

OrionB:

Introduce an entity for this logging information, and attach the elements you wish included, with a spanning uniqueness constraint across all of them as the primary identifier.

That's the way I would do it, and contrary to what Matt says, if you do it on objects that will be tables, it doesn't seem to affect the relational mapping. On the other hand, you are second-guessing the relational mapper.

It works as expected in CQL as well. I added this to the end of a Warehousing model of mine, that has entity types Warehouse, Party and Product, and the history fields got added to each of those tables, without creating a separate History table:

History is identified by creation-Date and update-Date and update-User where
    History has one creation-Date, History has one update-Date, History has one update-User;

Warehouse has one History;
Party has one History;
Product has one History;

Thanks for this solution. It's working for me so far.

This is not the only time I've wanted what in effect is a "design snippet" that I could add to my models and reuse, without introducing new tables. I hadn't thought of using a uniqueness constraint to solve this problem.