The logical model extends the conceptual model by defining how the database should be implemented at a high level. The logical model is database agnostic meaning that it is generalized so that it could be implemented in any database. This model is generally created by business analysts and data architects. The purpose of the logical model at this point is to create a technical map of the rules of the database as well as create the structure for the database.
By the end of the logical data model process, we should have all of the entities, attributes, and relationships. The primary key for each entity is specified as well as the foreign key that links the tables together. Normalization is also performed at this level but this is something that we will get into in a future tutorial.
The first step in this process is to map the conceptual model to the logical model using database constructs. In this process, we will map out the strong entities, supertype/subtypes, weak entities, binary relationships, and higher-degree relationships. The strong entities are ones that are on the “one” of a relationship within the data model. In looking at the conceptual model, for example, this would be the customer and category entities. You would first define the table names, columns, and their characteristics.
Once you have the strong entities mapped out, you would move to the supertype/subtype relationships and the weak entities. In our case, we don’t have any supertype/subtype relationships. One example could be a pet entity and having a subtype being a dog or a cat entity with specific criteria associated with them. A weak entity is one that’s existence is dependent on the strong entity. In the same scenario of a dog or a cat, both of those would be weak entities as well as they would inherit the primary key from the pet entity. They would also be existence-dependent on the pet entity to exist.
Next, you would work through all of the binary relationships between two entities. For example, the product and category, as well as the customer and order tables together, would be ones to map out the one-to-many relationships. Once you have those relationships, you would define the relationships between three or more entities until all of the relationships are defined. In our case, we had a many-to-many relationship between the order and product that needed to be resolved. We will get into that level of detail in a future tutorial but just make note that for any many-to-many relationship in a conceptual model, it must be resolved by having a bridge/middle table that creates two one-to-many relationships.
In this case, we will also define the primary keys and foreign keys as part of the relationships as well as validate the model through normalization that we will also get into in later tutorials in more detail. We would typically also define the integrity constraints of the data as well if needed. For example, we may define that the quantity as a numeric value that must be an integer and be greater than 0.
Typically, we won’t define the data types and sizes in the logical model as it is not meant to be database-specific. However, it’s acceptable to generalize the data types in the logical model. The following would be an example of the logical data model for the eCommerce company that has been extended. We will get into the specific details of each of the steps through normalization in an upcoming tutorial.