Tag: database

A Data Model for Online Concert Ticket Sales

What kind of data model makes online concert ticketing work? Have you ever been to a concert? I'm sure you have. And I bet you purchased your ticket online. Online shops let us buy tickets to concerts by our favorite artists without leaving our comfortable homes. We can find a venue or a location (even one that’s not near us), choose a seat, and get our tickets delivered instantly through email – or maybe we’ll wait several days and get the tickets delivered.

A Data Model for an Online Musical Equipment Shop

What kind of data model supports an online musical equipment store?Musical equipment shops used to be one of those old-fashioned industries, one that you wouldn’t expect could (or should) adapt to the online world. It wasn’t unreasonable to paint such a picture. After all, you can’t choose your ideal guitar based solely on magazine pictures, can you? Musicians and wannabe-musicians need to try various instruments, hearing and feeling how each one responds when played.

An Antique Store Data Model

You might run an antique store because you love history, but you’ll need modern technology to keep everything organized! In this article, we discuss what an antique store’s database model would need.Antiques are cool. I bet we all love history and antiques in some way. Some admire Victorian furniture, others are thrilled about old coins, vintage weapons, or medieval armor. Still others enjoy reading or just looking at old books or manuscripts. There are still many unsolved antique-related mysteries around the world, such as the Antikythera mechanism or the Voynich Manuscript.

A Database Model for Gallery or Museum Management

What kind of database model does it take to run a gallery or museum? How can it be optimized to manage events, partnerships, and other activities?When I think of a gallery or museum, I usually think of a peaceful place where you can hang out for hours, looking at interesting or beautiful things. Personally, I enjoy visiting the Technical Museum in Zagreb, which has cool exhibits like old cars, fire trucks, submarines, trams, and trains.

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 whenrepresenting a point in timerather than an absolute duration. Introduction

A Project Management Data Model

Project management is a booming field. In this article, we’ll examine a data model to support a project management app.Project management is anything but an easy task. You are limited in many ways – materials, costs, human resources, and project deadlines spring to mind – but it’s still up to you to deliver a result on time.If you think of building a pyramid, you can easily conclude it was a case of project management! It had a project sponsor (usually Pharaoh), a deadline (Pharaohs’ deadline ☺), human resources (mostly workers and slaves), material resources (stone blocks) and of course a project manager. A lot has changed since then, but the idea is still the same. We need to be as organized as possible if we expect to deliver a project result on time, up to its expected quality, and within its budget.

Supporting R in SQL Server 2016

SQL Server R Services combine the power and flexibility of the open-source R language with enterprise-level tools for data storage and management, workflow development, and reporting and visualization. This article introduces SQL R Services and the R language.What Is R and How Is It Used?R is the most widely used language for statistics, data mining, and machine learning. (R is also the name of the environment and the engine that executes code written in the R language.) You need to learn how to develop R programs like you need to learn any other programming language.

A Database Model for Action Games

In previous articles, we’ve discussed data model that could run card games , board games and even MMO games . We’ve also developed a simple tic-tac-toe game using Spring Boot and AngularJS. In this article we’ll move to the next level and develop a model that can store results of an “action” game.Let’s get started!Before We Start ModelingBefore we get into designing the database, we need to know some background on the type of games we’re dealing with and what they will require.

JSON and PostgreSQL: A Powerful Combination

JSON is a data interchange format that is designed to be lightweight and easy to work with. It’s quite popular in web applications, and it can be considerably more flexible than a traditional relational data model. PostgreSQL 9.3 and later versions support JSON, so you can store JSON data and use native Postgres functions to operate on it. This includes decomposing, transforming, or even creating JSON data from regular relational data.

Again and Again! Managing Recurring Events In a Data Model

A recurring event, by definition, is an event that recurs at an interval; it’s also called a periodic event. There are many applications which allow their users to setup recurring events. How does a database system manage recurring events? In this article, we’ll explore one way that they are handled.Recurrence is not easy for applications to deal with. It can become a hurricane task, especially when it comes to covering every possible recurring scenario – including creating bi-weekly or quarterly events or allowing the rescheduling of all future event instances.

MySQL Date Format: What Datatype Should You Use? We Compare Datetime, Timestamp and INT.

Whenever you need to save datetime data, a question arises about what MySQL type to use. Do you go with a native MySQL DATE type or use an INT field to store date and time info as a plain number?In this article, I’ll explain MySQL’s native options and give you a comparison table of the most common datatypes. We’ll also benchmark some typical queries and reach some conclusions about which datatype to use in a given situation.

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

Facts about Facts: Organizing Fact Tables in Data Warehouse Systems

