KB - What is a logical data model?

Data modeling is the process of creating a visual representation of data to be stored inside so-called databases. Data models ensure consistency in the labeling of entities and their attributes, semantics and ensure a certain level of quality of the data. Data modeling can be performed on three distinct levels: conceptual, logical and physical. A logical data model represents data in as much detail as possible, providing a structured and generic view of the data. Therefore, a logical data model shows an overview of all entities, including their attributes and their relationships.

An entity is a representation of an object in the real world (e.g. a ‘book’ or ‘user’). Furthermore, an attribute can be defined as a characteristic of such an entity (e.g. ‘title’ and ‘description’ for a book, ‘first name’ and ‘email’ for a user). In a logical data model, primary and foreign keys are specified for each entity. Comparing to a conceptual data model, a logical data model has more detail regarding the presence of attributes and (primary) keys in the model and the specification of relationships.

The primary key can be seen as an unique identifier of the entity or data object. For example, the entity ‘users’ will contain a lot of data, such as IDs, first names, last names, email addresses, phone numbers and so on. To ensure that each ‘user’ is unique, despite having overlapping data (e.g. identical first or last names), one or more attributes are selected as the primary key. In the example of users, the email address or a user number (ID) could define a unique user. In the case of multiple primary keys per entity or data object, this is called a composite key.

When an attribute is a foreign key, this means the attribute is a reference to an attribute of another entity. For example, the entity ‘order’ could include a foreign key of the attribute ‘client_id’ which is the primary key for the entity ‘client’. This way, a relationship between ‘order’ and ‘client’ is established. Additionally, the relationships between different entities are defined in a structured way. For example, a user can own multiple books, but each book is only owned by one user. A user also can own no books.

There are many languages that can be used to specify a logical data model. Two of the most common are the Entity Relationship Diagram (ERD) or Unified Modeling Language (UML).