In a relational model, we have three different types of data models with the conceptual, logical, and physical data models. Each of them has its purpose and builds on the next data model. The conceptual data model defines what the database will contain. Typically, this model would be created by the business stakeholders and the data architects. Their main goal with this data model is to organize the data, define the scope of the database and define what the business concepts and rules will be.
The conceptual model‘s purpose is to establish the entities, their attributes, and the relationships between the entities. In this data modeling level, there is not a lot of detail that is added to the actual database structure. There are some basic design steps involved in this process that are performed during this phase as this will be the foundation to the logical and physical data models.
The first step will be to discover the characteristics of the data elements. We have to start posing some questions about what the reports and queries would need to contain. We also need to know who the end-users are and how that information will be used. Knowing what the various end-user data views are going to be will also help. We’ll also need to know where the information is to be found and how the information is meant to be extracted when it is. Using those criteria, we can then determine what data elements are needed to produce the information along with what the data attributes will be. We’ll also define the relationships that would exist in the data, how frequently the data is to be used and what the data transformations may need to be to generate some of that information.
In order to answer some of the criteria about the characteristics, it can be useful to have the database design and the end-users create a description of what the end-user data views are going to be as that will help identity what the main data elements are. If there’s an existing system in place whether it is a manual process or an application, this can be very useful to identify the data and its characteristics. By taking a look at the various forms and files, we can determine what the data types may need to be, and the volume associated with it. If there’s already an automated system in place, we can also take a look at what the current reports look like as well determine what the designed reports would consist of. If an application is being developed in conjunction with the database design, it would also be useful to review the application designs for insight into the data elements.
The business rules in particular are important to determine the entities, attributes, relationships, connectivity, cardinalities, and constraints in the database. These business rules need to be simple and precise. In looking at our database, we could define some of the following business rules:
Let us take a look at a sample of what a conceptual model may look like for a simple eCommerce site. The business rules are as follows:
Notice the simplicity of the entities where the focus is on the entities, the top-level attributes, and the cardinality. Between a customer and order is a one-to-many relationship. Between order and product is a many-to-many relationship. A category to a product is a one-to-many relationship.