conceptual, logical and physical modelling
conceptual modelling is mainly for understanding the relationships and workflows between entities within a business.
For example, a customer can purchase mutliple products, so there is relationship between customer and product entities.
Typically people Entity Relationship Diagram (ERD) to visualize the models.
The entity is a rectangle box. The relationship is a line with different ends to represent different cardinalities.
0 to 1
0 to many (visualized by a crow feet)
many to many
1 to 1
etc.
The line can be a solid or a dash one depending on it is a 'identifying' or 'non-identifying' relationship.
An identifying relationship means the child record can not exist without the parent record. e.g. a car service log entity refers to a car entity. The child entity service log can not exist without a parent record of the car. The car id forms part of the primary key of the service log.
A non-identifying relationship means the child record can exist without the parent recrod. e.g. a person entity's postcode refers to a postcode table. But the person can exist without the postcode.
logical model enriches the conceptual model to bring in actual data attributes, while the conceptual model focuses on only entities and relationships. Logical model also brings in extra operational and transactional entities to model the business. e.g. A customer entity purchases product entities through orders, so there is an extra 'Order' entity in the logical model to describe the relationship better, while 'Order' is transactional.
Physical model, is the actual implementation of logical model with respect to a database system. It adds primary key, constraints, indexes, etc. and renames entities to conform with convention. It also adds extra entities to achieve database implementation of relationships. E.g. The Order and Product entities have a many-to-many relationship, so in database implementation, the physical table uses a mapping table Order_Product.
All the three types of models can use ER diagram to visualize.
Conceptual model vs Logical model vs Data model:
ERD feature Conceptual Logical Physical
Entity (name) Yes Yes Yes
Relationship Yes Yes Yes
Column Yes Yes
Column’s Type Optional Yes
Primary Key Yes
Foreign Key Yes