What are the benefits of a conceptual data model?

Why should I take the time to develop a conceptual data model? We're already developing logical data models.

Unless you are developing an enterprise logical data model, you are probably developing your logical data model as a solution for the application you are designing, whether that is a data warehouse, data mart, operational data store (ODS), transaction processing or other type of application. A conceptual data model is a business model, from a data perspective -- it is not a solution model and is application and technology neutral.

A conceptual data model, usually in the form of an entity relationship diagram, is developed in order to understand and capture business knowledge from a data (rather than a process) perspective. As a business model, it should be reviewed by the business for confirmation or correction.

A conceptual data model captures the key business entities (a person, place, concept, event or thing about which the organization wants to collect data), and the relationships between these entities. Attributes are often not added to conceptual data models as these are higher level models. Relationships should be modeled from a historical, longitudinal (non state-like) perspective. For example, if a relationship is usually one to many (1:M), but occasionally may be many to many (M:M), it should be modeled as an M:M relationship. It's the exceptions that will do you in, and it is important to know what the exceptions are in the relationships.

Sometimes it may seem like the business talks in terms of generalities and the business may not describe requirements in the level of detail required -- developing a conceptual data model can fill in the gaps between a requirements document and a solution model. Developing a conceptual data model and reviewing it with the business in order to gain approval will very often bring to mind business rules that weren't uncovered previously. When developing a conceptual data model you must act like a detective and ask probing questions and think of possible exceptions that could arise. If your solution application depends upon an existing data source, be sure to profile this data source (ideally with a data profiling tool). This will enable you to do not just a top-down analysis (interviews, review of business documentation, etc.), but a bottom-up analysis as well to develop the most thorough conceptual data models possible.

Significant problems can arise when conceptual data models are not developed. In one case, a requirements document was created after interviewing the business -- however, an incorrect relationship was modeled in the logical data model (a 1:M was modeled instead of an M:M) and data quality errors were forced into the system. Duplicate records had to be created (it was too hard to change the model after all the interfaces went into place), and the business lost confidence in the system.

Conceptual data models should be done in the requirements gathering phase of a project. It is invaluable from both a project management and development perspective. Developing a conceptual data model will help identify the breadth of the subject area and can help establish scope for the project -- if you're planning for two months for development and you have 100 data entities in your conceptual data model you will need to make an adjustment in time or scope. A conceptual data model can also serve as a basis for developing your logical data model and can be converted for this purpose.

If you want your application to be successful, it is inevitable that you must identify the proper data entities and relationships. The only questions are when will this identification occur and how much will you pay for it. These can be identified in the requirements gathering, design, development, testing/QA or production phases --each phase progressively incurring more cost to make the correction.