The process of defining your data warehousing system (DWH) has started. You’ve outlined the relevant dimension tables, which tie to the business requirements. These tables definewhatwe weigh, observe and scale. Now we need to definehowwe measure.Fact tables are where we store these measurements. They hold business data that can be aggregated across dimension combinations. But the fact is that fact tables are not so easily described – they have flavors of their own. In this article, we’ll answer some basic questions about fact tables, and examine the pros and cons of each type.

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.

Looking for a Simple Full-Text Search?<br /> Try MySQL InnoDB + CakePHP with Word Stemming

Implementing a user-friendly search can be tricky, but it can also be done very efficiently. How do I know this? Not long ago, I needed to implement a search engine on a mobile app. The app was built on the Ionic framework and would connect to a CakePHP 2 backend. The idea was to display results as the user was typing. There were several options for this, but not all of them met my project’s requirements.

Business Logic in the Database. Yes or No? It Depends!

We’ve had tremendously positive feedback on my recent article that talked about “Why SQL is neither legacy, nor low-level, nor difficult, nor the wrong place for (business) data logic, but simply awesome” both within the blog’s comment section as well as on reddit .However, one of the sections triggered very controversial feedback. Clearly, not everyone agreed to:Fallacy #5: The database is the wrong place for business logicNow this is really the biggest of all fallacies. Who said that? Some guy from the 90s who had written a book on Object-Oriented Design Patterns? Of course, if you blindly follow random architecture rules, this may apply to you. But then, beware that you’re following 10-20 year-old “legacy” principles by not using SQL (see also

Using Different MySQL Storage Engines in Database Design

Any database architect designing a MySQL database faces the issue of selecting the proper storage engine. Usually, an application uses only one engine:MyISAMorInnoDB. But let’s try to be a little more flexible and imagine how different storage engines can be used.The Initial Data ModelTo begin, let’s build a simplified data model for a CRM (customer relationship management) system that we’ll use to illustrate the point. The design will cover the main CRM functions: sales data, product definitions, and information for analytics. It won’t contain details typically used in CRM systems.

Database Modeling Course (2)

You’re finally ready to get down to real data modeling. We’ll start off with entities and their attributes. Entities are the basic building block of every data model. In this post, you’ll find out what they are and how to identify them.What Is an Entity? What is a Specific Instance of an Entity?Data models help us to identify what kind of information we’ll store in our system. We use such models to address the question

Upcoming Database Conferences: Winter 2015-2016

Once again, it’s time for a roundup of upcoming database events. Today, however, we’ll be covering events for the winter season (December 2015 to the end of February 2016) instead of our usual monthly schedule. Why are we changing it up? The holidays, for a start. Plus, organizers tend to schedule events during better traveling weather.Weather and seasonal festivities aside, the conferences are looking interesting. Berlin and Texas will go in-depth on the topic of machine learning and data science, so those interested in smart data should take note. Los Angeles and Sydney will also be hosting their own tech-centric conventions. For a more comprehensive list of software-related events, check out

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

Building an Information Mart With Your Data Vault

In the 3rdpost in this series, we looked at how we prepare data for use with a concept called the Business Data Vault. Now, in this final part, I will show you the basics of how we project the Business Vault and Raw DV tables into star schemas which form the basis for our Information Marts.Raw Data Mart vs. Information MartsAs of Data Vault 2.0, the terminology changed a bit to be more precise. With DV 2.0 we now speak of “

Creating Tables for Products and Services

Buying books was a way anyone could acquire a work of art for very little.Solomon “Sol” LeWitt,American artist,1928–2007Selling products and services can be two very different propositions. This originates in their differing definitions and real-world representations. Previously in this series, we discussed the table basics in the context of database design and sales. In this post, we’ll analyze the differences between products and services, how they impact the database model, and how we can accommodate both on one database.

Interesting Changes in MySQL 5.7

The General Availability version of MySQL is still version 5.6, but the development release of MySQL 5.7 definitely introduces some exciting changes to the world of database management systems. Is it worth giving a try? In this article, we’ll have a closer look at a few brand-new features that may help you decide to do so.Native JSON SupportJSON (short for Java Script Object Notation) is a format for storing information which can be a good alternative to XML. If you haven’t encountered it already, take a look at a very simple example of a JSON document:

The Business Data Vault

In my last post , we looked at the basics of modeling your data warehouse using the Data Vault 2.0 technique. This time, we get into the details of how we prepare the DV tables for business user access.What is a Business Data Vault?If you have done any investigation into Data Vault on various blogs or the LinkedIn discussion group, you have seen a few terms used that often cause confusion. These terms include:

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.

Applying Simple Access Control to a Small Data Model

