The ORM Foundation

I have four ValueTypes that often get added to my Entity Types. These are: CreateDate, CreatedBy, LastUpdateDate, and LastUpdateBy. Can a group of these value objects perhaps called "ObjectTimeStamp" be created an then be applied to an EntityType? I would like to then generate SQL DDL that has those four ValueTypes as fields in my database. Thank you.

ORM deals in Facts and Fact Types.
I have interpreted your post to mean that you want to record information about two Fact Types:

* Booking is created by Person on Date
* Booking is updated by Person on Date

These are two separate fact types.
Each fact type requires its own table as the following example shows.
The diagrams show:

1: The ORM2 model
2: The verbalization sof the fact types
3: The two generated tables

In the second post I have added the SQL that was generated from the model.

Hope this helps
Ken

PS: if you click on the diagram you should see a clearer picture.

And here is the SQL for the model in my previous post.
Ken

 

In looking at my earlier replies, I felt that some people might be saying to themselves "Yes - but there is only one booking. How do I link the Booking ID in the two tables?"

Well this is indeed an important question and the answer is to add the implied extra fact that the "booking" is actually for something.
So here is another example where I have added the fact "Booking is for Seat".
You can see that this has now created a unique table for "the booking" with related tables that record the create and update events.

The procedure for doing this and for creating the SQL DDL is described in the NORMA Tutorial 1 file in Library>Tools:NORMA Tutorials>

Ken

Hi Ken The ultimate table design I was looking for was that the create date and create by fields would be included on the Booking entity as fields in the table. I am trying to avoid a separate table.

Well.er..yes - I did understand that point from your original message.
However, the fundamental point I was trying to make is that an ER diagram is a representation of some "facts"

It is not for nothing that the "ORM based design sequence" first asks "What are the facts?" and then "How are the facts to be represented in an ER diagram?"

ORM tools automatically generate table structures that are in 5NF - which means (inter alia) - no duplicates and no nulls.

If you try put both the "create fact" and the "update" fact in the same table, then you will end up with a table with nulls.
If you have nulls in a table then you have to write extra code to handle the nulls.
This approach wastes time and causes various data anomalies that destroy the integrity of your data.

However, it would also be helpful for me to know why you want to "avoid a separate table"?
What is the benefit that you seek?

Ken

Thanks again Ken, I also understand your fundamental point and do understand how facts are translated to tables using the ORM tool. However, I was wondering if there is some ORM modeling technique I am not aware of which would result in those fields not ending up in their own table. The reasoneI desire that is because those types of fields are so common and needed for a great many db tables I have a hard time justifying creating the overhead of making that join over and over again. I know it's not normalized but in some cases (such as this) I feel it best to keep that data non-normalized. Thanks again for your input!

 

 Hello Chiliyago,

 

