What is a conceptual, logical and physical model?


David Hay has written an excellent short presentation that describes the differences between these and places them within the context of the Zachman Framework.

He makes a greate point – how can we expect others to think about language/definitions when those in the data management community often use these terms in such an inconsistent fashion.

The presentation can be seen in the following – Introduction to Data Modeling

David mentioned this presentation in the following – LinkedIn discussion


Open data model


The Open Data Model site was launched recently.

It’s aim is to:

“bring together data modelers and subject matter experts both from IT and business. The purpose is to reach consensus on the best way to represent any given set of facts, by ensuring those facts are truly understood.”

Effectively to build up a set of conceptual models for different domains which can then be used as starting points in modelling efforts.

(As far as I am aware) It has only been running for a few weeks and has already built up some really useful resources.

Definitely worth taking a look at.

Note you have to register to access most parts of the site.

Data modelling standards


How to Draw an Architectural Data Model in UML – was published recently by David Hay. It lists a set of guidelines to follow when using UML notation for data modelling.

For those using ER/Barker notation, he also published a set of guidelines a number of years ago – see Data Model Quality: Where Good Data Begins.

Both are useful resources and worth taking look at. Especially if you are thinking about creating modelling standards within your organisation.

Enterprise architecture model resource


I’ve come across a really useful resource created by Louise McDonagh.

An Enterprise architecture model consisting of ‘generic data, function and application architecture model framework’.

The ‘data model’ is in effect a set of conceptual model patterns – based around the following subject areas:

  • Activities and events
  • Actor/parties
  • Agreements
  • Assets
  • Business rules
  • Locations
  • Financial accounts
  • Product and services

A set of definitions is also provided – could be useful for initial ‘straw man’ definitions with stakeholders.

Definitely worth taking a look at – especially for anyone about to undertake or currently undergoing a business capability or business information modelling exercise.

Enterprise Data Modeling 7 Mistakes You Can’t Afford to Make


An interesting article by Karen Lopez – Enterprise Data Modeling 7 Mistakes You Can’t Afford to Make. Definitely worth taking a look at.

The first one ‘Forgetting that an enterprise architecture is a living framework’ – not viewing ‘data architecture as a final, fixed deliverable’ particular resonates with me at the moment.

A mistake I would add to the list is along the lines of ‘Getting too bogged down in detail’.

There is definitely a sweet spot in getting just enough level of detail to make the enterprise model useful to it’s intended audience (or should that be audiences?) whilst not swamping it and making it a ‘boil the ocean’ type process to create and maintain.

There must be a couple more to make it to 10?

Conceptual model patterns


What are they
Common model patterns are based on the premise that, whilst no two organisations are the same, there are common underlying structures that can be adjusted for individual organisation requirements. In effect, they are reusable templates that can be used, as a basis, to create conceptual models for a specific organisation.

These templates can help model the scenarios that are common to all enterprises e.g. Party e.g. Person/Organisation, Party Relationship e.g. employee, customer, supplier, Location, Finance, HR and are based on the experience of modellers across different business domains and industries.

It is worth noting that they are not database design patterns. They should be used to assist in the creation of organisation specific conceptual models, which then might be used as input for subsequent logical modelling exercises and application/database design.

Patterns tend to be set at two different levels of abstraction:

  • Enterprise level patterns – e.g. Activities, Assets, Locations and Parties – typified by those produced by David Hay.
  • Domain specific patterns e.g. Finance, Insurance and Marketing and for industrial sectors such as Insurance, Financial and Professional services – typified by those produced by Len Silverston.

Most patterns tend to be written in ER notation – though other notations such as UML could be used (in fact David Hay has used UML throughout his latest book Enterprise Model Patterns: Describing the World.

The following page – from David Hay’s site – includes a pattern for Party/Party Relationship

This is a typical pattern – providing the model and a brief explanation. In the books (referenced below) definitions for key terms are normally provided as well. This pattern can be used as the basis modelling the variety of party relationships types such as – ‘customer’, ’employee’ and ‘supplier’ – that occur in a typical enterprise.

Why use them?
1. Produce a conceptual model more quickly
Whether modelling data at an enterprise or at a project level conceptual model patterns can be used to quickly create an initial conceptual model. This coupled with the fact that terms and definitions are also provided, can be especially useful if the modeller has limited experience in a particular business domain. Also, it can help get over the issue faced at the beginning of all projects – staring at a blank piece of paper. Their use can help to get over this issue – quickly providing an initial straw man model and definitions which can be the basis of initial discussions with subject matters experts/stakeholders.

2. External validation
The models produced for an individual organisation can be compared to appropriate external patterns. Each organisation is unique and there are likely to be differences. But, if these are large it might be an indication that further analysis is required.

3. Can help change the perception of data modelling in projects
Data modelling is perceived as a ‘bottleneck’ by some project teams – with many not giving appropriate time/resource to data related tasks. It is often seen as a ‘nice to have’ but taking too long to produce worthwhile output . As mentioned above they can be used to quickly build a straw man model – to start providing an agreed-upon set of concepts, a common language, definitions and business rules – right at the ‘get go’. This can then get the team to start thinking explicitly about modelling data right from the beginning. This is in contrast to taking a number of weeks to create an initial model. In some projects, sadly this is perceived as taking too long and the team will have gone off and done their own thing.

4. Enterprise view point
As discussed in a previous blog Agile database development 101 – many projects do not take an appropriate enterprise viewpoint. Symptoms of this can be seen in many organisations. Multiple siloed systems with redundant data and associated data quality issues. Separate customer, supplier, contact, opportunities etc etc – making it very difficult to have a consolidated view within an enterprise for a person/organisation. Many patterns take an enterprise viewpoint, and if used by a project team, can help steer them, or at least make them aware, of the enterprise viewpoint.

1. Not a silver bullet
It is important that they are seen as a starting point and that they will need to be adapted. The hard work of analysis/modelling an individual organisations business requirements/rules is still required.

To (poorly) paraphrase a quote from Len Silverston
‘All organisations – across all business sectors – share 50% of the same model.
Organisation in the same business sector share 75% of the same model.
The other 25% is what makes your organisation unique.’

Conceptual model patterns can (hopefully) be used to quickly get to the 75% point. But, the other 25%, and the hard work required to get there, is still key.

2. Need to think through the templates models
Using a pattern will mean that a modeller will not have gone through the process of thinking through a model from scratch. Therefore, they might not gain the insight this process can provide. The potential danger of this approach needs to be balanced against the benefits gained from reusing other modeller’s experience and expertise.

Links and references
David Hay
Essential strategies – his website

Data Model Patterns: Conventions of Thought

Data Model Patterns: A Metadata Map

Enterprise Model Patterns: Describing the World

His TDAN articles on this topic and others – including great general advice on modelling e.g. conventions for layout and presentation.

Len Silverston
Universal data models – his website

The Data Model Resource Book Volumes 1, 2 and 3

His TDAN articles on this topic

Hopefully, this has provided a good overview on conceptual data patterns. It would be great to hear the opionions of others on this subject – especially on the advantages/disadvantages of their use.