“Information is the lifeblood of any organization…” We hear a lot of statements like this, or about an “information age,” or an “information economy.” When we agree with belief that amplifies the importance of information in the world today, we have to consider how to make that all-important information secure. Who can see my bank account? Was the facilities maintenance contract lost? Why can’t I get the latest lab report? The database professional has such concerns magnified by the thousands or millions, and so modeling for data security is critical. Here we look at some issues in securing and controlling access to information stored in a database.

Data Vault 2.0 Modeling Basics

In my last post , we looked at the need for an Agile Data Engineering solution, issues with some of the current data warehouse modeling approaches, the history of data modeling in general, and Data Vault specifically. This time we get into the technical details of what the Data Vault Model looks like and how you build one.For my examples I will be using a simplyHuman Resources (HR)type model that most people should relate to (even if you have never worked with an HR model). In this post I will walk through how you get from the

Upcoming database conferences – November 2015

As per every month, here’s the database modeling, design and administration events for November. This upcoming month will feature four big database conferences. Interestingly enough, three of them, in Germany, Brazil and San Francisco, will be focusing on PostgreSQL, so this is a good month of events for Postgres developers. The fourth one,All Your Basein London, will be a fairly encompassing one, focusing not only on the software challenges and solutions but also the human elements when it comes to working with databases.

Agile Modeling: Not an Option Anymore

The world is changing.No –the world as we knew it in IThaschanged.Big Data & Agile are hot topics.But companies still need tocollect,report, andanalyzetheir data. Usually this requires some form of data warehousing or business intelligence system. So how do we do that in the modern IT landscape in a way that allows us to beagileand either deal directly or indirectly with unstructured and semi structured data?First off, we need to change our evil ways – we can no longer afford to take years to deliver data to the business. We cannot spend months doing detailed analysis to develop use cases and detailed specification documents. Then spend months building enterprise-scale data models only to deploy them and find out the source systems changed and the models have no place to hold the now-relevant data critical to business success.

Database Modeling Course (1)

Data modeling is an essential step in the process of creating any complex software. It helps developers understand the domain and organize their work accordingly. In this article, which begins a new series devoted to database modeling, we’ll try to convince you why you should include it in your projects and what it looks like.Do I Really Need Data Modeling?As a novice developer, you often start your programming adventure withsimpleapplications like the sieve of Eratosthenes or enumerating the Fibonacci sequence. In such cases, a data model is not required – these programs are quite simple and they have a clearly defined input and clearly defined output. The difficulty of these tasks lies in the algorithm which has to work correctly and quickly. The data themselves are easy to understand: you provide some parameters and you get a set of numbers as a result.

Upcoming database conferences – October 2015

For almost any software developer, database designer, or otherwise, events and conventions signify a variety of opportunities. Whether it’s keeping up with the latest patches and functions of a programming language or seeing how other developers handle the sort of problems you deal with, or maybe making business contacts or just meeting like-minded people, there’s a lot to gain from attending the right events.In this article we’re going to mention some of the most important worldwide events taking place in the upcoming month which focus specifically on database modelling, design and administration.

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

What Is the Actual Definition of First Normal Form (1NF)?

