Here are 15 simple tips that will guarantee the quality of your database model! Prior to the emergence of NoSQL databases, data modeling had followed a standard norm. However, with the hybridization of storage models and especially with the integration among them, it has become necessary to consider NoSQL databases in systems that use relational databases. This article covers 15 tips for modeling databases in 2021 (the database modeling tool used in this article is Vertabelo).
Get to know the ER (Entity Relationship) diagram, its parts, and what often goes wrong when creating it. Have you ever created a relational database model? Or maybe you're trying to create your first one? You know (or you'll soon find out) that translating real-world problems to database logic can sometimes be quite difficult. One of the tools that might help you is the ER diagram. Common database design wisdom holds that the better your ER diagram, the easier it will be to build the database model.
You’ve probably made some of these mistakes when you were starting your database design career. Maybe you’re still making them, or you’ll make some in the future. We can’t go back in time and help you undo your errors, but we can save you from some future (or present) headaches. Reading this article might save you many hours spent fixing design and code problems, so let’s dive in. I’ve split the list of errors into two main groups: those that are non-technical in nature and those that are strictly technical.
Vertabelo’s new SQL Script tool lets you write and save SQL scripts and run them against a connected database. Creating an SQL Script Start by clicking on the Create Document button. It’s the first one on the main toolbar. The New Document window appears. Now select SQL script and click on the Create button. The New SQL script window will be displayed. Name your script using the Name field and click the CREATE button.
E-learning (or online learning) offers people a flexible and cost-effective way to pick up new skills. What’s behind these popular portals? Online learning has become an attractive way for busy people to expand their educational and technical horizons. Being able to learn what you need, at your own pace, and on your own schedule is appealing. Plus, these courses are priced much lower than their traditional counterparts. Since there are no classrooms, no full-time instructors, and a high reusability factor (once a course is produced, it can be accessed by many learners), online courses are very cost-effective.
In this article, I’ll walk you through some fundamental considerations for working with date- and time-related data in MySQL. We’ll also look at how to handle multiple time zones and daylight saving time changes. Let’s first address some core concepts that will help us understand the underlying complexity of time-related data. It is important to notice that these concepts apply when representing a point in time rather than an absolute duration.
There are a number of ways to contact someone these days, right? We have various phones: mobile and landline, personal and work. We have different addresses – residential, mailing, billing, business, etc. – and likely several email addresses, too. Don’t forget Skype and various messaging apps. Now add in LinkedIn and Facebook –which by the way, both have their own messaging elements. Not that long ago, many of these didn’t exist.
When designing your dimensional model, it is worthwhile to watch out for mistakes that commonly occur during the process. Specifically, they can occur in the relationships between tables, both in fact-to-dimension and dimension-to-dimension relationships. In this post, we’re going to take a closer look at five common modeling mistakes and what you can do about them. As you start a BI-related project, bulletproof dimensional design is hugely important. What makes a design bulletproof is the early mitigation of common design mistakes.
PostgreSQL provides an activity-tracking module called the statistics collector, which tracks table access and other internal events. If your database is experiencing long wait times, you may be able to use this tool and some simple SQL to find and fix the problem. Tracking Postgres Activity With the Statistics Collector Tool The statistics collector is designed to keep records about internal activity in a Postgres database manager. It can: count table and index access in both disk-block and individual-row terms.
Relationships are everywhere: between people, between organizations, between organizations and people. Think about being an employee of a company, being a member of a project team, or being a subsidiary of another company. Is there a straightforward way to accurately model and manage all these relationships? Can we easily answer the question ‘Who knows who?’ A Quick Review of Relationships Exactly how this basic model was derived was described in my previous article, Flexible and Manageable Bill of Materials (BOM) Designs.
The bill of materials design pattern is deceptively simple, yet incredibly powerful. This article will introduce an example, familiar to IT professionals, that you may not have thought fits the BOM pattern. It will also introduce concepts to show you how to make your BOM structures more flexible and much easier to manage. A Short Recap of the BOM A bill of materials has its roots in manufacturing. It is a list of the raw materials, sub-assemblies, intermediate assemblies, sub-components, parts, and the quantities of each needed to manufacture an end product.
When you were learning database concepts, data modeling looked pretty easy, didn’t it? You knew all the rules, and modeling seemed like a game: get a challenge, do your best, and eventually solve it. Job well done! Moving up to the next level – and so on. As you continue, though, you’ll see that database modeling is also an art. Many cases require a totally new approach. Everything can be done ‘by the book’, but sometimes you get better results when you go less orthodox.
The bill of materials (BOM) design pattern is deceptively simple, yet incredibly powerful. Historically, it’s been employed to model product structures, but the pattern can be used to do much more than simply define a hierarchy. This article will introduce three very different examples to help you to recognize the pattern in your own projects. What Is a Bill of Materials, or BOM? A bill of materials has its roots in manufacturing.
In my last post, I wrote about ensuring that your data model properly handles global information: numbers, currencies, phone numbers, addresses, dates, and time zones, among other things. However, I’ve realized that many example data models have exactly the “self-centric” or “Amero-centric” approach that I cautioned against. As an American living abroad (for almost 30 years now), I often find that people make too many assumptions about the universality of what they know.
Very few database authors mention the challenges of globalization and localization in any meaningful way. There’s a similar lack of foresight from database architects. The fact is that many authors and designers are frequently very ‘self-centric’: they create (or write about) data models that only properly handle their local time zones, addresses, etc. A self-centric approach has a big problem: the resulting model will only support local data. In today’s Internet-fueled world, applications are often unexpectedly accessed by users around the globe.
As JSON continues to increase in popularity, support from third-party products is burgeoning as well. According to the Microsoft team, JSON support was one of the most requested features on Microsoft connect prior to its official announcement. While some JSON functionality is available in SQL Server 2016, significant limitations may hamper development and storage efforts. The Basics of JSON JSON is a language-independent format to store objects in attribute-value pairs.
I’ve been practicing agile database techniques for about twenty years now. My use of these techniques didn’t start as a set plan; rather, it evolved over time as I consulted on various projects. It made sense to look for ways of working faster and with greater customer interaction. I can think of at least three kinds of agile database techniques: Data modeling Data warehouse development Database reverse engineering Agile Data Modeling Many think of data modeling as a laborious task.
Databases are designed in different ways. Most of the time we can use “school examples”: normalize the database and everything will work just fine. But there are situations that will require another approach. We can remove references to gain more flexibility. But what if we have to improve performance when everything was done by the book? In that case, denormalization is a technique that we should consider. In this article, we’ll discuss the benefits and disadvantages of denormalization and what situations may warrant it.
There’s a lot to keep in mind when you’re designing a database, and very few of us can remember every valuable tip and trick we’ve learned. So, let’s take a look at some online resources that feature database design tips and best practices. As we go, I’ll share my own opinions on the ideas presented, based on my experience in database design. Obviously, this article is not an exhaustive list, but I’ve tried to review and comment on a cross section of sources.
We all make mistakes, and we can all learn from other people’s mistakes. In this post, we’ll take a look at numerous online resources for avoiding poor database design that can lead to many problems and cost both time and money. And in an upcoming article, we’ll tell you where to find tips and best practices. Database Design Errors and Mistakes to Avoid There are numerous online resources to help database designers avoid common errors and mistakes.
Vertabelo offers you a feature to assign tags to particular versions of your model. It may be used, for example, to match the versions of your database model with the versions of your application. To assign a tag to the current version of the model, click Set tag in the options menu: Enter a tag name and click Set tag. Now, this version is tagged.
Sometimes, you want the reference between two tables to reference an alternate key and not the primary key. Here's how you can do it in Vertabelo. In this example, we want to model cars and their owners using owner and car tables. The car table has an alternate key consisting of the vin column: We want the owner table to refer to the car table, but using the car table’s alternate key.
Vertabelo has a built-in mechanism of live validation that checks your model all the time and gives you hints on how to improve it. Every error, warning or tip is flagged by a dedicated icon that you cannot fail to notice. Within your workspace, the problems are indicated in two places. In the left panel you can review all problems found in your model, while on the right side you’ve got information on problems detected in the currently selected object only.
Do you know the quickest way to find a single table even in the largest diagram you can imagine? The answer is to double click the table’s name in the navigation tree! In the Model structure panel on the left, you’ve got a navigation tree with groups of all elements used in your diagram, including database objects such as tables, references and views, as well as other subsidiary elements like subject areas and text notes.
Sometimes you may want to export your database model to a PDF file, especially for documentation purposes. It often happens that your diagram is larger than the default page size set in Vertabelo. If that's the case, you should configure the printout options in the Format section. Make sure that no element in your diagram is selected. Go to the Model properties panel on the right and expand the Format section which provides some basic formatting parameters.
If you need to automatically download SQL script from Vertabelo, for example, for the purposes of your build system, you definitely should check out our Vertabelo API. It allows you to download an SQL or XML of your model by a simple HTTP request. In the command line, it may look like this: VERTABELO_API_TOKEN=put-your-api-token-here MODEL_ID=put-your-model-identifier-here curl -u $VERTABELO_API_TOKEN: \ https://my.vertabelo.com/api/sql/$MODEL_ID create-database.sql To learn more about Vertabelo API, go here.
In our previous Tip #16 and Tip #17 you could have learned that using table shortcuts makes it easier to create a well-laid-out database model. Do you know that references between shortcuts may be shown in the diagram too? Let’s go back to our example from Tip #16. Assume that we have already created all necessary subject areas. Now, we want to put shortcuts of all purchase-related elements in the Purchase subject area.
In our previous Tip #16, we showed you how to create shortcut tables using copy and paste. But did you know that you can create a shortcut of a table with only one move of your mouse? Just find the table in the navigation tree and then drag and drop it in the desired place in your diagram. Let’s assume that you want to create a reference line between two tables that are located quite far away from each other in your database diagram:
Yes, you can! Shortcuts combined with subject areas might completely change your approach to database modeling. They allow you to make your diagram much more structured, logically divided and readable. Let’s look at an example. At the beginning, we have a really standard database model for a shop. Open your database model in Vertabelo: Now, we’re going to use subject areas in the model. To start, let’s put the whole model in a subject area:
Large models often happen to be quite messy. Using subject areas, you can improve the readability of your model and make it easier to work with. The idea behind subject areas is to group tables inside them according to their purposes – it allows you to create some logical structure in your model and makes navigation through it more natural. Open your database model in Vertabelo: To create a subject area, choose Add new area from the toolbox or press 7 on your keyboard:
When it comes to sharing things like documents or photos with other people, sending files to each other is becoming less and less common – we usually prefer to send links. They are quick, convenient and neither use up disk space nor make a mess in our file system. At Vertabelo, we know how inconvenient it can be to send files and that’s why we created public links for models.
Probably you have already generated a PNG image for your model, but did you know that the image may be generated for chosen elements only? Open your database model in Vertabelo: Select the desired objects with Ctrl + Click or using Select area from the toolbox: Click Export model as PNG image in the top toolbar: And this is your PNG image:
From our previous Tip #2 you could learn how to move between columns in your table using just arrow keys. Now, we will show you how to use the Down arrow to create one or even more new columns. Probably, this is the fastest way of creating new columns in this part of the galaxy. You really have to learn this. Open your database model and select the table in which you want to create a new column.
Sometimes you may want to use a data type that is so new that Vertabelo doesn’t recognize it. In such cases, the application displays a warning that the data type is not supported. If you find these warnings too distracting, you can turn them off. In this post, we will show you how to do it. Let’s take a look at the following example. Assume that you have a database model for MySQL 5.
This one is frequently asked on our support: “In the data types panel, I can’t find the data type I need. Does this mean I cannot use it in Vertabelo?” Of course, you can. Vertabelo will never let you get stuck in a data type dead end. Even if the type you need is not listed, it doesn’t mean you cannot use it. Data types you can find under the button are just the most popular ones.
Relationships in the real world may not be as easy as we would like them to be. Sometimes we want to model a situation where one table is refering to the other more than once. Luckily, with Vertabelo, this is a piece of cake. Let’s say we want to model a rental agreement between two people: landlord and tenant. In this situation, the rental_agreement table would need to refer to the person table twice – first for the landlord and second for the tenant.
Sometimes the naming convention you use requires a specific pattern for the primary keys’ names. That’s why you may want to set your own names for each primary key in your database model instead of using default ones. Go on reading to get to know how to do this in Vertabelo. To give your own name to a primary key, select the table that contains the primary key you want to name:
You can comment on your database objects (tables, columns, views) in Vertabelo. You can also include this metadata in your database, where it can be accessed by your SQL developers or DBAs. Here’s how you can do it. Go to your database model: Click SQL generation settings in the Model properties panel on the right: Check the Include comments for database objects option: Let’s try out how it works.
Primary keys may contain more than one column. Multicolumn primary keys are frequently used for junction tables, which are used to model many-to-many relationships. Select a table: In the Table properties panel on the right, check all the columns that you want inside the key: Now, you have a multicolumn primary key created: You can check out your SQL code by clicking the SQL preview button in the top right corner:
Sometimes, you want all identifiers in your SQL script to be quoted. For example, you want them to have a particular case. Luckily, Vertabelo allows you to quote all SQL identifiers in your generated script to prevent you from any SQL name conflicts. Go to your model: Click the SQL generation settings tab in the Model properties panel on the right: Check Quote all SQL identifiers:
Sometimes there are columns in a table that don’t belong to primary key, but are still unique. To mark them as a unique, you have to create an alternate (unique) key containing it. Single-column alternate (unique) key Select the table with the column you want to make a unique. Then, click the Alternate (unique) key tab in the Table properties panel on the right: Click Add key:
Did you know that you can copy tables between models in Vertabelo? It’s useful for instance with tables that appear in almost every database model like user_account, address, client or product, or with tables that are specific for your domain. You don’t have to create all these tables from scratch. Open the model which contains the tables you want to copy and click to select them. Note that you can hold down the Ctrl key if you want to select objects located in different parts of your model:
Would you like to make your database modeling faster and more convenient? Of course you would! One way to achieve this is to learn your database modeling tool’s keyboard shortcuts. To check them when working in Vertabelo, press Ctrl + I. Our favourite Vertabelo shortcuts Down arrow in the Columns section Did you know that you can move between columns in your table using arrow keys?
From today until Christmas Eve, we’re going to publish some tips & tricks for Vertabelo. Check our blog every day for a new one! List of all available tips: How to move a column up or down Use keyboard shortcuts in Vertabelo! How to copy tables between models How to make a column unique How to quote table names in generated SQL script How to create a multicolumn primary key How to include comments in SQL script How to name a primary key How to create multiple references between two tables How to use a data type that isn’t listed in the data types panel Vertabelo reports that my data type is not supported.
Have you ever changed your mind about the order of columns in your table? You probably have! Fortunately, changing the order of columns in Vertabelo is as easy as pie. Select the table that contains the column you want to move up or down. In Table properties on the right, you can see a list of the columns. On the left of each column’s name, there is a column selector:
What’s In A Name? The Database Edition Database models require that objects be named. While several facets of naming an object deserve consideration, in this article we’ll focus on the most important one: defining a convention and sticking to it. Why Use Naming Conventions? Look at the database model below. I went a bit overboard and removed as many traces of a naming convention as I could. This proves my first point: a naming convention is an important part of a well-built data model.
People love to communicate. We often joke that any software system always evolves into a messaging system. This article will explain the system requirements and step by step approach to design a data model for a messaging system. Requirements in Nutshell The core functionality of a messaging system in an application is to send notifications/messages to a user or a set of users. Our system also allows one to send messages to a user group.
Storing sales data properly and later combining it can lead to creating a predictive model with a high rate of accuracy. In this and the next few articles we’ll analyze a database design for recording sales. Everyone lives by selling something. Robert Louis Stevenson In today’s world, selling products is ubiquitous. And salespeople who have access to robust tools that leverage historical data to analyze trends and enable an enterprise to adjust business strategies accordingly have an advantage over their competitors.
Over the years, working as a data modeler and database architect, I have noticed that there are a couple rules that should be followed during data modeling and development. Here I describe some tips in the hope that they might help you. I have listed the tips in the order that they occur during the project lifecycle rather than listing them by importance or by how common they are.
Modern applications have plenty of authentication features beside registration and login. In this article we will take a look at how to design the database for two such features: email confirmation and password recovery. Email Confirmation What Is It? Most people familiar with the Internet know what an activation email is. An activation email is sent to the user after he or she registers for an account on a website or web application and contains a link that will allow the user into the system.
Why Talk About Errors? Model Setup 1 – Using Invalid Names 2 – Insufficient Column Width 3 – Not Indexing Properly 4 – Not Considering Possible Volume or Traffic 5 – Ignoring Time Zones 6 – Missing Audit Trail 7 – Ignoring Collation Why Talk About Errors? The art of designing a good database is like swimming. It is relatively easy to start and difficult to master.
Even though Vertabelo doesn’t provide a direct conversion of a data model from one database engine to another, this can be easily done in a few simple steps. The user’s question We’ve been developing a web application for a while. Initially, we wanted to use MySQL for our database but finally we decided to choose Postgres. Unfortunately, the entire data model was created for MySQL. How can we quickly migrate it to the new database type?
Written in the frequently-asked-questions style, this book provides the best troubleshooting techniques for PostgreSQL database administrators. It covers all major aspects of managing a PostgreSQL database; from database installation through optimizing performance, to dealing with transaction locks or fixing replication, to handling hardware and software disasters. So if you’re getting an error message when working with PostgreSQL, let this book help you solve your problem. “Troubleshooting PostgreSQL” is the latest book by Hans-Jürgen Schönig, the recognized authority in the PostgreSQL community.
In this final article in a four-part series, I complete the design for an online survey database to provide flexibility for multiple surveys, question re-use, multiple choice answers, ordering of questions, conditional jumps in the survey based on responses, and control over the users’ access to surveys via groups of survey owners. Introduction In the conclusion to Part 3 of this series of articles, I mentioned that I would be adding more advanced features in this article.
When writing a blog post on database modeling, you must be prepared that your abstract model doesn’t meet the needs of most readers. The reason is simple. Real-life database models are usually created in close relation to specific business and development requirements while the blog models are not. For the last few weeks, I have been writing blog posts about creating database models. Topics ranged from a general approach to database modeling through a simple online forum to a model for a more complex online survey.
In the conclusion to Part 2 of this series of articles, I mentioned that I would be adding more advanced features, such as: Conditional ordering of questions in a survey or, in other words, the possibility for a conditional path through the survey Administration of the survey Reports and analytics In this third article related to an online survey, I will extend the functionality to support conditional ordering of questions.
Just like your house gets dirty and requires cleaning from time to time, your PostgreSQL database may accumulate “dust” too. Unwanted pieces of data make the DB grow bigger and bigger. However, there is a mechanism to get rid of junk data and the DB can do it automatically for you. How Does a Database Get “Dirty”? Let’s talk a little bit about concurrency in databases. Generally, when multiple users (or more concretely – database transactions) read or modify the data stored in a database at the same time, they may conflict with each other.
Foreign Data Wrapper (FDW) is one of the big features that came with PostgreSQL 9.1. Simply, it allows you to access external non-Postgres data as if it were a regular Postgres table. Postgres FDW is an implementation of a decade-old SQL/MED (Management of External Data) standard in PostgreSQL that contains the information on how to allow databases to make external data sources (text files, web services, etc.) look like tables and have easy access to them using SQL commands.
In part 1 of this article series, we discussed a basic design for an online survey. In the conclusion to that article, I mentioned part 2 would cover more advanced features for our survey such as: Different types of questions such as multiple choice questions Conditional order of questions in a survey or, in other words, the possibility for a conditional path through the survey Administration of the surveys Reports and analytics Let’s start by extending the functionality to support different types of questions.
If you’ve ever had to design a database model with hundreds of tables, views and references, you know very well how difficult is to make such huge diagram readable and comprehensible. One of the possible solutions is to use some colors to distinguish different groups of tables or subject areas. See how Vertabelo allows you to do this quick and easy. To illustrate how you can color your entity relationship diagram to increase its readability, we will use a sample uncolored database model for a simple online store:
I need to create the design for a new database which will be the data layer for an application; the application will be an online survey or polling like Survey Monkey. My challenge is that the functionality that I require is not supported by existing survey sites, so I need to build my own. What I need is a conditional survey (if the answer to question 4 is “yes,” then we ask question 5 and skip question 6; but if the answer to question 4 is “no,” then we skip question 5 and ask question 6).
Database design is the process of producing a detailed model of a database. This model contains the necessary logical (table names, column names) and physical (column datatypes, foreign keys) choices to translate the design into a data definition language (aka SQL), which can be used to create the actual physical database. When I need to create the design for a new database, in other words, the data layer for an application, I follow a few mental steps that I think can help others when they need to go through the same process.
Level: Beginner So many organizations face the common problem of storing employee schedules. No matter what institution: a company, a university or simply an individual, many entities need an application to view schedules. Therefore, I will try to come up with a database model and then, in a future article, we’ll talk about a simple application to store employees’ schedules in a database. Currently the design looks as follows:
The Scenario You are the owner of an online store, located in Poland. The majority of your customers are from Poland and they speak Polish. But you want to sell your products abroad too and your international customers mainly speak English. So you want your online store to be available in both Polish and English. You also expect that your products will sell well in France, so you anticipate that you’ll have to prepare a French version of the store as well (and maybe Spanish too, because why not?
We hadn’t planned on adding a feature to print diagrams. Our original idea was that Vertabelo would supersede paper diagrams. Nevertheless, some of our users pointed out that they rely heavily on printouts (Export to PDF topic). So, I’d like to announce a new feature called “Export to PDF” also known as “Printing.” Note that exporting a database model to a PDF file is available for Premium and Team account plans only (see the comparison of the plans’ features in our Pricing).
The concept of materialized views (MVs) is almost 15 years old; Oracle first introduced these views in the 8i version of its DBMS. However, some well known DB vendors (like MySQL) still don’t support MVs or have added this functionality only quite recently (it’s available in PostgreSQL since version 9.3, which was released just a year ago). In this article I’ll try to give you some tips about when you should use MVs in OLTP systems.
I'd like to write something about the Vertabelo user interface. Don't really know what to cover in this article. A user manual for a rich GUI application is an oxymoron to me. No one reads it and no one should write it. On the other hand, it is too early to write about the libraries that we used. The time is needed to sum up the experience. So, I gotta share with you some inspirations how they affect Vertabelo.
Suppose we design a database. We’ve created some tables, each one has a few columns. Now we need to choose columns to be primary keys (PK) and make references between tables. And here some inexperienced designers face the dilemma – should a primary key be natural or surrogate? There’s one and only one answer to that question: it depends. If anyone ever tried to convince you that you should have only natural keys or only surrogate keys, just smile :)
Recently I was given a task which involved counting, with a single SQL query, all tables in each model version of Vertabelo. Vertabelo internally stores each model version as an XML file (download a sample XML file). So my task was to count all XML nodes satisfying a certain XPATH expression. Vertabelo runs on PostgreSQL 9.1, so PostgreSQL 9.1 tools were all I could use. To begin at the end, the final query looks like this:
A good data modeling exercise for beginners is to create a data model of an online store. Every time I give this exercise to my students, I’m surprised at how difficult it is for them. Find the Concepts... Let’s see how it can be done. We know we have to create a table for every concept in the domain. Think about the nouns and noun phrases you would use to describe the domain.
I like Oracle database. It is efficient, easy to use for beginners and professionals – its tools for query analysis and optimization are masterpieces. But it has a very annoying “feature” – empty text (containing 0 characters) is stored in the database as null. Where did such a feature come from? Probably from the ancient ages, just after the dawn of time, i.e., the late 70's . In that age, memory (RAM and disks) was very limited and system designers did their best to use as little memory as possible.
Today we are happy to announce that Vertabelo has a new feature we've been working on for some time – views. Now you can easily add them to the diagram, change their definition and other properties and have them generated in your SQL scripts. Quick overview of views Let's take a look at how simple it is to create a new view in our editor. Our goal is to make a view which joins three tables as follows:
Recently a fellow database architect claimed that in Oracle the type VARCHAR2(255) means a string of 255 bytes, not characters. There is not much difference between the two in the English-speaking world. It matters though if you want to handle people with names like Kołłątaj. (Not that Hugo Kołłątaj – a famous Polish 18th century politician – would ever use any of our systems, but he became our byword for all non-pure-ASCII names).
The question: Why doesn’t my script create tables? The other day I was testing Oracle SQL scripts generated by Vertabelo. Roughly, this is the code that was generated: ... -- Table: book CREATE TABLE book ( id integer NOT NULL, title varchar2(120) NOT NULL, isbn varchar2(15) NOT NULL, PRIMARY KEY (id) ) ; ... I used sqlplus to execute my script and see if it’s correct. sqlplus (database-details) The script run without errors but the tables where NOT created.