This looks like a great case to do some model optimization on. In other words, to represent the same bits of information in another way in order to create something more beneficial when mapped down. (Optimization is in one of the later chapters of Halpin's book)

(seems I have yet to learn to post images here, so I will attempt to describe it by copying text from the verbalization window:)

 

ORM2 Verbalization

Booking Change is for initial creation.

ORM2 Verbalization

Booking Change is on Date.
   Each Booking Change is on exactly one Date.
   It is possible that more than one Booking Change is on the same Date.

 ORM2 Verbalization

Booking Change is for Booking.
   Each Booking Change is for exactly one Booking.
   It is possible that more than one Booking Change is for the same Booking.

 

 These three facts, including the newly introduced Unary, have an external uniqueness constraint over them affecting Booking Change. Copied is the verbalization shown when selecting the new Booking Change entity

 ORM2 Verbalization

Booking Change is an entity type.
Reference Scheme: Booking Change has change id.
Reference Mode: change id.

Fact Types:
Booking Change is for initial creation.
Booking Change is on Date.
Booking Change is for Booking.
Booking Change has change id.

 

 

And the last fact, introduced by Ken

ORM2 Verbalization

Booking is for Seat.
Each Booking is for exactly one Seat.
For each Seat, at most one Booking is for that Seat.

 

 

 When mapped, this will match what you said was desired; a single table, plus the new table introduced by Ken to show other information about a booking. It also makes even more clear the constraints in the earlier diagram which are probably incorrect. For example:

Only once a day may a person update some booking.

A person may create a booking only once per day.

 A Booking can only be updated by a given person once.

A booking cannot be updated twice on the same date.

 All a formula for frustrated customers!

 If someone would diagram that and post thie picture, I'd appreciate it!

 

EDIT:

 

It seems I responded to the diagram without addressing the original question!

You may certainly introduce a new entity type named something to the tun of "Creation and Update Information." Attach four fact types to it, two to person two to date, and put the preferred identifier as the combination of these four facts.  Now, everywhere in your model that references this new entity will map to show the four-part identifier. (created by, created on, updated by, updated on). You can also put in one single place in the model business rules such as:

'cannot be modified before creation' 

'creation date is mandatory'

'updated date is optional'

'if an updated date is recorded, a person who updated is also recorded'

 

 

 

Hi Orion,
Thanks for your contribution.
Here is the procedure that I use to post an image:

1: Create the image as a .png file (using Snagit)
2: Click on the "options" tab at the top of the reply window.
3: Use the  "Add/Update" button and related procedure to add the image (see below)

Ken

 

As Orion has mentioned, Terry's 2008 book covers your question.
For example on page 755 of Terry's book you will find a discussion of  "introducing controlled redundancy to reduce the number of table joins required for focused queries" - which is what you seem to be looking for.

Terry makes the point that this:
"slows down updates and makes the system harder to work with but it can dramatically reduce response times for queries"

The subsequent text contains examples and more details.

Ken

Thanks, Ken!

 

Here are the images referenced above. The first is a remodelling of Ken's diagram with two ternary fact types.

 

The second is what I believe to be an answer to the original question.

It sounds like what you're asking for is audit columns in your database. These are extremely difficult to model at the ORM level because it first adds a huge amount of noise to the model, and second is difficult to apply correctly when automated absorption patterns are applied. The extra FactTypes in the ORM model will force tables to be created where they would not otherwise have existed, may be duplicated if you absorb a subtype, may be left off of a subtype if you define them on a supertype and do not absorb the subtype, etc. The audit system should not shape the system being audited, and an auditing system applied at the ORM level obviously does affect the shape of the system.

NORMA goes through an incremental generation process where each step in the process has its own XML file format. In this case, the sequence is ORM (with relational model extensions enabled) --> XML representation of a relational model --> XML representation of DDL --> DDL formatted for a specific database target. In this case, what you want to do is modify the relational model before it becomes DDL. This facility is enabled in the tool in the February 2009 release (yes, I know it isn't posted yet. Despite paying swisscom for the 'unrestricted bandwidth' internet service, I'm in a hotel in the Netherlands with dial-up performance. I'll upload the files Saturday night or Monday).

If you provide a simple ORM model of the valuetypes you want applied attached to one ObjectType, then you should get one generated table. Provide the ORM for this model and I'll give you a transform and registration information that will add the additional columns to all generated tables. Even if this isn't what you're after I'll probably post it anyway because I know others need similar auditing capabilities. This is one of the main reasons for adding the facility to modify a generated file before moving on to the next stage.

-Matt 

The official Febrary 2009 CTP bits are now posted. The following instructions will work with the latest code. Please don't try it on anything earlier--the cycle in the input/output formats will likely crash the system.

To add audit columns to every table, create an XSLT file somewhere on your system similar to the following (you might want to change length and name settings)

<?xml version="1.0" encoding="utf-8"?>

<xsl:stylesheet version="1.0"
 xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
 xmlns:dcl="http://schemas.orm.net/DIL/DCIL"
 >
 <xsl:output indent="yes"/>
 <!-- Add a fixed set of columns to every table-->
 <xsl:template match="dcl:table">
  <xsl:copy>
   <xsl:copy-of select="@*"/>
   <xsl:apply-templates select="dcl:column"/>
   <dcl:column name="creationDate" isNullable="false" isIdentity="false">
    <dcl:predefinedDataType name="TIMESTAMP" />
   </dcl:column>
   <dcl:column name="lastUpdate" isNullable="false" isIdentity="false">
    <dcl:predefinedDataType name="TIMESTAMP" />
   </dcl:column>
   <dcl:column name="createdByUserName" isNullable="false" isIdentity="false">
    <dcl:predefinedDataType name="CHARACTER VARYING" length="40" />
   </dcl:column>
   <dcl:column name="lastUpdatedByUserName" isNullable="false" isIdentity="false">
    <dcl:predefinedDataType name="CHARACTER VARYING" length="40" />
   </dcl:column>
   <xsl:apply-templates select="child::*[not(self::dcl:column)]"/>
  </xsl:copy>
 </xsl:template>
 <xsl:template match="*">
  <xsl:copy>
   <xsl:copy-of select="@*"/>
   <xsl:apply-templates select="node()"/>
  </xsl:copy>
 </xsl:template>
</xsl:stylesheet>

 

Now add registry information (put this in a .reg file, adjust the 2005/2008 data and your file location (bolded below), and run the .reg file)

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\ORM Solutions\Natural ORM Architect for Visual Studio 2005\Generators\AuditColumns]
"Type"="XSLT"
"OfficialName"="AuditColumns"
"DisplayName"="Audit Columns"
"DisplayDescription"="Add audit columns to all tables"
"SourceInputFormat"="DCIL"
"ProvidesOutputFormat"="DCIL"
"TransformUri"="C:\\Program Files\\Common Files\\Neumont\\DIL\\Transforms\\AddAuditColumns.xslt"

When you restart your project in Visual Studio and launch the 'ORM Generator Settings' dialog for your .orm file, you should now see a 'Generated File Modifiers' header section with an 'Audit Columns' selection inside. Check the box to get additional columns in all of your files.

Note that this is a starting point. In practice you would also need triggers to populate these fields. These could also be auto generated by modifying either the DDIL and/or the DCIL formats. If anyone has written triggers to auto-populate audit columns and wants to volunteer their approach please do.

-Matt

 

Note that on my Windows 7 machine, the registry key is at HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ORM Solutions.