The First Normal Form (1NF)is exceptional. The other normal forms (2NF, 3NF, BCNF) talk about functional dependencies and 1NF has nothing to do with functional dependencies. Moreover, we have precise definitions for other normal forms and there is no generally accepted definition of 1NF.Does 1NF Equate to “Atomic Attributes”?When you look at various descriptions of 1NF the word that you see most often isatomic. It is common to say that a relation is in 1NF if all its attributes are atomic. A good, theory oriented book by C.J. Date (

Tackling Your Troubles – Building a Bug and Problem Database

Death and taxes – add “software problems” to that list of the inevitable. There is always a new issue, a new failure, a new key opportunity that an organization must address. And to avoid repeating the problems, or to revise your prior fixes, it is critical to capture the problems accurately and completely. You need a history of what happened and when. In this piece, we create the logical model for a problem or “bug” reporting system.

Upcoming database conferences – September 2015

For almost any software developer, database designers or otherwise, events and conventions signify a variety of opportunities. Whether it’s keeping up with the latest patches and functions of a language or seeing how others handle the sort of problems they deal with, or maybe making business connections or just meeting like-minded people, there’s a lot to gain from assisting the right events.In this article we’re going to mention some of the most important, worldwide events of the upcoming month which focus specifically on database modelling, design and administration.

Database Model for a Driving School’s Reservation System. Part 2

Let’s build further changes into the data model, which I created in my earlier blog post , such as having an automated approach to assigning an instructor and vehicle to a lesson, invoicing to customers and tracking of them.First off, I need to build logic on the application side to assign an instructor and vehicle to lessons before they actually take place. The main thing to ensure here is availability, i.e. an instructor or vehicle can be assigned to a lesson only if both of them are available on the scheduled time of the lesson.

How to Keep Track of What the Users Do

In several of the projects we have worked on, customers have asked us to log more user actions in the database. They want to know all of the actions the users perform in the application, but capturing and recording all human interactions can be challenging. We had to log all modifications of data performed via the system. This article describes some of the pitfalls we encountered and the approaches that we used to overcome them.

Analysing the SQL Server Numeric Data Types

A common challenge for database modellers is deciding which data type is the best fit for a particular column. It is a problem which involves consideration of both the properties and the scale of the data that will be stored, and in no case is this more evident than when handling numeric values because of the large variety of alternatives that most relational databases provide for their storage.In this article we’ll explore the numeric data types available in SQL Server and analyse their advantages, disadvantages and general usage.

Designing a Data Model for a Hotel Room Booking System

It’s common knowledge that the best way to learn something is to practice it in a real-life scenario. Obviously, the same applies to database modeling. Therefore, in this article I decided to teach you how to create a simple database structure, taking a textbook example of a hotel room reservation system. I will show you how to get started and give you some ideas for extending the model.Database Modeling: Discover, Discover, Discover

ColumnStore Indexes in MS SQL Server

Introduced in SQL 2012, ColumnStore indexes differ greatly from standard row-based indexes. Intended for OLAP systems, these indexes store data in a highly compressed, segmented fashion with the column as the basis (rather than typical row-based indexes). This type of column-based index allows for great performance gains in data warehouses where table scans, rather than seeks, are performed.ColumnStore indexes have evolved significantly over the last few SQL Server versions:

Database Model for a Driving School’s Reservation System. Part 1

I need to design a data model for a reservation system for a driving school. The subject area looks quite straightforward, but complexities are still involved. You have to track all requests from clients and keep track of resources (vehicle, time and instructor) consumed during lessons.IntroductionI like to use adomain driven approachfor designing a data model. It makes me put technology obsession aside and concentrate primarily on modeling the subject area revolving around its associated entities and relationships amongst themselves.

Upcoming database conferences – August 2015

For almost any software developer, database designers or otherwise, events and conventions signify a variety of opportunities. Whether it’s keeping up with the latest patches and functions of a language or seeing how others handle the sort of problems they deal with, or maybe making business connections or just meeting like-minded people, there’s a lot to gain from assisting the right events.In this article we’re going to mention some of the most important, worldwide events of the upcoming month which focus specifically on database modelling, design and administration.

How to Design a Database Model for a Movie Theater Reservation System

Do you like going to the movies? Have you ever considered what the database design behind their reservation system looks like? In this article we’ll prepare an example database model for a movie theater.There are a few assumptions we have to bear in mind:contemporary multiplex movie theaters can have one or more auditoriums within a larger complex,each auditorium can have a different number of seats,seats are numerated with row number and seat position within a row,

SQL Server – Indexing Strategies: Clustered, NonClustered, and Filtered Indexes

This article reviews optimal placement of clustered and nonclustered indexes on OLTP databases, and explains how filtered indexes can be used to improve performance.Clustered IndexesBy default, SQL Server will create the table’s clustered index during the creation of the primary key:This can be overridden by specifying the NONCLUSTERED keyword during creation:The HEAP exists because the table does not have a clustered index defined.It’s a common misconception that the primary key and clustered index are bound together; the primary key is a special type of unique constraint (it does not allow NULL values, while normal unique constraints do). The Clustered index is a strictly-ordered B-tree intended to maximize scan performance. Only one clustered index per table is permitted.

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.

Adding More Advanced Features Like Managing Categories and Voting on Threads and Posts

In my second article about an online forum, I mentioned that there might be several more advanced features to be added:Forumcategoriesand sub-categories where each category has a subject, several moderators and additional information like creation date of the category.Apostmight have asubjectin addition to the content.We might want to allow users tovote upandvote downon threads and posts.To be able to grasp the model more easily, we have prepared an example of such a forum with categories, threads, posts, etc. We hope that this makes it easier to understand things:

How to Store Authentication Data in a Database. Part 1

How difficult is it to program a user login function for an application? Novice developers think it’s very easy. Experienced developers know better: it is the mostsensitiveprocess in your application. Errors in login screens can lead to serious security issues. In this article we take a look at how to store authentication data in your database.The most common way to authenticate users nowadays is withuser nameandpassword. How do you store this information in a database? Obviously, you should store login info in one table. The name of the table is up to you. My advice is to

Database Design: More Than Just an ERD

Database design is the process of producing a detailedmodelof a database. The start of data modelling is to grasp the business area and functionality being developed.Before Modeling: Talk to the Business PeopleThis is a key principle in information technology. We must remember that we provide a service and mustdeliver value to the business. In data modeling that means solving a business problem from the data-side such that the required data is available in a responsive and secure way.

Making a More Advanced Model With User, Thread, and Post Statuses

In my first article about an online forum, I mentioned that there might be several more advanced features to add:Moreformal details about the userinstead of a single “name” field. You may want the user’s first name, last name and username or nickname. A nice forum would also allow users to have a profile picture, email, roles, status (to allow users to be blocked), and other information like when they last visited the forum.

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?

Data Modeling in Agile Development: One Data Modeler’s Experience

Data modeling or database design is the process of producing a detailedmodelof a database. The start of data modeling is to grasp the business area and functionality being developed. When we work with an Agile process (in this case, Scrum), there is a tendency to assume that everyone can work with everything. However, I would like to point out flaws in that idea and my recommendations related to data modeling and Scrum.

A Handy Guide to Solving PostgreSQL Problems

Written in the frequently-asked-questions style, this book provides the best troubleshooting techniques for PostgreSQL database administrators. It covers all major aspects of managing a PostgreSQL database; from database installation through optimizing performance, to dealing with transaction locks or fixing replication, to handling hardware and software disasters. So if you’re getting an error message when working with PostgreSQL, let this book help you solve your problem. “Troubleshooting PostgreSQL” is the latest book by

Spider Schema – a Bridge Between OLTP and OLAP?

IntroductionAs I mentioned in my article “OLAP for OLTP practitioners” , I am working on a project that needs to create ananalyticaldatabase for on-line analytical processing (OLAP). I have mostly worked with on-line transaction processing (OLTP) with some limited reporting features. OLAP is a new area for me. In OLAP, the main focus of the database itself is simply to store data for analysis; there is limited maintenance of data. In the previous article, I focused on the differences in

A Book Review

Book and Author Importance of Data Model Quality Takeoff Checklist Merciless Review Merciless Humiliation? Is it Agile? Conclusion Book and Author Today I’m going to review“A Check List for Doing Data Model Design Reviews”by Kent Graziano. This publication is available as an e-book on Amazon.com .The book is very short – it will take you less than an hour to read it. But don’t let the small volume mislead you. Graziano’s writing is very concise and full of information. There are no digressions, examples, anecdotes or metaphors. Just raw, austere instruction on what to check when designing a data model and why it is worthwhile to have the whole team review it before moving to the next steps of implementation. Even the title of the book is like the book itself – clear, not very attractive, but concise.

How (And How Not) to Decompose Relations

When you read about normalization you usually get the set of conditions that a database in the nᵗʰ normal form should satisfy and the set of rules, a sort of a cook-book, for obtaining that normal form. But why these rules are safe to apply to your denormalized relations is a skip material. Here, I would like to present some elementary concepts on how we decompose relations and what can go wrong.The concepts I want to present may seem a bit complicated but, on the other hand, they are so basic that you rarely see it written. Still, they are essential for understanding how the normalization process works. One may prepare a perfect meal just by following a recipe but no one can master the art of cooking without understanding what’s going on behind-the-scenes. The same holds true with databases.

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:

Why Do We Need Transaction Isolation Levels?

Imagine that you’re implementing a system for a large bookstore. Many operations have to take place at the same time, multiple customers may simultaneously want to purchase the same book, prices of some books may change, new products are still being delivered, etc. As you know, a single action done by a user is run as a transaction in a database. Let’s see what can happen if you allow multiple transactions to work on the same data.

OLAP for OLTP Practitioners

I am currently working on a project where we need to create a database that will be primarily used to store data for reporting and forecasting. In the past, I have mostly worked with databases used for typical CRUD (create, retrieve, update, and delete) operations of data with some limited reporting features. When performing CRUD operations, normalization is important; while in analytics, a de-normalized structure is generally preferred.Here is a simple example of a normalized order table:

Database Modeling in Scrum Teams

Welcome! This is my first blog entry. I would like to invite you to explore the world of Scrum and databases. I’m a professional Scrum master. During my work, I’ve frequently encountered difficulties when collaborating with others to model a database. I would like to present crucial elements of applying Scrum. I will prove that Scrum is a perfect solution for plenty of teams.A Long Time Ago, in a Galaxy Far Far Away – Waterfall

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

“Concise Guide to Databases” – briefly about everything

There are books that you plan to read. Then, there are books that you actually started reading and then stopped. Then, there are books you started reading and you hope to finish sometime. The last database book I did read was “Concise Guide to Databases” by Peter Lake and Paul Crowther.As title suggests this isnota book that dwellsdeeplyinto one specific aspect of DB theory or technology, quite the opposite. So if you want to master one topic that you work on this is not the book for you. If you have to write a specific piece of code using this-and-that then this is not the book you need right now. However, you may be interested in this book if you are a database newcomer or if you want to get a bigger picture of databases in general. Also, if you want to look at new solutions in DB business that could suit your company then you may be interested in this book. Personally, I read this book out of curiousity.

Dirty PostgreSQL Database? Clean It up With a VACUUM!

Just like your house gets dirty and requires cleaning from time to time, your PostgreSQL database may accumulate “dust” too. Unwanted pieces of data make the DB grow bigger and bigger. However, there is a mechanism to get rid of junk data and the DB can do it automatically for you.How Does a Database Get “Dirty”?Let’s talk a little bit about concurrency in databases. Generally, when multiple users (or more concretely – database transactions) read or modify the data stored in a database at the same time, they may conflict with each other. One user wants to read the row while another one may want to modify it. They do these operations within different transactions so they should be able to do their jobs without worrying about someone else doing something with the same data. It’s the DBMS’s job to handle it. So let’s look at this problem from a DBMS’s perspective. There are a few possible strategies to do this.

Designing & Deploying a PostgreSQL Database in the Cloud

Our users sometimes say to us: “Vertabelo is awesome but… how can I add the data?”  Oh, come on! Vertabelo is not a database engine or a database administration tool. At least not at the moment. For now, Vertabelo is intended mainly to design database models. This doesn’t mean that we don’t care about what you can do next with models created in our application. Of course, we do! That’s why we provide our users with some useful tools on

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.

Formatting Ad-Hoc Reports in PostgreSQL

OR/M and noSQL guys see SQL as an awkward way to store and retrieve objects from the database. Here at Vertabelo, we see SQL as a useful language to query data. Making reports in pure SQL is a pure pleasure. Running an interactive query is almost the same experience as asking a human to provide information.At some point you’ll have a need to write a report in a few seconds. Running IDE and coding is not a viable option. In this article I’ll show you how to perform and format ad-hoc reports using a PostgreSQL command line client. I’ll focus on formatting options so examples will be simplified.

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.

5 Must-Read Database Modeling Books?

I recently realized that ourdatabase modeling librarycould use a fewmore advancedtitles. So I headed over to Amazon to see what they had on offer.There are plenty of introductory books for beginners that tell you how to normalize data , and introduce you to indexes , but what about something for the professional, grown-up database modeler? Here are 5 of the best database modeling books I found (listed in no particular order) that go beyond the basics and come highly recommended by Amazon reviewers. Go ahead and add them to your wishlists!

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

What Is a Database Index?

Sooner or later there comes a moment when database performance is no longer satisfactory. One of the very first things you should turn to when that happens is database indexing. This article will give you a general overview on what indexes are without digging into too much detail. We’ll discuss additional database index topics in future articles.In general, a database index is a data structure used to improve queries execution time. To explain what an index is, we need to say a few words on how the data stored in tables is organized.

Database Indexing – Whose Role Is It Anyway?

I remember my own confusion about who was responsible for database indexing when I was a junior programmer some years ago. At one of my very first commercial projects, software architects created a database structure, developers wrote the code, and browser magicians made it look outstanding. The final product was deployed to servers and champagne corks popped. Our good mood didn’t last for too long…After three months or so it turned out that one of the main features, a search engine, started choking. A rather simple search query took a few seconds to run. The time for optimization came very quickly. After some fast profiling with the production data and the cause of the slowdown became obvious – it was the DB that consumed too much time.

SQL vs. Mongo query

In recent times, NoSQL databases have become a hot topic and have gained a crowd of advocates. Indeed, when NoSQL database started to arise, SQL-to-NoSQL converters arose with them as well. But unfortunately, thanks to the code conversion possibility, SQL supporters gained unquestionable evidence that in many cases the good, old SQL is much easier to use. Especially when it comes to queries.I performed a quick Google search and found plenty of nice converter tools. Here is one of them:

Database engine usage by Vertabelo users

The Vertabelo journey continues … We now have almost10,000users and the number of Vertabelo advocates keeps growing strong.Vertabelo users come from over100countries and speak various languages. What unites them?The relational database.Let’s see what relational databases they use:We wanted to determine the most popular database engine among Vertabelo users based on one of three widely-used operating systems:Windows,Linux,Mac OS.Guess what we find out?Among all supported popular databases (PostgreSQL, MySQL, Microsoft SQL Server, SQlite, Oracle, IBM DB2, HSQLDB),

How to Model Inheritance in a Relational Database

In the process of designing our entity relationship diagram for a database, we may find that attributes of two or more entities overlap, meaning that these entities seem very similar but still have a few differences. In this case, we may create a subtype of the parent entity that contains distinct attributes. A parent entity becomes a supertype that has a relationship with one or more subtypes.First, let’s take a closer look at a simple class diagram.

N-ary relationship types

When we design a database, we draw an entity relationship diagram (ERD). It helps us understand what kind of information we want to store and what kind of relationships there are. It is imperative that this diagram is easy to read and understand.The number of entities in a relationship is the arity of this relationship. The aim of this article is to give some examples and show how big an impact the arity of relationships has on not only the readability of the diagram, but also the database itself.

jOOQ – A Really Nice Alternative to Hibernate

In the Java world, there are many standards. Some of them have grown up from commonly used libraries or frameworks. For example, JPA, which was highly influenced by Hibernate.Standards are good. They eliminate chaos and bad practices. But standards shouldn’t be a kind of Bible for developers. A set of tools used in the project should be chosen consciously, not blindly “because it’s a standard and others use it.” It’s like in the business market, where a monopoly causes a lot more harm than good.

Relational databases are not rocket science ready

As usual I’ve tried to make the title of my article something catchy. This article will not be about rocket science per se, but instead it will be about a specific usage of relational databases. So, keep reading!Suppose you’re working at some top secret corporation and you’re developing a rocket controller. The rocket controller must:explode when it is near a targetlog time and distance (in case it doesn’t explode)The Java code to achieve this may looks like the following:

Database Design 101

In this video you will learn how to start creating your database model. You will find out why nouns are important and how you should handle them when creating a database model.If you want to learn more, read our beginner tutorial on how to create a database model .

The Boyce-Codd Normal Form (BCNF)

Why do you need all of this normalization stuff? The main goal is toavoid redundancyin your data. Redundancy can lead to various anomalies when you modify your data .Every fact should be stored only onceand you should know where to look for each fact. The normalization process brings order to your filing cabinet. You decide to conform to certain rules where each fact is stored.Nowadays the go-to normal forms are either the

SQL Performance Explained – the must-read book

Some time ago, the Vertabelo Team participated in the PostgreSQL Conference Europe 2013 . Some of the talks were really nice. One of them stuck in my head for quite a long time. It was Markus Winand’s lecture titled“Indexes: The neglected performance all-rounder.”Although I had had a solid background in databases, this 50 minutes long talk showed me that not everything concerning indexes was as clear to me as I had thought. This was the kind of lecture I like the most – when you sit and say to yourself “damn, I didn’t know that!”

On Keys

Today we continue our series of posts on data normalization. In the previous post on data normalization I explained what functional dependency is. Today we will talk aboutcandidate keysin a table.A candidate key is a set of columns such thatall other columns in the table are dependent on it, and the set isminimal, that is if you remove a column, then the resulting set is not a candidate key.Example: Table Person

On Functional Dependencies

Do you remember the post about update anomalies ? I promised you we’d explain how to design tables which have no update anomalies. So here we go!Today we begin a series of posts on data normalization. We will talk aboutfunctional dependencies, a concept that needs to be explained before we dive deeply into database schema normalization.The subject is rather abstract and theoretical but I will try to restrain myself from going too deep into mathematics. I will try to keep things simple and down-to-earth. (The operative word being try ;) )

