Back to articles list
Database design goes way beyond just drawing lines and boxes. In this article, I reflect on the process of data modeling with an emphasis on best practices, as well as on how to use tools to implement those best practices to create a good database design.
Database design is the process of producing a detailed model of a database. The start of database modeling involves getting a grasp on the business area and the functionality being developed.
If you're a bit unsure about the steps involved in the database design process, I would refer you to this description of database design steps.
Start Modeling: Talk to the Business
This is a key principle in information technology. We are solving a business problem from the data side so that the required data is available. We need to talk to the business people to understand their needs.
We must ask questions like:
- “What is the domain?”
- “What are the challenges in this domain?”
- “What are the problems to be solved?”
- “What information do we need to hold?”
By talking to the business, we can consider trade-offs that might affect the database model. We also lay the foundation for modeling.
Let's use a concrete example. Take an accounting application for a company: you would need to model customers, suppliers, invoices, payments, accounts, balances, etc. You need to learn about these concepts and about accounting. You can only do this by talking to the business people.
Getting Concepts Into Order
This initial work with business will lead you into a model of what "concepts" must be stored in the database (read this explanation of the different levels of models). From the concept of what we need to store in the database, that is, our conceptual model, we move to a logical one. The logical model documents the business concepts and rules upon which we layer details (you might be interested to read this discussion on whether logical data modeling is obsolete).
If you're unsure about the different types of data models, see our article on how to implement conceptual, logical, and physical data models with Vertabelo.
The logical data model adds more information to the concepts we have already documented. It describes how the data is structured and how the entities are related to each other. In addition, it includes information on the types of data we are managing.
In Vertabelo, we can create a logical data model through a logical entity-relationship diagram (ERD). Check the details of how to perform logical data modeling with Vertabelo.
Here is a simple, and not yet complete, logical data model of customers, suppliers, invoices, payments, and accounts.
One other advantage that I find from working with Vertabelo is that I don't need to worry too much about the exact notation. The modeling tool allows you to worry about the design and not about the specifics of entity-relationship diagram (ERD) notations and symbols, which, obviously, should be the least of your concerns during the database design process.
Let's Get Physical
To actually work with the database, we need to go from our logical model to a physical one. The Vertabelo tool allows us to generate a physical data model from a logical one easily. You first create a logical data model, then you can "auto-magically" generate a physical one by selecting the logical model and clicking "Generate physical data model" (see this detailed guide for the exact steps).
Obviously, the generated physical data model will be similar to the logical model; however, logical data types will be translated into data types that are allowed for the particular database management system (DBMS) for which you generate the physical model. The physical model will also indicate which attributes are foreign keys between tables. You may also wish to perform additional modeling related to the physical aspects of the database – for example, indexes and views.
In addition, it's possible to create a physical data model directly; you do not have to create a logical one first. Going straight to a physical model will make sense for smaller, more directed modeling activities, where the business domain is better-defined. The physical database modeling process is straightforward and shouldn't present too many challenges. Having a logical data model will prove useful for larger projects, but having at least a physical one is better than having none at all.
Evolution of Your Database Design
Developers generally think that the database model should revolve around the actual code, while data modelers think that the code should be created based on a relatively static data model. Data modeling today needs to be collaborative. The code and data model influence each other back and forth.
So, we need a tool that supports a collaborative database design process and modeling. Besides working with the business to create the conceptual design, data modelers need to collaborate during the development cycle to update the logical and physical data models as required. Modelers and developers must adapt the model until it really supports the business and non-functional requirements of the system.
Obviously, changes can lead to mistakes. Again, having a tool can help; a tool that constantly validates your data model is invaluable. Vertabelo has a built-in, live, online validation for both logical and physical data models so that problems are detected during modeling, not during implementation. And errors stay visible for everyone collaborating on it. Also, you can adjust the validation settings as required. Here is an example of my incomplete data model with several errors and warnings.
Going back to the accounting example, you might discover during development that it is not enough to model a single currency such as euros or dollars for invoices and payments. Rather, you would need to store amounts with their respective currency and convert them to the “base” currency in which the company’s bookkeeping is held. You may also need the currency exchange rates and historic information of the rates that were used for the conversion of currencies in the past.
This is where a collaborative database modeling tool like Vertabelo really proves its worth. You can find more information about using Vertabelo for collaborative modeling. You just click and share your model with your team members.
Physical to Implementation
Once you have your first version of the physical model, you'll probably be eager to start working with the actual database. To do that, Vertabelo will generate SQL DDL (Data Definition Language) scripts to create the database. I won't write all the details here, as you can find them in the online knowledge base article how to generate an SQL script creating a database.
Let me tell you from experience – this is such a welcome feature. You avoid having to deal with the vagaries of different database SQL DDL syntaxes, and you can focus on your design.
Versioning
Now, as I wrote above, your models will evolve, whether it's during database design, during software development, or afterwards during the actual usage of your database. There are two great features of Vertabelo about which I want to be sure you are aware.
First, Vertabelo includes version management. You can track modifications and manage versions of the data models, so it's easy to "turn back time" and rollback to a previous version if needed. If you are disciplined, you can tag the different versions with precise names, whether that might be drafts or actual releases of the database.
The other feature, which I have dreamt about for many years during my database modeling, is the ability of the Vertabelo tool to automatically generate migration scripts between versions of your data model. I have lost count of the times that I had to manually write and correct migration scripts repeatedly. Here is an example of generating the migration scripts between two versions of a database for an online survey.
What a boon for data modelers to have a tool that efficiently manages versions and figures out the impact of changes between the versions!
Large Models
First, let me be honest. I am not always working with large models, but sometimes I do have to create them. Here, again, Vertabelo offers us a solution to organize our models.
We can visually group tables with subject areas; if you'd like to see how to do that, you can also watch a video about managing large data models in Vertabelo.
You can also use this technique when you are reverse engineering from an SQL DDL script into a data model.
Get Started With Database Design
If you're looking for some best practices of database design, I'd recommend you take a look at this article. For tips for better database design, you need look no further than this article. And see this one for advice on how to get started using Vertabelo for your database design.
