Many projects, be they physical or task-related, progress through a series of steps that take the project from the idea stage to the real world. In data modeling in particular, there are three progressive modeling stages: the conceptual model, the logical model and the physical model. Each model is different from the others, but the biggest differences occur in the high-level overview of the conceptual model and the detail-oriented nature of the physical model.
Conceptual Model
A conceptual data model simply identifies the highest-level relationships found between entities. The features of the conceptual data model include the entities and the relationships among them. No attribute is specified and no primary key is specified. The only information shown on a conceptual data model are the entities that describe the data and the relationships between those entities.
Logical Model
A logical model describes, in detail, the data and how that data will be implemented in the database. The logical model includes all entities and relationships, each entity’s attributes, primary keys, foreign keys for identifying the relationships between different entities, and normalization at the logical level. The logical model is created by specifying primary keys for all of the entities, finding the relationships between the entities, finding the attributes for each entity, resolving the many-to-many relationships and, finally, normalization.
Physical Model
The physical data model is representative of how the model will be built within the database and shows table structures that include column name, column data type, column constraints, primary key, foreign key and the relationships formed between the tables. More specifically, the physical data model includes table and column specifications, foreign keys to identify relationships, physical considerations, and physical data. This model is designed by converting entities into tables, converting relationships into foreign keys, converting attributes into columns, and modifying the physical data model based on constraints and requirements.
Differences
The differences between the conceptual model and the physical model are primarily of scale and depth. The conceptual data model starts with the idea and uses that idea to suggest what a system is and how that system might work by describing data and the relationships between entities. The physical model, on the other hand, goes into much more detail by showing how the data will be built, and diagrams the relationships between the entities by converting the information into tables and columns. A physical model may also be a model that is built to scale to represent the conceptual data that is simply diagramed on paper to show the data and the relationships.