Tag: tip

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 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.

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. So you can pretty much guarantee that in a few years, we’re going to have some new way of contacting people and organizations.

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

Postresql Monitoring with SQL

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.

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 RelationshipsExactly 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 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.

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?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.

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. 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.

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. 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.

Support, Functionality, and Limitations of JSON in SQL Server 2016

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.

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. Hopefully, you’ll find the information that best suits your needs and goals.

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 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.

Tip #24 – How to name a version of the model

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

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 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

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 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.

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 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:

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 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 .

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 thePurchasesubject area.First, select all relevant tables (use

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

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 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:

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 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

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.5. Note that one of the columns uses the JSON data type which has only been introduced in

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

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, 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:

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: 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

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 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:

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 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

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) 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:

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 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:

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 , 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?

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

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. 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

Naming Conventions in Database Modeling

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

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 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.

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 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.

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.1. Plan Ahead

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 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

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. 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.

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 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?

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.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:

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

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 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 Wrappers for PostgreSQL

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.

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 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.

A Database Model for an Online Survey. Part 1

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).

5 Steps for an Effective Database Model

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.

How to Store Employees’ Schedules in a Database

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

How Do You Make Your Database Speak Many Languages?

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

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

Can SQL Help Solve Crossword Puzzles?

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 of Materialized Views (MVs)

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.

Some bits about the Vertabelo user interface

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.

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 :)

How to Count XML Nodes in PostgreSQL 9.1

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:

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 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

Pitfall in Oracle Database: An Empty Text Stored as Null

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 [1] . 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,

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 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:

Oracle VARCHAR2 Column Size

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.

Why Doesn’t SQL*Plus Execute My Script?

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?