Using an SQLite Database on Android Platform A Short FAQ

When developing an application with a SQLite database as persistent storage, it’s worth it (or necessary, in fact) to know some low-level details like: where the data is storedphysicallyand how we can determine if its structure is really the same as what we expect it to be. Being familiar with these things makes it easier and faster to develop, as well as find and fix bugs. I’ll try to explain some of the most frequently asked questions regarding SQLite databases.

Update Anomalies

Let’s take a look at the following table:What’s wrong with this table? It’s difficult to modify data in it. Upon modification, several anomalies can occur:Insert anomaliesIt’s impossible to insert a product into the table if the product hasn’t been bought by a customer yet. Similarly, it’s impossible to insert a customer who hasn’t made a purchase yet.Update anomaliesIt’s difficult to update data in the table. If you want to change the name of the product, you have to update all rows where the product is bought. You cannot change the price of the product for all future purchases.

Handling Database Structure Changes

In the previous article we wrote a simple Android app allowing the user to manage his ToDo list. He could add new tasks, mark them as done and delete them. That article showed how to create an SQLite DB in an automated way and how to do some simple CRUD operations on it.Let’s say that the first version of an application is released, we distribute it (i.e., it shows up in Google Play), people download and use it. After some time we decide to improve the app. Our goal is to add the ability to prioritize the tasks.

