Tag: database modeling

Designing a Database for an Online Job Portal

Across the globe, the job portal site is a well-known feature of the Internet landscape. Big players like Indeed and Monster have turned job hunting and recruiting into a veritable online industry. Let’s dive into the elementary features leveraged by job portals and build a data model that can support them.People love saving time by using technological innovations; the online job portal is another version of working smarter, not harder. Job seekers and companies alike realize the value in taking their search online: they get a better reach at higher speeds and lower costs.

A look at algorithms used in RDBMS implementations of DWH systems

When you’re using a data warehouse, some actions get repeated over and over. We will take a look at four common algorithms used to deal with these situations.Most DWH (data warehouse) systems today are on a RDBMS (relational database management system) platform. These databases (Oracle, DB2, or Microsoft SQL Server) are widely used, easy to work with, and mature – a very important thing to bear in mind when choosing a platform. Although they include special features for DWH configuration (like bitmap indexes, partitioning, and materialized views) we still have to take care of data transformation ourselves.

A Database Model for a Taxi Service

Call them taxis or cabs, these convenient rides for rent have been around for centuries. Nowadays, it’s a lot more complicated to run a taxi service. In this article, we’ll look at a database model designed to meet the needs of a cab company.The history of “calling a cab” began in 17thcentury London. In most places, cabs are more affordable than ever. They are also becoming a lot more accessible: we can order a taxi by phone, via mobile applications, or on the Web. Or we can use the same techniques that have been working for hundreds of years – line up at a cab station or flag one down on the street.

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.

A Data Model for Listing Apartments and Other Rental Units

Everyone planning to move to a new location looks for good accommodations. In the Internet era, the very first place they tend to look at sites dedicated to listing rental properties. According to a poll by one well-known agency, 76% of people looking to rent turn to the internet first; this figure rises to 88% for those aged 25–34.Let’s dive into a full-fledged data model that supports such sites and their underlying features. Our model also enables site users to conveniently filter search results.

Serving Delicious Food (and Data) – A Data Model for Restaurants

What part does database design play in running a restaurant? What might the data model for a restaurant database look like? Find out in this article.A restaurant serves people with ready-made food. This is a type of business that is thriving all over the world, and often with a lot of flare. People feel very comfortable going to restaurants, and they are starting to expect a wide range of options when it comes to their next meal.

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

What Do the Olympic Games, UEFA Euro 2016 Football Matches, and Databases Have In Common?

On hearing what I do, people tend to ask me the same question:Can you develop a system that predicts football match results? Or Olympic medal outcomes?Personally, I don’t put much faith in predictions. Still, if we had a large amount of historical data and relevant indicators, we could certainly design a system to help us come up with more accurate assumptions. In this article, we’ll consider a model that can store the results of matches and tournaments.

Dimensions of Dimensions: A Look at Data Warehousing’s Most Common Dimensional Table Types

When we start a data warehousing project, the first thing we do is define the dimensional tables. Dimensional tables are the interesting bits, the framework around which we build our measurements. They come in many shapes and sizes. In this article, we are going to take a closer look at each type of dimensional table.Dimensional tables provide context to the business processes we wish to measure. They tell us all we need to know about an event. Since they give substance to the measurements (i.e. fact tables) of the data warehouse (DWH) system, we spend more time on their definition and identification than on any other aspect of the project. Fact tables

What a Concept! Is Logical Data Modeling Obsolete?

When databases were sized in megabytes rather than petabytes, their design was a well-defined discipline of data analysis and implementation. A progression of modeling steps – from conceptual and logical through relational and/or physical – promised successful deployment.But as we passed more orders of magnitude in data volume, we seemed to stop seeking modeling approaches to manage that volume. So the question arises: Is logical data modeling obsolete?To arrive at a reasonable answer, we’ll have to recover some forgotten lore about data modeling.

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.

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.

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.

What Do Poker, Blackjack, Belot and Préférence Have to Do with Databases?

How to design a database flexible enough to accommodate several very different card games.Recently, we showed how a database could be used to store board game results . Board games are fun, but they’re not the only online version of classic games going. Card games are also very popular. They introduce an element of luck into gameplay, and there is much more than luck involved in a good card game!In this article, we’ll focus on building a data model to store game matches, results, players and scores. The main challenge here is storing data related to many different card games. We could also consider analyzing this data to determine winning strategies, improve our own playing skills, or build a better AI opponent.

