In an era, like the one we are living in, in which there is an overabundance of data, an excellent database design lies at the heart of any effective strategy. When you design a database there are some crucial steps you must pass through, because each one is essential to reach your objective, that is a database that is functional and usable. First of all you have to identify the objects (the entities) that are going to appear in the database and the relationships between them. Then you should go in further detail to abstractly represent data relationships. Finally you should decide how to actually implement your database, choosing a particular database system and coping with its characteristics. Basically you go down from a very high level, in which you gather and represent the business requirements, to a very low level, when you actually implement your database.
In each step, what you do is to define a data model, going from the conceptual data model down to the physical data model, with the logical data model being the intermediate level.
The conceptual data model
The conceptual model does not deal with how the real database will be implemented. It works at a very high level, translating real world problems and business requirements into a conceptual framework that is easy to understand and that for this reason can be communicated also to non-experts. It just focuses on identifying the entities that will be part of the database and the relationships between them, which means the operations or associations that exist between them. It is very effective for understanding and defining the business processes of the company. So if we were to attempt a conceptual data model definition we could say that a conceptual data model is a representation of the general structure of data required to carry out business requirements (which means support company processes, record business events, and track performances) and this independently from any specific software, database management system or data storage structure.
A conceptual data model is needed to gather an accurate understanding of the data types that will be available and needed, to come to an agreement between different business units on what will be needed and to define names, data types and features of the different data types. It’s important to understand that when you deal with a conceptual data model you are dealing with objects at a very high level. For example, if you refer to a car, you mean the real world object “car” and not an abstract representation of a car; a set of metal parts connected in a certain way. This is the reason why not everything that appears into a conceptual data model may be translatable into the physical data model.
The logical data model
The logical data model is a step further, and it is the first step in actually building the architecture of the applications. It is still independent of the particular system used (for example a particular database management system) but starting from the conceptual model tries to translate the high level views that you can find in the latter into a formal, abstract scheme, adding a further level of detail to the conceptual level. Differently from the conceptual data model the logical data model uses indexes and foreign keys to represent data relationships but still they are kept independent from any DBMS implementation. It may be considered as a bridge between the conceptual data model (business view) and the physical data model (developer’s view) and can be used to check whether the actual implementation truly fulfils the requirements stated into the conceptual model.
So the difference between the conceptual and logical data model is clear. The logical data model is an abstract representation of a possible implementation, without being bound to any specific implementation, while the conceptual data model is a high level representation of the business requirements and the connected data sets and relationships. The logical model – through a process of normalisation – gives a more structured representation of entities and their relationships and goes more in detail on their relationships and characteristics: redundancies, many-to-many relationships, ambiguities and entities attribution uncertainties are resolved and a blueprint of a possible database implementation emerges.