Tag: Tip
Top 12 Database Design Principles in 2023
A well-designed data model must support database integrity, performance, scalability, and security. To achieve these qualities, pay attention to the top 12 database design principles explained in this guide.
Database design principles are essential to creating efficient, reliable, and scalable databases. A database created following these fundamental design principles ensures that its data will be stored in it in an organized and structured manner. It will facilitate database administration and allow users to obtain accurate results.
Top 15 Database Modeling Tips for 2021
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).
Common ER Diagram Mistakes
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.
The 9 Most Common Database Design Errors
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.
Using Vertabelo’s SQL Script Editor
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.
A Smart Way to Build Skills – An E-Learning Portal Data Model
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.
The Proper Way to Handle Multiple Time Zones in MySQL
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.
Does Evolving Contact Information Mean Changing Your Database?
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.
Five Common Dimensional Modeling Mistakes and How to Solve Them
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.
Party Relationship Pattern. How to Model Relationships
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.
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.
Database Modeling Tips
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.
Identifying the Bill of Materials (BOM) Structure in Databases
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.
Beverly Hills 90210 and ZIP+4: Handling Addresses in Data Models
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.
7 Key Things to Remember About Data Model Globalization
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.
Denormalization: When, Why, and How
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.
13 Blog Articles on Database Design Best Practices and Tips
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.
19 Online Resources for Learning About Database Design Errors
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.
Tip #24 – How to name a version of the model
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.
Tip #23 – How to create a reference to an alternate key
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.
Tip #22 – How to identify problems with my model
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.
Tip #21 – How to quickly find a table in a diagram
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.
Tip #20 – How to configure Vertabelo printouts
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.
Tip #19 – How to automatically download SQL script from Vertabelo
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.
24 Database Modeling Tips for Vertabelo
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.
Tip #17 – Drag a table from the navigation tree and drop it in your diagram to create a shortcut
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:
Tip #16 – Can I put the same table in the diagram twice?
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:
Tip #15 – How to visually group tables and organize large database models using subject areas
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:
Tip #14 – How to create a model preview link and embed the model in a website
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.
Tip #13 – How to export selected tables as an image
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:
Tip #12 – The quickest possible way to create a new column
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.
Tip #11 – Vertabelo reports that my data type is not supported. What can I do?
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.
24 Database Modeling Tips for Vertabelo
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.
Tip #9 – How to create multiple references between two tables
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.
Tip #8 – Setting Your Own Names for Primary Key
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:
Tip #7 – How to include comments in SQL script
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.
Tip #6 – How to Create a Multicolumn Primary Key
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:
Tip #5 – How to quote table names in generated SQL script
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:
Tip #4 – How to Make a Column Unique
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:
24 Database Modeling Tips for Vertabelo
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:
24 Database Modeling Tips for Vertabelo
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?
Vertabelo Advent Calendar
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.
24 Database Modeling Tips for Vertabelo
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:
Naming Conventions in Database Modeling
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.
Database Model for a Messaging System
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.
Modeling a Database for Recording Sales. Part 1
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.
Tips for Better Database Design
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.
Email Confirmation and Recovering Passwords
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.
7 Common Database Design Errors
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.
How to Convert a Data Model Between Two Database Management Systems
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?
A Database Model for an Online Survey. Part 4
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.
Basing Database Models in Reality: A Blogger’s Challenge
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.
A Database Model for an Online Survey. Part 3
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.
A Database Model for an Online Survey. Part 2
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.
How to Color ERD Diagrams in Vertabelo
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:
A Database Model for an Online Survey. Part 1
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).
5 Steps for an Effective Database Model
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.
How to Store Employees’ Schedules in a Database
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:
How Do You Make Your Database Speak Many Languages?
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?
Now You Can Print a Diagram
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).
Designing a Database: Should a Primary Key Be Natural or Surrogate?
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 :)
Database Design 101
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.
Good news: we now support views
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:
