Facts and dimensions are the fundamental elements that define a data warehouse. They record relevant events of a subject or functional area (facts) and the characteristics that define them (dimensions). Data warehouses are data storage and retrieval systems (i.e., databases) specifically designed to support business intelligence (BI) and OLAP (online analytical processing) activities. They are different from databases designed to support transactional systems – e.g., e-commerce sites – whose function is primarily OLTP (online transactional processing).
Most of the best books on data modeling were originally written decades ago. But the concepts they discuss are more relevant than ever; in today’s business world, most decisions are made on the basis of well-modeled data repositories. With the rise of NoSQL databases, unstructured information, and Big Data repositories (where quantity seems to be more important than quality), you might think that data modeling is an archaic discipline. That notion can be maintained until inconsistent data, anomalies, and inaccurate reports start to pop up everywhere and you begin losing confidence in your data.
Sometimes your data models speak for themselves. But for those who don’t understand their language, you need to add clarifications that explain the reasons behind your design decisions. You may think that your database designs are works of art and therefore need no explanation. But the problem with works of art is that each person who looks at them can interpret them differently. This is fine from an artistic point of view, but it doesn’t work in day-to-day database work.
Don’t put off until tomorrow what you can model today. Every minute you spend building and keeping your data model up to date means hours saved in the future. In my early days as a database designer, I thought data modeling was an unnecessary step that only delayed the time to get the databases up and running. I thought that because, in most cases, databases started with just a few entities – 6 or 7, tops.
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.
Thinking of a database design for audit logging? Remember what happened to Hansel and Gretel: they thought leaving a simple trail of breadcrumbs was a good way to trace their steps. When we design a data model, we are trained to apply the philosophy that now is all that exists. For example, if we design a schema to store prices for a product catalog, we may think that the database only needs to tell us the price of each product at the present moment.
Think of them as your sidekicks to deal with the challenges you encounter every day in working with databases. Good database tools come to the rescue in all stages of the database lifecycle: from the conceptual design, through logical/physical design, all the way to maintenance, refactoring, and optimization. Every database professional, be it an architect, engineer, designer, programmer, tester, or administrator, has a preferred set of tools. Some tools are specific to the database system (RDBMS), such as MySQL, Oracle, SQL Server, or PostgreSQL, while others work with virtually any database engine.
Sharing is a good habit. The guys at Vertabelo know this, so they have made a big effort to make sharing database models with your clients very easy. True story: there was a time in my life when, in order to show a data model to a client, I had to print it on several sheets of paper. Then – using scissors, tape, and a fair amount of manual dexterity – I had to compose it into a sort of poster that would allow me to display all of its details.
Feeling overwhelmed by the amount of time it will take you to learn to be a database designer? Read about the essential skills and talents you’ll need – it’s not so terrible! When you walk down the aisles of the supermarket, shopping cart in one hand and grocery list in the other, what are you thinking? If you're like me, you're imagining how to improve the organization of the shelves so that your weekly shopping is less time-consuming.
Find out who’s who in the database department and decide which role you most identify with. In small companies, there is usually only one database job. The person in that position may be an architect one day, a designer the next day, a programmer another day, an administrator the day after that, and sometimes even an analyst or even a data scientist. If you’re planning to work in a small company, you should get used to the idea that you’ll be known as “the database guy/gal” and you’ll have to do a little bit of everything.