A central theme in my current work is the idea of a universal data model. Research in this direction led to noted author and consultant Len Silverston and his work on the subject which led to licensing ER/Studio and Len's models from Embarcadero. Soon after that, we invited Len to our offices to help us better understand his approach to data models and the process of data modeling. It was an informative and very productive three days. I wholeheartedly recommend his books and his consulting services to you. (See www.univdata.com.)

One of the most difficult concepts to grasp, for me at least, in the realm of data modeling is the transition from logical data model to physical database schema. When I look at boxes and lines with crow's feet connectors, I think of physical database tables. So looking at a logical model like this one makes me think of twelve tables.

The model depicted above (created using ER/Studio) is a simplification of Len's party role logical model that he talks about in his book Data Model Resource Book Volume 1. I've marked the entities that are "logical" and not physical in nature with a yellow background. ER/Studio allows me to mark these entities in the model as "logical" so that they will not be generated in the physical model that the modeling tool will create for you. The only problem is the PARTY ROLE TYPE entity, if marked as "logical," does not get generated and the relationship and foreign key does not get created in the PARTY ROLE table in the physical model.

When you add attributes such as those that PERSON and ORGANIZATION have that are unique to a sub-type, you have to push them to the physical model. Where the sub-type has no unique attributes but can be represented by a unique value record in the super-type entity/table, that entity can remain "logical only."

So now I modify the model and remove PARTY ROLE TYPE and create a relationship between ROLE TYPE and PARTY ROLE and then generate a physical model and this is what I get:

From the physical model I can generate a database script and create a database. Perhaps it is not practical to generate the physical model from the logical model in all cases, but where it can be done in order to avoid maintaining two separate models and risk synchronization issues, I would encourage it.

I'm looking forward to getting Len's next book that focuses on patterns in data modeling which ships next week. Amazon already has my order.