If you've heard me present, then you know that one of my primary design mantras is:

Everything is a trade off.  You need to be able to explain, in both business and technical terms, the cost, benefits, and risks associated with every design decision.

If you can't do that, then you aren't doing design: you are copying design patterns from other designs and hoping for the best outcome, even if your current design has different requirements and risk tolerances.

I am often told that a database design must have surrogate keys on every table and that "right" way to implement surrogate keys is to use the Identity property in SQL Server (or the RowID in Oracle, or the Identity property in DB2).  These sorts of features of modern DBMSs do vary from vendor to vendor, so the cost, benefit, and risk associated with each varies by version and by vendor.  Most IT pros I speak with don't know that.

Many people I speak with assume, incorrectly, that Identity Property = Surrogate Key = Primary Key= Unique Index but this is incorrect. It is important for database designers to understand the differences between these concepts.

Nigel Rivett has written a great article over at Simple-talk.com about SQL Server's identity property and some of the interesting "features" of this incrementing function:

Note: An identity column is not guaranteed to be unique nor consecutive. You should always place a unique index on an identity column if your system requires uniqueness.

...

Note: The next value is the step added to the current seed; not one more than the max value in the table, or even the step from the last or maximum value.

I'm guessing that 6+ out of 10 of experienced SQL Server designers and developers are not clear on these two gotchas.  Other DBMSs have technical issues as well.  When I'm questioned on why I don't just slap on an identity property, call it a PK an move on to the next change request, I usually start asking questions:

1. Will the application need to assume that the values are always sequential?  What if the sequence is missing a few steps?  Will that break the code?
2. Will the Identity column value be displayed anywhere outside the database, such as on a report or on a screen?  Will the users be confused if a sequence is missing or if they "restart" in the middle of an order?
3. Will the Identity column value be used outside this database? How?  Where?
4. What are our plans for dealing with rows that exceed the maximum number of identity values (in some versions of DB2, identity values maxed out at 32k or so)?

Identity properties can be useful, but as in every design decision, there are uses that are appropriate and uses that are not -- it all comes down to cost, benefit, and risk.

Malcolm Chisholm has written an article for DM Review entitled "Is Data Modeling Sufficient for Database Design?"  However, I think a better title would have been "Working with Hidden Subtypes", as his comments on Codes Tables, Indicators, Nulls, and their dependence on other attributes really do focus on the complexities involved in making good data modeling style decision.

By now you get the picture. Certain attributes are only populated under certain circumstances. Traditionally, we have gotten around this need by creating subtypes in data models, thus spawning additional entities. Yet, in the above example we have four sets of supertypes and subtypes (Product Development Status, Product Category, Finish Type, and another for Finish Types of Gold and Oil Rubbed Bronze). It would be an absolute nightmare to try to model such a situation using subtyping, and anyone who did would certainly face a rebellion from the people charged with implementing it. Furthermore, traditional data modeling only permits one supertype and one set of subtypes per entity. It cannot deal with the various independent, overlapping and hierarchical natures of the subtypes we find even in the relatively simple situation shown in Figure 1. The only solution is to use code tables and indicators to deal with the problem in the context of a single Product entity.

Such a solution misses out on design. On the face of it, the Product entity on Figure 1 appears to say that every record in the physically implemented table will be uniform, in accordance with third normal form. In reality there are a number of subtypes hidden within the Product entity. These hidden subtypes are never formally identified, named or defined, let alone managed.

I'm not sure I agree that traditional data model allows only one set of subtypes per entity.  However, I have seen the rebellion he mentions if subtyping is used in the physical model.  Rebellion is a nice term for "revolt" in this case.

I use subtypes, but I have sometimes been forced to work with models that are overly subtyped to the point the models no longer provide the benefits that they are supposed to deliver.  My classic presentation story about this is a US Department of Defense data model I worked on that had some subtyping that went more than 25 levels deep.  It even said that a PERSON is a subtype of an AGREEMENT.  I don't know which world that inheritance existed in, but it did not reflect how I understood my world, nor did it reflect how most of our military users saw their world, either.  Sure, this was during the Reagan years and it was a project for the Strategic Defense Initiative, but still.....

Anyway, Chisholm has done a great job of showing how subtypes exist in our models whether or not we've chosen to use the subtype structure to represent them.  In fact, by not subtyping, we've chosen to hide them and move the subtyping rules to someplace outside our models.

One of the perks of speaking at DAMA meetings is the ability to hear what's going on in the trenches with real data architects -- those of you working day in and day out trying to squeeze even more business value out of your data models, process models, and other architectural components.

