Back to articles list
A database designer’s job is to translate the client’s business requirements into a data model that not only stores the business data correctly but also supports the processes that use the data.
A database designer, sometimes called a database architect or a data officer, is responsible for designing the databases in an organization. He/she carefully evaluates the business requirements and drafts the data models. Then, initial discussions with the business occur to validate the understanding of the data and the business processes. A database designer’s job also includes preparing supporting documentation when building the physical database.
What Is Data Modeling?
Data modeling is a process in which a database designer creates a data model that supports his/her application. Its purpose is to represent how database objects interact and how they solve business problems.
A data model describes the structure of the data in the database tables and the relationships among them. It is represented by a series of ER diagrams that contain the major entities, their attributes, and the relationships among the entities. It is very important to build the right data model from the start.
Data modeling must also consider flexibility. No data model is ever set in stone even after the database is deployed. A data model must be adjusted over time to reflect new data and new requirements. This is why accounting for flexibility is important when you design it.
Data modeling helps you translate the business requirements into technical requirements for building the physical database more easily. It also helps you find potential performance problems with queries before you even create the database. For all these reasons, data modeling is very important.
Types of Data Models
When building a new database, your work as a database designer takes you through at least three major phases. A data model goes through an evolutionary process starting from a conceptual data model. It is then expanded into a logical data model. This, in turn, is expanded further into a physical data model, later implemented with SQL scripts.
Each type of data model is designed for interacting with different types of stakeholders. We will briefly describe and explain the use of these data models below. If you would like a more in-depth explanation, take a look at this article.
Conceptual Data Model
The conceptual data model is the first data model we build. In a conceptual data model, the main entities are defined, usually using ER diagrams. This is also when the main stakeholders from the business side participate.
A conceptual data model helps identify and define the initial scope of the business problem, the entities involved in solving the problem, and how they interact. These entities are generic representations of concepts like order, store, and employee.
The relationships between these entities are typically represented with lines linking the entities that interact in the real world. So, as an example, the order, store, and employee entities should have relationships that link between them.
Logical Data Model
The logical data model builds on the conceptual data model. Normalization techniques such as 3NF (the third normal form) are applied. We ensure all relationships between entities are represented in our data model. This step is the key difference between the conceptual and the logical data models.
Physical Data Model
The physical data model is the final and the most detailed version of our data model. In this step, we define all of the tables in our application. We also define the relationships among tables, set their cardinality, transform entity attributes into columns, and choose or define data types for each column. We make sure to set default values or constraints on column values, constraints between tables, and set collations.
Once a physical data model is designed, we usually build a set of SQL scripts that define this structure to create our database. Instead of just writing everything by hand, it’s much better to use a database modeling tool like Vertabelo Database Modeler.
The Database Designer’s Job
A database designer’s duties are not limited to technical development and diagram design. A typical day involves looking at the backlog of business requirements and seeing if anything has changed.
A Day in the Life of a Database Designer
When there are changes, the database designer analyzes the requirements and meets with the business to clarify the implications of the changes on the data model and the database. After these clarifications, the database designer updates the model with the changes. This may range from just changing a single data type to redesigning entity relationships and applying normalization if the change has a larger impact.
If there is a performance metric that needs to be met, then the database designer may have to find and specify indexes to be created. If there’s an ETL process to be mapped, he/she may specify what stored procedures carry out the data transformation.
A database designer has to consider the implications of the changes on maintenance tasks on the database, like backups, restores, and log shipping. If changes are major, then the database designer needs to discuss with the database administration team or with the development team that monitors the database. Another responsibility of the designer is to define and maintain the data dictionary for the database.
Risks and Problems Faced by a Database Designer
As with any job, some problems are predictable so you can have a plan sketched out. But there are problems you can’t predict, and a database designer’s job is not an exception.
One of the riskiest things a database designer can do is to make assumptions. This can cause unexpected problems later in the project. Whenever in doubt, it’s best to clarify with the business rather than move forward. I have seen it happen many times, to both myself and my colleagues.
We may make small assumptions about data types or the amount of data for a specific table. If we are wrong, however, this can impact the physical data model and cause a lot of rework to meet the target performance metrics.
Another problem is in assuming that you have covered everything and that there will never be errors. Problems usually happen at the end of a project when everything is already deployed.
Some scenarios require adjustments, even after your database is already deployed. This may be due to an increase in the stored data, changes in business requirements, or needs for new reports. These events impact not only your tables but also your database objects, your indexes, possibly data types, relationships, and many other things.
The Pros and the Cons of Being a Database Designer
As with any job, there are pros and cons depending on how you look at things, what you enjoy, and how you want to live.
The pros are that you always find interesting business contexts to map out into a data model and build into a database. It’s a great job for people who can focus and who like to solve difficult problems. It’s a great job if you like to communicate and solve technical problems and if you can and are curious to understand both business and technology contexts.
As with any difficult skill, the pay is quite good. If you’re working on projects with clients from big companies, business travel may be a bonus if you enjoy travel and meeting new people in new environments.
The cons aren’t many in my view. But for some people, some of the things mentioned in the pros are actually cons. If you prefer to focus deeply on your work and minimize communication or if you cannot or do not want to travel due to family constraints, then the database designer job may not be a fit for you.
Become a Database Designer!
With some technical expertise in the field, some programming background, and openness to communication, I think anyone can have a database designer’s career even if you don’t check all the boxes. The ideal skill requirements to be a database designer are briefly listed below.
- Data modeling and normalization techniques.
- Database querying and performance tuning knowledge.
- Database internals specific to the database engine required by the customer.
- Knowledge of ETL processes.
- Knowledge of business intelligence and reporting.
- Knowledge of general software architecture and design.
- Project management skills.
- Good communication skills.
Thinking about becoming a database designer? If you find yourself checking off some items in the list above, don’t hesitate – we’ve got your back! Whether you are applying for your first job as a database designer or just want to be familiar with the most important topics for the role, we have set up a list of interview questions related to database modeling.
Just getting started with database modeling? It’s important to have a tool like Vertabelo Database Modeler to help you out with designing, sharing, and versioning your data models.
We hope you enjoyed the article! Feel free to browse around for more information about the amazing world of databases.
