How to Become a Database Designer
Database modeling has some science, some art, a lot of techniques, and quite a bit of general wisdom. All good database modelers study a lot, practice a lot, cultivate creativity, and develop interpersonal skills.
The road to becoming a database designer may seem arduous. But if you enjoy working with data, giving structure where there seemingly is none, and helping people find hidden truths in tides of information, you will definitely find the journey enjoyable.
What Is Data Modeling?
First things first. Let’s be clear on what data modeling is. Data modeling is the process of creating a conceptual representation of the information to be contained in a database. The result of this process is a data model, with data objects (the entities for which information is to be stored), the associations or relationships between them, and the rules or constraints that govern the information in the database.
The person in charge of data modeling is the database designer, although modeling is often among the responsibilities of the database engineer or architect. The database designer’s job is not limited to simply drawing diagrams but includes reviewing requirements – either by reading documentation or interviewing users – then creating a data model that fits those requirements. This is why you need data modeling.
Once a data model is created, the designer must support that model and walk it through its evolution and lifecycle. This means working closely with programmers, analysts, and anyone else who needs to use the model. It also means accepting requests for changes to the model, taking suggestions to improve it, or adapting it to new needs.
Steps Toward a Database Designer’s Career
Rome wasn’t built in a day, and you won’t become a database designer overnight. To get to the finish line, you have to follow a series of steps. It starts by getting a degree, then deepening some specific knowledge, learning to use tools, getting certified, becoming an expert in certain specific skills, and finally learning skills indirectly related to the activity you want to perform – the so-called "soft" skills. But let’s take them one step at a time.
1. Earning a Degree
The first important milestone in any database designer’s career is to get a degree. Associate and bachelor’s degrees programs are the best path for those who want to gain an introductory understanding of database design and software development. Associate degree programs cover specific aspects of database design and software while offering more general education and business courses. Bachelor’s degree programs offer even more business-related courses and can prepare graduates for more senior positions in the field of computer science or software development.
The most obvious difference between the two types of degrees (besides the cost) is the time it takes to earn each. For a full-time student, an associate degree program usually takes two years. A bachelor’s degree program, on the other hand, usually takes four years for a full-time student. Obviously, the four years of the bachelor’s degree program will allow you to learn much more than you would in an associate degree program.
You can take a short course on database design. But short courses typically require you to have some prior knowledge in different aspects of computer science. There are also courses designed for people who are not computer literate. Vertabelo Academy has a wide range of such courses specially designed for non-IT people.
You also have the option, if you are disciplined enough, to learn on your own without taking a degree or course. There are a lot of choices in database design books you can read. Just keep in mind that, when it comes to preparing your resume, it won’t do you much good to mention you’ve read this or that book on design. In contrast, it does help to have a degree or at least a certificate that you’ve taken a course.
2. Deepen Your Knowledge
Once you have acquired the necessary knowledge on database design, consolidate and reinforce it with real practice. The theory you learn in a course or a degree, even along with the exercises you are given to solve, only gives you an idea of the problems you face in your work as a database designer. Incidentally, this additional knowledge prepares you better for when you have to go to a job interview for a database designer position. The following are the skills you need to master to become a database designer.
Normalization, Keys, and the Designer’s Instinct
Whether you’ve taken a degree or a course or just read a book, you will undoubtedly have learned something about first, second, and third normal forms. But to reinforce your knowledge, look at different entity-relationship diagrams (I assume you already know what ERDs are and what they are used for) and analyze them to see if they are normalized. You should be able to determine in which normal form they are.
In this diagram, the same entities are represented in two different ways. Can you tell at a glance in which normal form the table on the left is and in which form the schema on the right is?
I also recommend studying the problems a schema without normalization can cause. You can compare the space occupied by a non-normalized schema and a normalized schema of the same data to see the differences – with the tables populated with a reasonable amount of data, obviously. Or you can try to run queries on non-normalized tables to see the inconsistencies that appear.
There are a couple of things you learn with practice. First, you should never leave a table without a primary key. Second, when two tables are related, you should define a foreign key that associates the involved fields of both tables. If you forget these tips, you are reminded when you find duplicate rows in a table or orphan elements in master-detail relationships, and that gives you nightmares.
Speaking of keys, you’ve probably heard of natural keys and surrogate keys. Until you have a lot of experience, you have to take the time to think about what type of key you give to each table you create. Once you do a few dozen ERDs, you will have developed the designer’s instinct to create schemas in the third normal form effortlessly, with the correct relationships and the best key for each table.
Data Warehouses: Dimensions, Measures, Schema Models, and Other Concepts
Database design concepts are usually taught with transactional processing in mind. But today, designers increasingly deal with databases for analytical processing. These databases are known as data warehouses and have some design ideas and techniques that differ from those of conventional databases.
Data warehouses are built using the traditional relational model, but with several new additional concepts. For example, some fields represent dimensions, while some represent measures. Some tables collect facts, while others group dimensions. Specific design models for data warehouse schemas now exist, like star, snowflake, and galaxy.
Finally, data warehouses introduce a technique called denormalization. Unlike what you might think, denormalization does not mean reversing any previous attempts at normalization. To understand what denormalization is used for, think of it as a technique to introduce redundancy in table designs to simplify queries and data visualization.
3. Choose Your Tools
In addition to the theoretical knowledge your studies give you and the practical knowledge your experience gives you, you must master the tools for database design. The tools of the database designer are those that help you with the task of creating data models.
The truth is, good designers can do their job without any tool other than pencil and paper. But to be efficient and to accomplish their tasks in the shortest time possible, they must use tools that help them be more efficient.
The tools that save the most time for the database designer are the ERD design tools, such as the Vertabelo.com platform. These tools simplify the database designer’s job of drawing diagrams, automate the creation of SQL scripts, and generate diagrams from existing databases.
Another fundamental tool for every database designer is the SQL language. DDL (data definition language) is required to create or modify schemas, but to be a database designer, you must also know how to write SQL queries to obtain information from a database.
There are other useful tools for the database designer, such as monitoring and profiling tools and programming languages for data analysis and database administrators. If you know these tools, listing them adds value to your resume.
4. Get Certified
We’ve talked about mastering tools, but how do you prove in your resume that you know how to write SQL or that you know how to use some ERD design tool? You do so by taking specific courses, taking exams, and getting certified.
There are basically two types of certifications: those that show you have mastered a particular tool and those that show you have a certain skill. Both add value when displayed on your resume.
Companies that develop the tools often offer training programs, and when you complete one of those training programs, you get a certificate as proof you have mastered the tool. These certifications are often free. They are interested in certifying many professionals in the use of their tools, so it is in their best interest to train you to use them without charging you (much) for it.
Certifications in a certain skill – such as, for example, SQL programming, NoSQL database management, or ETL process design – are often expensive, but they add a lot of value to your resume, mainly because they save interviewers from having to evaluate whether you really know how to do your job. Post-graduate courses are a suitable option if you already have a degree.
5. Go All the Way
You have already studied and learned the materials to be a database designer. You have deepened your knowledge. You have taken courses and obtained certifications. What are you missing to become a database designer? Knowing how to do key tasks and being able to execute them from start to finish.
Modeling a Database
You may know how to draw an ERD, but modeling a database involves more than that. To be a database designer, you must be able to go from surveying and analyzing requirements to generating and executing scripts to transform diagrams into working databases.
Writing Complex Queries in SQL
It is not simply a matter of a query returning the data it is supposed to return, but also returning it as quickly as possible. Knowing how to write SQL queries means thinking with the same logic as the database engine to gather the requested information and deliver it with minimal effort.
Optimizing database performance includes creating an appropriate design and monitoring database activities to detect operations that degrade performance. It also involves knowing how to troubleshoot performance issues, whether it’s creating indexes, redesigning the schema, or informing the DBA of potential problems in database engine optimization.
Securing a Database
While assigning permissions and privileges on database objects is normally not the database designer’s job, he or she can identify areas of responsibility in their designs that make it easier for an administrator to correctly assign permissions to each database user.
6. Acquire Soft Skills
This last step is often overlooked in a database designer’s career, but don’t leave this unaddressed. In many situations, the soft skills mentioned below are as important, if not more so, than traditional technical skills and knowledge, a.k.a. the hard skills.
Designers with business acumen can better interpret and give the right dimension to every requirement they receive, and that translates into better design decisions. Something as simple as deciding whether to use a natural key or a surrogate key for a table can be important for the business; aligning your decisions with the business objectives is key to the success of your work.
Communication and Interpersonal Skills
Creating a good design is only part of the job. After creating and perfecting your model, you must be able to present it, explain it, and help anyone who needs to work with it. You must also accept every design can be improved, so be prepared to receive suggestions and criticism. In general, as a designer, you are part of a team. Being a useful part of the team means being a team player who always thinks of the benefit to the team.
The Finish Line: A Database Designer Job
If you have done all of these things, you are probably already modeling databases like a pro and have many stories to tell. The comment space below is for you to share useful advice and funny stories, and for you to contribute to enriching our lives as database designers.