How to Create a Database Model From Scratch

So you want to create your first database model but you don’t know how to start? Read on!I assume you already know a little about tables, columns, and relationships. If you don’t, watch our video tutorials before you continue.Start With a System DescriptionYou should always start creating a database model with a description of a system. In a classroom situation, a system description is given to you by a teacher. In real life, preparing a description is a

Using an SQLite Database on Android Platform – Introduction

According to the report of the International Data Corporation , Android operating system reached more than 80% market share during the 3rd quarter of 2013. Together with iOS, Android dominates in the mobile devices’ world.This made me think that it may be worth writing a few words about how to create mobile applications for these two mobile platforms. But not the kind of “hello world” applications – there are plenty of tutorials about that. I’d like to focus on the use of local

Database Design 101

In this video tutorial you will learn about references – how to create a relationship between the tables, how it affects their structure, and how it looks in the data.

WordPress Database Finally Unveiled!!!

Sorry for that tabloid title, but I couldn’t resist. I’m going to show you how to view the database design of other people’s systems. It will work for legacy systems as well. Of course, you may call show tables and describe table in the database console but that’s a very rudimentary way to examine the structure. You’ll miss a bird’s eye view of the design which is crucial to understanding a system as a whole.

Database Design 101

Vertabelo presents part 2 of our Database Design 101 series that brings you easy-to-understand introduction to databases. This time we will focus on table columns and most commonly used data types.

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

