Sunday, March 26, 2006

How To: Experience the Data Model

Not everyone can be proficient at creating and comprehending data models. Modeling is a very abstract concept with its own unique hieroglyphic language. So to make it easy on everyone, why not allow people to experience the model as you design it.

1. As your defining the logical data model, stop each week and create a physical model based on the logical model designed so far
2. Create the data schema scripts and build the database on a server some where
3. Auto generate a “Throw-Away” UI based on the schema using a code generator
4. Release the generated UI on to a web server some where
5. Allow your key users that are giving business input on the design access to the UI
6. Get feed back from these users

Most users will describe data and system requirements in terms of UI presentation and reports. This approach is a very natural way to communicate with users and technology has advanced enough to enable us to accomplish this at very little cost.

There are several code generators that can generate UI and code based on templates and database schema. The one that I would recommend would be, because it just works right out of the box. I was able to generate a complete UI within a matter of hours. This one generates code based on Windows DotNet. There are other UI and code generators out there, but I had difficulties getting the output code to work the first time. It is very important to have hassle free “Throw-Away” UI and code generation. NOTE: Though I say “Throw-Away”, the generated code and UI are actually quite good and usable. Plus you can create your own UI templates.

By generating a “Throw-Away” UI for users to play with, you allow the user to experience the Data Model. This speeds up communication and comprehension of the data model. You can then capture the user’s feedback and categorize them into: data definitions and relationships; business rules; workflow; aesthetics; usability issues. Then place the user feed back into a requirements document. Not a bad week’s worth of work!

During this phase, don't get distracted into customizing the UI for aesthetic and usability reasons unless it is dirt cheap to do so. Just record this information into the requirements document. Stay focused on data definitions and relationships in order to get those defined properly. Once the model has been fully defined and explored, you can then start customizing the Generated UI or start a customized UI from ground up.

Note: If the data model is quite large, then you can break up the model into subject areas in which each subject area has its own exploritory and development cycles.

1. Key users get a better understanding about the data model being designed
2. Better feed back from key users
3. The database scripts can be reused for development
4. The data captured by the users can be used to seed the database with example data for developers and testers to use.
5. The generated UI may turn out to be satisfactory to the users needs and requires little or no further development or customization.
6. You have a solid requirements document to develop a customized UI
7. The weekly delivery primes the development cycle
8. Gets the developers hungry to use a code generator and code templates for elements of code needed to create a customized UI.
9. Lowers the number of development and test resources required for the project

Bottom Line:
1. Increases your chances of the new system fulfilling the actual business needs
2. Decreases your time-to-delivery for a working system
3. Increases reliability, predictability, and maintainability of the new system by encouraging the use of code generators and code templates in strategic ways early on in the design phase

Warning: Garbage in Garbage out. This is very important. Get an experienced data modeler. Your system is driven by the business definitions as well as processes.

Thursday, March 23, 2006

Model Design Patterns For: Document Management System

The following design pattern I’ve known for several years and have implemented it to great success. The subtles are what will save you:
1. Having an explicit ExpirationDate will save you having to look up what an initial contract’s end date is when you imply that the Start date of the replacing contract is the end of initial contract (When in doubt, be explicit not implicit);
2. You can use Between statements for getting contracts that are currently active;
3. Not mixing non-customer-visible change history with official versioned data will save you from having to always filter change history out of the resultset.

The design pattern below will cover a simplified contract management model that will track the work flow status of a contract and provide simple change control. This example is just to demonstrate the design pattern and can be used in variation for other concepts as well. It will simplify the understanding of your system.

Concept: Contract
A contract defines the terms for services to be rendered in exchange for money between two or more legal entities. A contract can not be changed once it has been activated. If you require to change the contract after the contract is activated you need to Terminate the contract and create a new contract with the required changes.

Concept: Contract Effective Date
A contract’s effective date represents the date the contract will be activated. This date can’t be changed once the current date is less than its effective date.

Concept: Contract Expiration Date
A contract’s expiration date represents the date the contract expires. Open ended contracts have no expiration date.

Concept: Contract Termination Date
A contract’s termination date represents the date the contract was terminated. Use this date to prematurely cancel a contract and yet preserve the original expiration date.

Concept: Contract Status
A contract’s work flow is controlled by its status: Draft, Approved, Active, Terminated, Expired.
1. Draft – Represents that the contract is a draft. Contract and terms can be changed freely.
2. Approved– Represents that the contract has been approved. Contract and terms can NOT change except the Contract Status, Effective Date and Expiration Date.

3. Active – Represents that the contract is activated. Contract and terms can NOT change except the Contract Status and Expiration Date.
4. Terminated – Represents that the contract has been terminated prematurely. Contract and terms can NOT be changed.
5. Expired – Represents that the contract has naturally Expired based on the contract’s expiration date. Contract and terms can NOT be changed.

Concept: Change history tracking
Change history tracking tracks all changes done to a table. Please see for technique.

ContractID (PK)
ContractNumber (AK)
… (Other attributes about the contract)


ContractID, RecordedDateTime (PK)
… (Other attributes about the contract)