Star Schema vs. Snowflake Schema

In the previous two articles, we considered the two most common data warehouse models: the star schema and the snowflake schema. Today, we’ll examine the differences between these two schemas and we’ll explain when it’s better to use one or the other.The star schema and the snowflake schema are ways to organize data marts or entire data warehouses using relational databases. Both of them usedimension tablesto describe data aggregated in a

How to Design a Localization-Ready System

In this era of globalization, companies – including software developers – are always interested in expanding to new markets. This often means localizing their products for different areas. In this article, we’ll explain a few approaches to designing your data model for localization – specifically, for managing content in multiple languages.What Is Localization?Localization is the process of adapting a product to various markets. It is a prominent factor in achieving maximum market share in terms of product sales. When localization is done correctly, users will feel that the product was produced for their language, culture, and needs.

The Snowflake Schema

In a previous article we discussed the star schema model. The snowflake schema is next to the star schema in terms of its importance in data warehouse modeling. It was developed out of the star schema, and it offers some advantages over its predecessor. But these advantages come at a cost. In this article, we’ll discuss when and how to use the snowflake schema.The Snowflake SchemaThe snowflake schema’s name comes from the fact that dimension tables branch out and look something like a snowflake. When we look at the model above, we’ll notice it’s a fact table surrounded by a few dimension tables, some of which do the aforementioned branching. Unlike the star schema, dimension tables in the snowflake schema can have their own categories.

Security Approaches in Data Modeling. Part 4

This is the fourth in our multi–part series on data modeling for information security as well as data characteristics. A simple data model for a fictional website that supports shared–interest organizations (bird–watching clubs, etc.) has provided us with content for exploring data modeling from a security viewpoint.In Oscar Wilde’s playLady Windermere’s Fan, Lord Darlington tags a cynic as “somebody who knows the price of everything, and the value of nothing.” Sadly, the information in our databases can be unconsciously treated in the same way. Is a customer account worth the sum of its purchases? What do we suffer if we lose four hours of marketing data during holiday shopping season?

The Star Schema