Welcome to our new Database Design 101 series that brings you an easy-to-understand introduction to databases. Let’s begin with answering the question what a database is and how data is stored in databases.

Milestones of Relational Databases

Computer databases first emerged in the late 1960s after computers had become powerful enough to handle large amounts of financial, human resources and bank data. Let’s take a look at the history of how modern databases came about.1960s:navigational databases, mostly hierarchical databases and network databases, were first used1970:E.F. Codd from IBM introduces the concept of relational databases and the first normal form1971:second and third normal form are introduced by Codd1973:project INGRES (the predecessor of PostgreSQL) begins in Berkeley

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

Testing Support from the Database Level

Testing is difficult. Good database design may make it more efficient and more easily applicable. It may help detect regression – a situation when a working code breaks due to some apparently unrelated change to the code.You cannot always afford comprehensive regression testing of your system. But what you usually can afford to do is to implement a repeatable process for build and automated testing of your system. It may cost a little at the start but it will pay off greatly later.

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,

How to embed a database diagram on the Internet

So I’m sitting here thinking to myself: How should I share a new Vertabelo feature with you?Should I write an over-hyped article full of industry jargon and marketing-speak?Hmmm. Tried and failed.I’ve just gotta show you the feature straight up and how simple it is to use. More meat, little fat, no fluff :).Well tah dah! Here it is:Go ahead, click on it. Use the mouse wheel to zoom. Drag to pan. Click on the logo make the diagram bigger.