StatusCode (PK)
StatusName (AK)

Tuesday, March 21, 2006

Modeling Questionnaire

Here are some strategic questions to ask key business personnel to help accurately model how the business uniquely defines its information.

Entity Questions:1. What is the list of Items (or Entities) that you are tracking or would like to track?
2. How do you uniquely identify each {Entity Name Here} ?
3. Do you manually assign identification to each {Entity Name Here}?
4. Can the unique identifier change for a {Entity Name Here}?

Entity Relationship Questions:
1. Can {Entity A} have 1 or more {Entity B}?
2. Can {Entity B} have 1 or more {Entity A}?
3. Can {Entity A} exist without {Entity B}?
4. Can {Entity B} exist without {Entity A}?

Entity Subtyping Questions:
1. Are there one or more types of {Entity Name Here}?
2. Is a {Entity Name Here} a type of {Entity Name Here}?
3. Can a {Entity Name Here} be classified under more than one subtype?

Entity Attribute Questions:
1. What is the list of attributes that you directly associate with a {Entity Name Here}?
2. Is this {Entity Attribute Name Here} Required?
3. Is this {Entity Attribute Name Here} Changeable?
4. Is this {Entity Attribute Name Here} made up of multiple values?
5. Is this {Entity Attribute Name Here} a Calculated value?
6. Is this {Entity Attribute Name Here} unique?
7. Does this {Entity Attribute Name Here} be selected from a standard list of values?

Entity Mastering and Business Ownership Questions:
1. What is the list business areas that are affected by this information model?
2. Does {Entity Name Here} get mastered by this {Business Area}?
3. Does {Entity Name Here} get used by this {Business Area}?

Wednesday, March 15, 2006

How To: Build a Change Log into Your Database

How do you track all changes for a table within a database? Easy. Build a corrisponding history table that mirrors the table in question with the addition of a datetime column to the primary key and an action column to indentify the action that got this item into the history table (Insert, Update, Delete). The history table is maintained by a trigger on the master table.

PersonID (PK)

PersonID, RecordedDateTime (PK)


Saturday, March 11, 2006

Logical Data Modeling: An Introduction

Data Modeling In General
I found it worthy to note that the purpose of creating a normalized logical data model is to accurately document the business entities and relationships between them in a detailed model. The value of this logical model is two fold: One is for the business owners to have detailed comprehension of their own business information; Second is to transfer this business knowledge to the developers to equip them to accurately build the system to the exact business needs. They will also create an optimized physical data model (Schema) for the system based on this logical model. Therefore it is critical that you interview the appropriate knowledgeable business people and document the business as much as you can. You will run across business areas in which the business may not be well defined. It will then be your responsibility to understand this area as much as you can, model it accurately, and get approval on the design by the business owners.

Meta Modeling: A WarningDO NOT fall into the temptation to meta model a business area that is not well understood by the business owners in the attempt to avoid your due diligence. This includes throwing in many-to-many relationships into the model to solve problems where the relationship between entities were unclear. This will lead to complications in development and result in a system hard to maintain, use, and raises the cost of ownership. Think like a lawyer. The point is to model the business accurately and provide a natural means to describe and enforce the definition and relationships of the business entities that need to be managed. Only after you have exhausted all avenues of research is when you should use a meta model. But isolate that meta model to be applied specifically to the area that is requiring that level of flexability. Then the meta model will be your friend and not your enemy.

Step 1: Use Case Modeling
The first thing you need to do is document the real world scenarios of the business areas you need to model. This should be detailed in proper sentences to work out the nouns and verbs. The nouns and verbs will be your key to discovering the data model. Of course there are many other benefits as well: Classifying your users, identifying external systems and data dependences, identifying user interfaces, discovering required reports, understand business processes, and finding the scope of the system you are to build.

Step 2: Extended Relational Analysis
Data modeling using the rules of normalization ( is not the most natural and easy way to model any system. This can be proven by the very fact that many people don’t truly understand how to normalize a data model. But there is good news. There is a different approach that makes it childs play to create a normalized data model using a technique called Extended Relational Analysis (ERA). I’ve learned it 13 years ago and has been critical to my career. So I highly recommend it. This doesn’t mean that you don’t need to understand the normalization rules, but it does mean that you don’t have to memorize them in to a daily magical chant so you don’t forget. Think of ERA as an as a way of thinking and a means to organize your thoughts rather than a software tool.

ERA technique is broken up into three areas: Entity Analysis; Relationship Analysis; Attribute Analysis. Entity analysis uses nouns out of the use case model to help define each entity that a business is required to deal with. Relationship Analysis uses verbs out of the use case model to help define each relationship between business entities. Attributes Analysis uses the modifiers (Adjectives and Adverbs) to help finish the definition of each business entity defined or create new ones missed with the other two analysis steps. There are many classes you can take out there to help you learn this technique in modeling ( Its worth the expense, trust me. It will make your modeling efforts much easier and the end results increase your chances of lowering your total cost of ownership of any system you build. Just do it.
For more detail see