Today, reports and analytics are almost as important as core business. Reports can be built out of your live data; often this approach will do the trick for small- and medium-sized companies without lots of data. But when things get bigger – or the amount of data starts increasing dramatically – it’s time to think about separating your operational and reporting systems.Before we tackle basic data modeling, we need some background on the systems involved. We can roughly divide systems in two categories: operational and reporting systems. Operational systems are often called Online Transaction Processing (OLTP). Reporting and analytical systems are referred to as Online Analytical Processing (OLAP). OLTP systems support business processes. They work with “live” operational data, are highly normalized, and react very quickly to user actions. On the other hand, the primary purpose of the OLAP systems is analytics. These systems use summarized data, which is usually placed in a denormalized data warehousing structure like the star schema. (What is denormalization? Simply put, it’s having redundant data records for the sake of better performance.

Database Design

I’ve been practicing agile database techniques for about twenty years now. My use of these techniques didn’t start as a set plan; rather, it evolved over time as I consulted on various projects. It made sense to look for ways of working faster and with greater customer interaction.I can think of at least three kinds of agile database techniques:Data modelingData warehouse developmentDatabase reverse engineeringAgile Data ModelingMany think of data modeling as a laborious task. But that need not be the case. You can take data modeling out of the back room and do it in front of a customer. You might think that customers would be put off by the technical gore, but they aren’t. Business staff have a passion for their work and a vision of where they want to go. They are thrilled to have IT as a facilitator rather than a roadblock.

Part 3 – Customers, Calls, and Meetings

Previously in this series, we imported SuiteCRM’s database model into Vertabelo and showed how to use Vertabelo’s features to organize it . In this article, we’ll see how common CRM data is stored in its database. We’ll also check the assumptions made in Part 2 about the relations between tables and their functions. If needed, we’ll make modifications to the model.What Do We Currently Have?In Part 1, we installed SuiteCRM locally using the Bitnami installation pack. After successfully logging in, the main SuiteCRM screen looks like the one below:

Using Configuration Tables to Define the Actual Workflow

The first part of this series introduced some basic steps for managing the lifecycle of any entity in a database. Our second and final part will show you how to define the actual workflow using additional configuration tables. This is where the user is presented with allowable options each step of the way. We’ll also demonstrate a technique for working around the strict reuse of ‘assemblies’ and ‘sub-assemblies’ in a Bill of Materials structure.

Crow’s Foot Notation

The most recognizable characteristic of crow’s foot notation (also known as IE notation) is that it uses graphical symbols to indicate the ‘many’ side of the relationship. The three-pronged ‘many’ symbol is also how this widely-used notation style got its name. Let’s see where crow’s foot is placed in the history of data modeling and take a look at its symbols.History: How Crow’s Foot Notation Got StartedThe beginning of crow’s foot notation dates back to an article by

Crow’s Foot Notation in Vertabelo

Various ERD notations follow different styles for entities, relationships, and attributes. Usually there isn’t much standardization between them, so notations bear little resemblance to each other. Among the plethora of ERD diagram notations, crow’s foot notation is definitely the most used. In this article, we’ll investigate its components within the Vertabelo database model.Before we start looking into crow’s foot notation, we must understand that there are various levels of Entity-Relationship diagrams:

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.

Using Workflow Patterns to Manage the State of Any Entity

Have you ever come across a situation where you need to manage the state of an entity that changes over time? There are many examples out there. Let’s start with an easy one: merging customer records.Suppose we are merging lists of customers from two different sources. We could have any of the following states arise:Duplicates Identified– the system has found two potentially duplicate entities;Confirmed Duplicates– a user validates the two entities are indeed duplicates; or

Security Approaches in Data Modeling. Part 3

This is the third of our multi-part series on applying information security approaches to data modeling. The series uses a simple data model, something to manage social clubs and interest groups, to provide the content we look to secure. Later we will address modeling for authorization and user management, as well as other parts of a secure database implementation.In social situations, it’s common to “read between the lines” – deducing the unspoken assumptions and assertions in a conversation. The same occurs in creating software and storing data in a database. Invoices are enumerated with the customer ID embedded, and how many data entities use a date-time as part of the key? It’s hard to imagine thoroughly documenting or structuring everything without some type of omission. But in our last instalment, we went through exactly that exercise. We were able to ascribe sensitivity to several parts of our social club database. But to quantify and manage that sensitivity, we must augment the structure of our data model in order to make the sensitive data and its relationships clear.

Part 2 – How to Organize a Large Database Diagram

In Part 1 of this series, we successfully imported the SuiteCRM database structure into our online database modeling tool. That’s when we saw that the model contains 201 tables without relationships between them. We got a wild bunch of tables that looked really messy. In this article, I will show you how you can organize such a large model.Just after importing to Vertabelo , the SuiteCRM database model looks as follows:

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.

Part 1 – How to Install SuiteCRM and Reverse Engineer Its Database

Customer Relationship Management applications (CRMs) have been around for some time. We could argue a lot about their advantages and disadvantages when comparing them to each other. In this series, we’ll take a look at the database behind one popular open-source CRM: SuiteCRM. We will also show how to install everything we need. In upcoming articles, we’ll organize the data model and see how it reacts to changes made on the user interface.

Viewing Holidays With Data Modeler’s Eyes

Celebration!! Family time!! Long drive!! A day at the beach!! All these words buzz into our minds when we think of holidays. Have you ever considered how a multinational company keeps track of holidays across the globe? There must be a data dictionary to maintain all these details so that they can ensure seamless business with their local partners.This article will explain such a data model.The Project Requirements in a Nutshell

The Reference Data Pattern: Extensible and Flexible

Having reference tables in your database is no big deal, right? You just need to tie a code or ID with a description for each reference type. But what if you literally have dozens and dozens of reference tables? Is there an alternative to the one-table-per-type approach? Read on to discover ageneric and extensibledatabase design for handling all your reference data.This unusual-looking diagram is a bird’s-eye view of a logical data model (LDM) containing all the reference types for an enterprise system. It’s from an educational institution, but it could apply to the data model of any kind of organization. The bigger the model, the more reference types you’re likely to uncover.

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.

WordPress – Behind the Scenes, Part 2

In Part 1 of this series, I demonstrated how to install WordPress locally and how to import a WordPress database into Vertabelo . In this article, we’ll take a closer look at the tables in the WordPress database.A Quick Look at the WordPress Database Model and the DashboardIn the previous part , I imported the WordPress database into our online database modeling tool. For the record, the structure of the database is as follows:

WordPress – Behind the Scenes, Part 1

How often have you wondered about the structure of an existing database? It could be a 5-year-old legacy system or a brand-new open-source project. Let’s take a look at the ERD diagram of the database behind the most famous content management system: WordPress.What Is WordPress and How Does It Work?WordPress is an open-source CMS (content management system) that was initially released in 2003. It started out as a blogging platform, but it has developed so much that today it can be used for almost anything.

Logging in With External Services

Inputting a username and password is one way of accessing an account, but it’s not the only one. In this article, we’ll see how to enable external services (like Google or Facebook) when logging in to a database.What Are External Service Logins?Giving a user the option to access their system accounts through external services is a growing trend among web designers. This option can provide several benefits, such as giving users one less name-and-password combination to remember. It can also help administrators personalize user experiences.

Creating a Data Model for Carpooling

Nowadays, carpooling is accepted and promoted by people around the globe. It certainly reduces one’s personal carbon footprint, and it can be more cost-effective than renting or buying a car.Carpooling also takes a lot of work – organizational work that can readily be done by a well-designed database. This article explains a detailed data model that a carpooling website could use.Data Design, Meet CarpoolingSo, we need to design a data model for a ride-share (aka carpooling) website.

Problem Set 2 – Identifying Entities and Attributes

In an earlier article on data modeling we promised to give you a set of exercises to practice finding entities and attributes. Here is the second installment of our problem set. Enjoy.Problem 1: CountriesDescription:Find the right entities and their attributes to represent all the countries in the world, their interior regions (which can be called states, provinces, or regions) and their cities. We want to represent each country’s name, continent, date of independence, type of government and population. For each region (or province, state, etc.) we wish to store the capital city, the name of the governor, and the population. Finally, for each city we want to have the name, founding date, population, and the number of schools per inhabitant. We would also like to represent what every country calls its interior regions.

Renting out Cars Is as Simple as Driving: A Data Model for a Car Rental Company

You might have rented a car on your last vacation. You reserved your car online, and then picked it up from its designated location after paying all the previously-agreed charges. Once you were done, you returned it to the agency and perhaps paid some additional fees. Did you ever think about the system that makes all these things happen? In this article, we’ll look at a data model for a car rental system.

Problem Set 1 – Identifying Entities

In an earlier article on data modeling we promised to give you a set of exercises to practice finding entities. Well, here they are. Have fun!Problem 1: Language SchoolMr. Trotter, the proprietor of a rapidly-growing language school, wants to introduce a new system in his company. He can no longer keep track of all the information on his own, so he’s asked for our help. He is a bit chaotic, though. This is what he said:

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

I wrote a song about dental floss but did anyone’s teeth get cleaner?Frank ZappaWhen we think of the dental office, our first associations are the drill, the pain, and the fear. OK, that sounds bad. Besides taking care of teeth, a dentist has many other obligations that are professional, legal, or both. All of them require proper data management.To meet this documentation requirement, many dental and medical offices use paper records. Slowly but surely, though, there’s a trend towards the digital records and management of the 21st century.

An Unemotional Logical Look at SQL Server Naming Conventions

In the database world, there are some things that are universally agreed upon. Increased RAM is largely beneficial to DMBS systems. Spreading out data and log files on RAID improves performance.Naming conventions are not one of those things.This is a surprisingly polarizing topic, with the proponents of various methodologies firmly entrenched in their positions. And very vocal and passionate in their defense of the same.This article will delve into some of the specific conventions and the arguments on both sides, while attempting to present a reasonable conclusion for each point.

How Does Database Design Help Organize Teachers, Lessons, and Students?

An investment in knowledge pays the best interest.Benjamin FranklinIn the modern world, education is omnipresent. Now more than ever before, it plays an important role in our society. It’s so important, in fact, that many of us continue our education well after finishing school or college.We have all heard of lifelong learning, non-formal education, and workshops for all ages. These methods differ from formal education in many ways, but they also have things in common. There are classes, lessons, teachers, and students. And just as in a traditional setting, we’ll want to keep track of the class schedule, attendance data, and instructor or student achievement. How can we design a database to meet these needs? That’s what we’ll cover in this article.

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

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.

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

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.

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

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.

Defining Identifying and Non-Identifying Relationships in Vertabelo

Various data modeling tools allow modelers to define relationships in a data-model as identifying or non-identifying. We can define a relationship as identifying or non-identifying in Vertabelo as well. This article will explain the way to do so.IntroductionBefore moving ahead with the article, I’d like to explain whatidentifyingornon-identifyingmean.Let’s take a real time example of a book storing system. In the system, a book belongs to an owner, and an owner can own multiple books. But the book can also exist without the owner and it can change the owner. Thus the relationship between a book and an owner is a non-identifying relationship.

Managing Roles and Statuses in a System

There are many ways to solve a problem, and that’s the case with administering roles and user statuses in software systems. In this article you’ll find a simple evolution of that idea as well some useful tips and code samples.Basic IdeaIn most systems, there is usually a need to haverolesand userstatuses.Roles are related torightsthat users have while using a system after successfully logging in. Examples of roles are “call center employee”, “call center manager”, “back office employee”, “back office manager”, or “manager”. Generally that means that a user will have access to some functionality if he or she has the appropriate role. It’s wise to assume that a user can have multiple roles at the same time.

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

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.

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,

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

SQL Server Datatypes: Common Modeling Dilemmas

When designing a database, early decisions can have a huge impact on the performance and storage requirements. These decisions can be difficult to change later, as most subsequent work will depend on the physical model. This article highlights some common design decisions, flaws, and misconceptions.Creating a Primary Key: Uniqueidentifer or IntegerWhen defining a surrogate primary key for a table, two options are the most common: Integer and UniqueIdentifier (aka.Globally Unique Identifiers

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.

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.

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:

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

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.

Modeling a Basic Data Structure to Manage Users, Threads, and Posts

An online discussion forum is a site wherepeoplecan holdconversationsin the form of postedmessages. Discussion forums allow conversations to take place when people are not on-line, and messages may be temporarily archived. Also, depending on the forum set-up, a message might need to be approved by a moderator before it becomes visible to other users. Forums have a specific set of terms, for example, a single conversation is generally referred to as a “thread”. Threads can be replied to by as many people as would like to. Most forums require users to register and subsequently log in to post messages. However, on most forums, users do not have to log in to read existing messages.

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

UML Notation

UMLis popular for its notations. We all know thatUMLis for visualizing, specifying, and documenting the components of software and non software systems. What’s more, UML has many types of diagrams which are divided into two categories. Some types represent structural information, others general types of behaviors. Among these, there is one that is commonly used for entity relationship diagrams.In UML, anentityis represented by a rectangle:Relationshipsare solid lines with cardinality specified at the ends of the lines:

Arrow Notation

Arrow notationhas become one of the less recognized notations in entity relationships diagrams in recent years. Let’s discuss its elements.Entity and relationshipsAs you can see below, an entity is always represented by a rectangle, which is common to most notations (there isn’t a distinction if it is dependent or independent entity). Relationships and cardinality are represented by various combinations of arrows as the diagram below presents.There isn’t one way to represent attributes. Usually they are depicted the same way like in the IDEF1X notation or UML.

IDEF1X Notation

IDEF1X(Integration DEFinition for Information Modeling) is a method for designing relational databases with a syntax that supports constructs in developing conceptual schema.Not everyone knows that this notation has an interesting history. Indeed, the need for semantic data models was first recognized by the U.S. Air Force in the mid-1970s. As a result, the ICAM Program came into being (It identified a need for better analysis and communication techniques for people involved in improving manufacturing productivity), that later developed a series of techniques known as the IDEF; IDEF1X being one of them.

Chen Notation

Continuing our trip through different ERD notations, let’s review the Chen ERD notation.Peter Chen, who developed entity-relationship modeling and published his work in 1976, was one of the pioneers of using the entity relationship concepts in software and information system modeling and design. The Chen ERD notation is still used and is considered to present a more detailed way of representing entities and relationships.EntitiesAn entity is represented by a rectangle which contains the entity’s name.

Barker’s Notation

When looking at different kinds of ERD notations, it is hard not to come across Barker’s ERD notation, which is commonly used to describe data for Oracle. Richard Barker and his coworkers developed this ERD notation while working at the British consulting firm CACI around 1981, and when Barker joined Oracle, his notation was adopted.Let’s take a closer look at Barker’s syntax.The most important components in the ERD diagram are:entities, which can be thought as physical objects or elements that can be uniquely identified, and

ERD Notations in Data Modeling

An entity relationship diagram (ERD) is a diagram that defines the structure of database instances. Choosing which notation to use is typically left up to personal preference or conventions. Here, you can find some useful information about each notation: Part 1 – Barker’s Notation Part 2 – Chen Notation Part 3 – IDEF1X Notation Part 4 – Arrow Notation Part 5 – UML Notation

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.

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 .

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

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.

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

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

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.

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.