This tutorial explores the steps in designing a new relational database. We begin by looking at the conceptual model design in two parts:
- Conceptual Data Model Design
- eCommerce Example
1. Conceptual Data Model Design
We will now take a closer look at the relational database model. In a relational database, we have three different types of data models: conceptual, logical, and physical. Each of these data models has a unique purpose and builds on the others.
The conceptual data model defines the main data objects, that is, what the database will contain. Typically, this model is created by the business stakeholders and the data architects. Their goal with the conceptual model is to organize the data, define the scope of the database, and define the relevant business concepts and rules. The conceptual model does not depend on any database management software or hardware. The conceptual model establishes the entities, their attributes, and the relationships between the entities. At this level, there is not a lot of detail added to the actual database structure. However, there are some basic design steps performed during this phase because the conceptual model is the foundation of the logical and physical data models.
-
The first step in conceptual model design is to discover the necessary characteristics of the data elements.
We start by posing some questions: What would any reports and queries need to contain? Who are the end users, and how they will use the information? What are the various end-user data views going to be? Where is the information to be found, and how the information is meant to be extracted? Using those questions, we can then determine what data elements are needed to produce the information, along with what the data attributes should be. We can also define the relationships that exist in the data, how frequently the data is to be used, and what any data transformations may need to be to generate some of that information.
-
It can be useful to have the database designer and the end users create a description of what the end-user data views are going to be, as that will help identify what the main data elements are. It can also be useful to look at any existing manual process or application, if there is one. 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 them. If there’s already an automated system in place, we can also take a look at what the current reports look like, as well as 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.
Business rules are also important for determining the entities, attributes, relationships, connectivity, cardinalities, and constraints in the database. These business rules need to be simple and precise. In looking at our Postgres database, we can define some of the business rules as follows:
- An artist can record more than one album.
- An album can only list one artist.
- An album can store multiple tracks.
- A track can only belong to a single genre.
- A track can only be sold under a single media type.
- An employee can be the main support representative of many customers.
- A customer can only have one main support representative.
As you can see, these business rules help define what the entities are and what the key attributes and relationships will be. They also, in many cases, start to define the cardinality between the entities. It is important that the business rules are accurate and vetted by the business end users and stakeholders. Having incorrect business rules can create underlying problems in the database that will affect the applications that rely on it.
-
-
- Conceptual Data Model
- A data model design step that establishes the entities, their attributes, and relationships between entities in a relational database.
2. eCommerce Example
Let's take a look at what a conceptual model may look like for a simple eCommerce site. The business rules are as follows:
- A customer can place many orders.
- A customer uses a credit card to pay for the orders.
- An order can only belong to a single customer.
- An order can consist of many products.
- A product can be purchased by many customers.
- A product can belong to a single category.
- A category can consist of many products.
A sample conceptual model would look like the following:
Based on these business rules, we see the following relationships:
- Between a customer and an 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.
The conceptual design model focuses on the global view of the entire database, while avoiding the details. It provides a bird's eye view of the data environment. It is the simplest model, compared to the logical and physical models.
