Vertabelo’s new SQL Script tool lets you write and save SQL scripts and run them against a connected database.Creating an SQL ScriptStart by clicking on theCreate Documentbutton. It’s the first one on the main toolbar.TheNew Documentwindow appears. Now selectSQL scriptand click on theCreatebutton.TheNew SQL scriptwindow will be displayed.Name your script using theNamefield and click theCREATEbutton.Your new script will be opened in the workspace as empty file. The name of your script is visible in the top left corner of the toolbar. You can see a list of all your scripts by clicking on the drop-down menu arrow to the right of the current script name.
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. So you can pretty much guarantee that in a few years, we’re going to have some new way of contacting people and organizations.
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
PostgreSQL provides an activity-tracking module called thestatistics 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 ToolThe 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 RelationshipsExactly how this basic model was derived was described in my previous article,
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 BOMAbill of materialshas 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?Abill of materialshas 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. You can look at it as a hierarchical decomposition of a product. Other terms for the same thing are product structure, bill of material, and associated list.
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. For example, some Americans assume that others automatically understand their country’s ZIP code system, and its supplementary ZIP+4 version. In my experience, most of the world has no idea what a ZIP+4 is.
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. We need to support as much flexibility as possible for this international audience. Therefore, we need to design our data models with a globalized approach.
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 JSONJSON is a language-independent format to store objects in attribute-value pairs. Due to its compactness and flexibility, it is increasingly replacing XML. Its simplicity makes it superior to XML as a data-interchange format; although JSON lacks the structured overhead of XML, that feature is unimportant for data interchange.
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. Hopefully, you’ll find the information that best suits your needs and goals.
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 AvoidThere are numerous online resources to help database designers avoid common errors and mistakes. Obviously, this article is not an exhaustive list of every article out there. Instead, we’ve reviewed and commented on a variety of different sources so that you can find the one that best suits you.
Vertabelo offers you a feature to assigntagsto 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, clickSet tagin the options menu: Enter a tag name and clickSet tag. Now, this version is tagged. You can see this in theModel versionssection in the
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 usingownerandcartables. Thecartable has an alternate key consisting of thevincolumn: We want theownertable to refer to thecartable, but using the car table’s alternate key. Let’s start with a typical reference by switching to a reference in the toolbox and putting a line from the
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 theModel structurepanel 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 theFormatsection.Make sure that no element in your diagram is selected. Go to theModel propertiespanel on the right and expand theFormatsection which provides some basic formatting parameters. Here you can configure the page size, orientation or margins of your printout:
If you need to automatically download SQL script from Vertabelo , for example, for the purposes of your build system, you definitely should check out ourVertabelo 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:To learn more aboutVertabelo 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 thePurchasesubject area.First, select all relevant tables (use
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
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 withCtrl + Clickor usingSelect area from the toolbox: ClickExport model as PNG imagein the top toolbar: And this is your PNG image: What’s more, if you select a subject area, everything inside it will be included in the image. Open a model containing a subject area:
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 theDown arrowto 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. Go to the
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.5. Note that one of the columns uses the JSON data type which has only been introduced in
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
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, therental_agreementtable would need to refer to thepersontable twice – first for the landlord and second for the tenant. Your initial model could look as follows:
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: ClickSQL generation settingsin theModel propertiespanel on the right: Check theInclude comments for database objectsoption: Let’s try out how it works.Assume that you want to add your comment to the
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 theTable propertiespanel 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 theSQL previewbutton 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 theSQL generation settingstab in theModel propertiespanel on the right: CheckQuote all SQL identifiers: If you want to check out the result, select any table and click the
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) keySelect the table with the column you want to make a unique. Then, click theAlternate (unique) keytab in theTable propertiespanel on the right: ClickAdd key: Click thedown arrowto show the properties: Find the column you want to make a unique and add it to the 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 likeuser_account,address,clientorproduct, 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 theCtrlkey 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 , pressCtrl + I. Our favourite Vertabelo shortcutsDown arrow in the Columns sectionDid 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
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. InTable propertieson the right, you can see a list of the columns. On the left of each column’s name, there is acolumn selector: Find the column you want to move, click on its
What’s In A Name? The Database EditionDatabase 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 NutshellThe core functionality of a messaging system in an application is tosend notifications/messagesto a user or a set of users. Our system also allows one to send messages to a user group. User groups can obviously be formed on some parameters like access privileges, geographical location of users, etc.
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 StevensonIn today’s world,selling productsis 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. There are lots of parameters that can affect company results: the current global economic situation, clients’ location, age, material and marital status, and history of previous contacts or sales to clients.
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.1. Plan Ahead
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 ConfirmationWhat 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. The email is primarily used to ensure that the email address provided by the user during registration is
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. If you want to learn to design databases, you should for sure have some theoretic background, like knowledge about database normal forms and transaction isolation levels. But you should also practice as much as possible, because the sad truth is that we learn most… by making errors.
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 questionWe’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? Is there a simple way to do this in Vertabelo?
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.IntroductionIn the conclusion to Part 3 of this series of articles, I mentioned that I would be adding more advanced features in this article. Those advanced features are:
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
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 questionsin a survey or, in other words, the possibility for a conditional path through the surveyAdministrationof the surveyReportsandanalyticsIn this third article related to anonline survey, I will extend the functionality to support conditional ordering of questions.In the future, we may add questions that require a rated response. For example: “How much do you like database design, rate between 1 and 100 (with 1 indicating that you like it very little and 100 indicating that you like it immensely)?”
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 asmultiple choice questionsConditional order of questionsin a survey or, in other words, the possibility for a conditional path through the surveyAdministrationof the surveysReportsandanalyticsLet’s start by extending the functionality to support different types of questions.
I need to create the design for a new database which will be thedata layerfor an application; the application will be an onlinesurveyor 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 detailedmodelof 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, thedata layerfor an application, I follow a fewmentalsteps that I think can help others when they need to go through the same process. And, to be honest, for me, I progress through the first steps mentally without actually working on the technical details – and sometimes at a more subconscious level.
Level:BeginnerSo 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 model is pretty straightforward.Tip – Natural and Surrogate Key Strategies
The ScenarioYou 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 bothPolishandEnglish. You also expect that your products will sell well in France, so you anticipate that you’ll have to prepare a
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
Everyone has solvedcrossword puzzlesand has certainly had some problems finding an appropriate word. Thanks to SQL, it is ridiculously simple to quickly dispel your crossword doubts and give you the correct answers. Of course, Google is commonly known as a universal cure for many doubts, but handling the problem yourself is much more rewarding.Recently I came across some simple and interesting examples from Andrew Cumming’s book “SQL Hacks.” These examples won’t make a huge impression on those who are programming experts, but less-experienced code wranglers could find them interesting. In this article I will try to present a funny approach to solving casual problems using the power of SQL according to Andrew Cumming’s book.
The concept ofmaterialized 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 thenouns and noun phrases you would use to describe the domain. Roughly, every noun iseither a concept, an attribute of a concept, or an example
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. In old versions of Oracle DB,
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 viewsLet’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 typeVARCHAR2(255)means a string of 255bytes, 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). I was very surprised by what the architect said and I decided to further investigate the matter.
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:I used sqlplus to execute my script and see if it’s correct.The script run without errors but the tables where NOT created.I copied and pasted part of the script into a new file:I executed it in sqlplus and this time the table was created.OK, I scratched my head. What is the difference between the two? The only real difference is the blank line at the end of the command. Could it REALLY be the reason?