Every few months I give up on stacking everything on my desk and begin the arduous task of cleaning it up. In my most recent battle with desktop clutter, I came to realize that I am a book addict. Set aside the numerous entertaining novels I've bought and read over the last few months because they don't make it to my work desk. If they did, I'd never get any work done.

Here's a list of just the books currently floating in the stacks on my desk. No, I am not making this up. And no, I'm not going to give you a link to each one of them. You can always hit www.amazon.com or your favorite alternative and look them up. Many of these come from my local Borders store and others from Amazon. My Amazon Prime account has more than paid for itself.

I must also confess that some of these were not purchased recently but have somehow made their way back off my shelf and onto my desk in recent months. The order is of no particular import except to note that it is essentially a LIFO list which may provide a quasi reverse chronological order to my wandering interests and/or problems/challenges.

• Agile Principles, Patterns and Practices in C# by Martin and Martin
• The Data Model Resource Book Volume 1 by Silverston
• The Data Model Resource Book Volume 2 by Silverston
• The Data Model Resource Book Volume 3 by Silverston and Agnew
• Pro ASP.NET 2.0 in C# 2005 by MacDonald and Szpuszla
• SQL Server Analysis Services 2005 with MDX by Harrinath and Quinn
• Windows Workflow Foundation by Scribner
• Programming WCF Services by Lowy
• Pro C# 2008 and the .NET 3.5 Platform by Troelsen
• jQuery in Action by Bibeault and Katz
• Building a Data Warehouse by Rainardi
• Programming Collective Intelligence by Segaran
• Workflow in the 2007 Microsoft Office System by Mann
• Pro SharePoint 2007 Development Techniques by Bruggeman and Bruggeman
• Regular Expressions by Friedl
• Regular Expression Recipes for Windows Developers by Good
• Microsoft SharePoint Buidling Office 2007 Solutions in C# 2005 by Hillier
• C# Cookbook by Teilhet and Hilyard
• Refactoring Databases Evolutionary Database Design by Ambler and Sadalage
• SharePoint Server 2007 Best Practices by Curry and English

Update: Wandering through the house, I found several more at various favorite reading spots. They've now taken their proper place on my desk piles. Here they are:

• Pro Silverlight 2 in C# 2008 by MacDonald
• Thinking in C++ Second Edition Volume 1 by Eckel
• Chris Crawford on Interactive Storytelling by Crawford
• A Programmer's Introduction to C# Second Edition by Gunnerson
• Visual C# 2005: The Base Class Library by Balena (I won this one)

Yes, this is a sad but not unhealthy addiction. The only detriment here is to my pocketbook and the fact that I'm running out of shelf space. Do you any of you have similar addictions?

Yesterday's post dealt with Len Silverston's Party Role model and how to take it to a physical model using ER/Studio. After re-reading the post, I realized that while a party may have a role that distinguishes it as a person rather than an organization such as "Employee," a party might also have a role that is indistinguishable such as "Customer." So if I have a PARTY ROLE with a ROLE TYPE of "Customer," how do I know whether this customer is an individual or an organization if I have some business rule that must handle an order for one in a different way than the for the other?

The answer, I believe, is in adding a PARTY TYPE which tells me what "sub type" the party is but does not tell me what role the party may play in the system. This distinguishes a "type" from a "role" in that the party can only be one type but might play one or more roles in the system at the same time or at different times. Here's what I ended up with (see update 01/04/09 for corrected diagram):

In this way, I can always determine through query what sub-type the party is regardless of the role. Of course, I could perhaps deal with this by having roles that uniquely apply to a specific sub-type but I think that would defeat the purpose of Len's model.

I'd like to hear what you think.

Update: Browsing Len's Volumne 1 on data modeling, I discovered that this is indeed covered in the Party Role model on page 440. And Karen is right about the relationship. I was too eager to post up the image and so overlooked the fact that I'd created the wrong type of relationship between the PARTY TYPE and PARTY entities in ER/Studio. I'll post up a fix soon.

Update 2 (01/04/09): Here's the corrected diagram with the non-identifying relationship. See Karen's comment.

Yeah, that looks better.

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.