Give the toys back to your children!

You may not believe it but the items in this picture are not just kids’ toys; they areimportant partsof the software development process! We call them “database tokens.”When there are multiple developers on the project and you are not creating a few java classes for yourself, you need a versioning system which stores files with the source code. We use GIT, which allows developers towork simultaneouslyon the same module or even on the same Java file.

Theory and History: Why are Relational Databases “Relational”?

Many people wonder why relational databases are called “relational.” Some think that it’s because of a logical entity-relationship model you often start your design with. Or, because you have tables and relationships (aka foreign keys) between them. But that’s not the case.The name comes from the mathematical notion of “relation.” It all started with  E. F. Codd  who in 1970 (in the article  A Relational Model of Data for Large Shared Data Banks

When Normal Forms are Not Enough

A real-life example:Let’s assume that we have a system which stores data fordistributorswho sell products manufactured by some company. Each distributor receives points for selling products and may redeem a specified number of points to obtain a discount or extra bonus. Points are calculated byan external systemand updatedvery frequently– after processing of each order placed by the distributor himself, a member of the distributor group, or a customer assigned to the distributor. The distributor is identified using a distributor number. We are using a DB2 database.

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:

JDD 2013 – What we found out about databases?

A week ago we were in Kraków at Java Developers Days , one of the biggest annual Java conferences in Poland. For the first time we showed Vertabelo live to a wider audience.It was a pleasure to meet all the Java developers and talk about their projects, technologies and tools they use. We had a strong team at the conference and it allowed us to talk anyone interested - more than one hundred talks. See some of us in action on the photo below.

Five reasons why we built Vertabelo

We are launching a new product on a market, so let me tell you the short story about how and why Vertabelo was born. e-point and I personally have more than fifteen years of experience in building business applications. All of them use relational databases as a storage for their data. Most of those applications are rather big - think abouthundreds of tablesin a database and hundreds of screens in a UI. We had to design databases in