What is data modeling?
When starting to create a new database, the database designer or database administrator needs to carefully plan ahead. Mistakes made in the planning phase could be very costly later on when the database is in the process of being put together and decisions have already been made on what kinds of technologies or formats are to be used.
To minimize the risks, the designer will usually start off by data modeling. The data model created will provide a blueprint for the finished database, ensure consistency, and make sure that no elements have been forgotten. The models also make it easy to consult with all stakeholders and get feedback on what problems the database should solve.
There are three stages in data modeling: conceptual, logical, and physical. Each stage brings the database closer to reality.
Data modeling stages
The conceptual model sketches out the entities to be represented and determines what kinds of relationships exist between them. It deals with the scope of the database to be created and defines the general rules that need to be considered.
The logical model will take these entities a step further and work out the details of how their attributes and relationships. It defines the structure, but does not concern itself with the technical aspects of how the database will be constructed. Read more about logical data models.
The physical model moves from abstraction to reality and considers the database management technology to be used, the design of the tables that will make up the actual database, and the keys that will represent the relationships between these tables. Read more about physical data models.
Make your own ER diagram in Gleek.
So the conceptual data model is the least technical of the three, but it’s also the stage at which the designer has the freedom to experiment and think about the design that will best fit the project. It can be tempting to skip the conceptual data modeling stage, especially for experienced database designers, but every database is different, and the conceptual model offers the valuable opportunity to understand the database before getting into the details of building it.
Find out what are the main principles for effective database design.
What is the purpose of a conceptual data model?
The conceptual data model gives the designer the chance to gain an overview of the system to be designed without being concerned with the details of how it will be implemented. This is the time to ask questions, consult with stakeholders, consider the business requirements that the database will solve, and the business processes it has to support. Conceptual data models can be very quick to create, but they can also rapidly highlight faulty assumptions and potential problems. The conceptual model is a simplified diagram of the final database, with the details deliberately ignored so that the big picture can be understood.
9 characteristics of a good conceptual data model
The ideal conceptual data model will do all of the following.
Provide a high-level overview of the system to be built.
Define the scope of the data to be represented.
Create a blueprint that can be referred to throughout the project.
Diagram entities and relationships rather than attributes.
Avoid dealing with technical considerations or terminology.
Prevent the model from already being tied to a particular database management system.
Be used to get feedback from non-technical stakeholders.
Focus on the business requirements the database needs to solve.
Provide a solid foundation for creating logical and physical models.
How to create a conceptual data model
Entity-relationship models are one of the most popular ways to create a quick and clear conceptual data model. An ER model consists of entities, attributes, and relationships. The nature of these relationships can be quickly and easily shown by using either Crow’s Foot or Chen notation. Either of these notation systems can be used to indicate cardinality, in other words whether it’s a one-to-one, one-to-many, or many-to-many relationship. You can also use class diagrams to create a conceptual data model.
What is an example of a conceptual data model?
Now it’s time to look at a few examples of simple conceptual data models. We’ll use the Gleek diagramming tool to create ER models for these and stick with Crow’s Foot notation to explain the relationships.
Library database example
Let’s sketch out the data objects and relationships needed to model a library database. The library can have zero or many books, while each physical book can be associated with one and only library. The same goes for the relationship between the library and its members, so we can use the same Crow’s Foot notation between them.
If we add the concept of loaning books to members, we can see a different relationship entering the picture. While a member can have zero or many loans and a loan can be associated with one and only member, each book can be associated with zero or one loan, while a loan can be associated with one and only one book.
This simple model doesn’t tell us that much about how the library will work, but it does get us thinking about the entities that will have to be represented in the final library database. If you want to see a more detailed model, created using a class diagram that includes attributes and other elements, check out this class diagram of a library database management system.
Order management system example
Moving on to a different example, let’s think about how an order management system might work. This could be used for a retail or wholesale store. The important thing is that it models the data objects that would need to be tracked and the possible relationships between them.
We’ll start with the customer. Each customer can send in zero or many orders, while there can be one and only one customer associated with each order.
The order will list one or many items, while each item can only be associated with one and only one order. Those items are each associated with real-world products. Each item listed in the order can have one and only one item, while the products can be associated with zero or many order items. The products can exist in the store database without necessarily ever being part of a customer order.
The customer will have to eventually pay for the order, so an invoice will need to be issued. That will complete the process, with an invoice created from the items listed in the order. Each item can be associated with one and only one invoice, while there can be one or many items in the invoice.
Make your own ER diagram in Gleek.
Again, this is a very simplified version of what actually happens in the database for a real store, but it captures the basic objects that need to be represented and the relationships between them. You might like to try sketching out your own ideas for these examples. Just head over to Gleek.io and you can start diagramming right away!
Related posts
The logical data model explained
What is the entity-relationship diagram in database design?
How do you convert an ER diagram into a relational schema?
What is data modeling? Types & process
Quick guide to physical data modeling