How to create ValueType Group?

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.

Re: How to create ValueType Group?

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>

Re: How to create ValueType Group?

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?

Re: How to create ValueType Group?

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!

Re: How to create ValueType Group?

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:)

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

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'

Re: How to create ValueType Group?

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"

Re: How to create ValueType Group?

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.

Re: How to create ValueType Group?

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)

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.