At a recent DAMA chapter event, I was discussing some of the ways I've dealt with a particular issue and the person I was speaking with said "I really wish we could have you come in for a few days and tell us how we could better collaborate with our developers".  When I said that this is what I do, the other person said that he didn't know that consultants were available for short term contracts.  He went on to explain that most of the consulting agencies his company deals with will not enter into engagements of less than 30-60 days and usually ask for 6 months to a year of commitments.  In speaking with others, I've found that the agency rule of pushing for long term assignments is common.

I guess that's the difference between getting consultants through agencies and dealing directly with independent professionals.  Sure, I've had long assignments (my longest contract ended up being seven years long), but we do quite a bit of work on shorter term, project-specific efforts. 

We even have clients who pay a small retainer and use increments as small as 30 minutes for a quick phone call on how to deal with a specific question. Some of our clients have us give yearly "Data Modeling for Non-modelers" training for new staff.  Some have us provide ad hoc commentary on their data models, especially those prepared by third parties under longer-term contracts.

My philosophy is that I want InfoAdvisors to be profitable based on quality of work, not quantity of billable hours.  There are places you can go to get a warm body to help out, with a great deal of supervision and client-provided training, but that's not us.  We aren't in that business.  In fact, our ability to work with shorter assignments is what allows us to do so much data management community volunteering and support.

You don't have to enter into some large, onerous long-term contract to have us help you meet your goals. 

Ever wonder what Microsoft means when they give someone the title of product Evangelist? Jeff Sandquist of Microsoft discusses the product evangelist role at Microsoft.

You dodged my question, so I’m asking again: How do you reconcile the line between evangelist and apologist?
You’re equating evangelism with cheerleading. Evangelism is not cheerleading. It’s not about raising your arms up and saying “This is awesome!” Evangelists know where the warts are —they know the strengths and the limitations. The right evangelist understands what competitors are doing better, and understand our strengths.

You win no one by bashing a competitors’ product. A great evangelist knows that their credibility is everything — otherwise they’re a shill. I don’t feel like I straddle the line with being an apologist, because I’m not a cheerleader — I’d never say something is what we all know it actually isn’t.

All vendors have product evangelists, even if they don't give them that title. For many vendors, this is a role played by the Product Manager, but for others, it can be a clued-in sales or marketing person's job.

I like this idea of product evangelist not being a "fanboy" role, not being a cheerleader, and no being a "there are no warts in this product" role.
Who are the ERwin, ER/Studio, PowderDesigner, Telelogic, Rational Data Architect, Visible Analyst evangelists you've met?

CA has established a Product Line Community for CA ERwin Data Modeler users.  This is a virtual user group that works in conjunction with local user groups to assist users in successfully using ERwin and ERwin-related products.

I have posted in the past about the important of funnelling all your product enhancement request through supportconnect, your local user group, and the PLC -- but I guess I have not been direct or vocal enough.  In response to a recent discussion on the ERwin User Discussion Group, I posted the following:

I have posted here many times on the importance of sending requests to the PLC and your local user group. I guess my language hasn't been direct enough. I don't know if this is true, but I get the feeling from chatting with CA folks that requests coming from the PLC have the highest priority.

I do worry that requests going through user group teams and the PLC are still vetted and summarized, which risks losing some urgency and priority, but it is my feeling (and I have nothing but intuition backing this up) that individual requests not going through these channels have very little chance of making on the list.

So I will say it again:

**** Send all your change request through the normal channels, through the PLC, and through a local user group. ****

You'll first have to "sell" your request to those groups to ensure that they understand why it is important, then they can sell the product management teams.

If you haven't formally joined the ERwin PLC, then you need to do that now, even if you aren't interested in their regular webinars or sharing your experiences with other users.  You don't join for the "club" experience; you join so that your requests for new or modified functionality get the proper visibility and support from other users.

Even if you don't have a local user group most groups will let you join -- even if they are thousands of miles away.  I'm a member of many EMUGs around the US and Canada.  I don't know if that is against their rules, but I've joined them anyway.  A couple of user groups will reject your membership requests.  If that happens, find another one to join.

My local user group, the Toronto EMUG, shared with members a list of enhancement requests and asked for our input for prioritizing.  They also solicited new requests at the same time.  If your local group didn't do that, you should contact them and find out why.  It might be a simple mismatch in e-mail addresses or a spam filter that blocked this communication.

CA is putting a ton of emphasis on the PLC.  If you want your needs for ERwin to be heard and acted upon, you need get signed up.

Not a PLC member yet? Please use the registration instructions below:

• Go to the causergroups.ca.com.
• Click on "Join Today" located in the lower right corner.
• At the drop down menu, select "PLC - Modeling".
• Enter your information.
• Submit registration.