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.

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.

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 17th century 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.

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.

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

blockquote.citation { width: 440px; font: italic normal 300 15px/24px 'Helvetica', 'Arial', sans-serif; text-align: left; padding: 20px 30px 20px 40px; background-color: #f2f2f2; border-top: 1px solid #d8d8d8; border-bottom: 1px solid #d8d8d8; margin: 30px auto; background-image: url(/_file/blog/_img/openquote.png); background-position: top 10px left 10px; background-repeat: no-repeat; text-indent: 0px; box-sizing: border-box; } blockquote.citation p{ font: italic normal 300 15px/24px 'Helvetica', 'Arial', sans-serif; background-color: transparent; display: block; } What part does database design play in running a restaurant?

Does Evolving Contact Information Mean Changing Your Database?

There are a number of ways to contact someone these days, right? We have various phones: mobile and landline, personal and work. We have different addresses – residential, mailing, billing, business, etc. – and likely several email addresses, too. Don’t forget Skype and various messaging apps. Now add in LinkedIn and Facebook –which by the way, both have their own messaging elements. Not that long ago, many of these didn’t exist.

Five Common Dimensional Modeling Mistakes and How to Solve Them

p.subtitle { font-size: 13px; text-align: center; } 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 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.

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?

Party Relationship Pattern. How to Model Relationships

Relationships are everywhere: between people, between organizations, between organizations and people. Think about being an employee of a company, being a member of a project team, or being a subsidiary of another company. Is there a straightforward way to accurately model and manage all these relationships? Can we easily answer the question ‘Who knows who?’ A Quick Review of Relationships Exactly how this basic model was derived was described in my previous article, Flexible and Manageable Bill of Materials (BOM) Designs.

Flexible and Manageable Bill of Materials (BOM) Designs

The bill of materials design pattern is deceptively simple, yet incredibly powerful. This article will introduce an example, familiar to IT professionals, that you may not have thought fits the BOM pattern. It will also introduce concepts to show you how to make your BOM structures more flexible and much easier to manage. A Short Recap of the BOM A bill of materials has its roots in manufacturing. It is a list of the raw materials, sub-assemblies, intermediate assemblies, sub-components, parts, and the quantities of each needed to manufacture an end product.

Database Modeling Tips

When you were learning database concepts, data modeling looked pretty easy, didn’t it? You knew all the rules, and modeling seemed like a game: get a challenge, do your best, and eventually solve it. Job well done! Moving up to the next level – and so on. As you continue, though, you’ll see that database modeling is also an art. Many cases require a totally new approach. Everything can be done ‘by the book’, but sometimes you get better results when you go less orthodox.

Identifying the Bill of Materials (BOM) Structure in Databases

The bill of materials (BOM) design pattern is deceptively simple, yet incredibly powerful. Historically, it’s been employed to model product structures, but the pattern can be used to do much more than simply define a hierarchy. This article will introduce three very different examples to help you to recognize the pattern in your own projects. What Is a Bill of Materials, or BOM? A bill of materials has its roots in manufacturing.

Looking for a Simple Full-Text Search? 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

blockquote.citation { width: 440px; font: italic normal 300 15px/24px 'Helvetica', 'Arial', sans-serif; text-align: left; padding: 20px 30px 20px 40px; background-color: #f2f2f2; border-top: 1px solid #d8d8d8; border-bottom: 1px solid #d8d8d8; margin: 30px auto; background-image: url(/_file/blog/_img/openquote.png); background-position: top 10px left 10px; background-repeat: no-repeat; text-indent: 0px; box-sizing: border-box; } blockquote.citation p{ font: italic normal 300 15px/24px 'Helvetica', 'Arial', sans-serif; background-color: transparent; display: block; } Very few database authors mention the challenges of globalization and localization in any meaningful way.

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!

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 use dimension tables to describe data aggregated in a fact table.

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.

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 Schema if (typeof VertabeloEmbededObject === 'undefined') {var VertabeloEmbededObject = "

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 play Lady Windermere’s Fan, Lord Darlington tags a cynic as “somebody who knows the price of everything, and the value of nothing.

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.

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 modeling Data warehouse development Database reverse engineering Agile Data Modeling Many think of data modeling as a laborious task.

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.

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 Started The beginning of crow’s foot notation dates back to an article by Gordon Everest (1976, Fifth Computing Conference, IEEE).

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: conceptual data model – an overview of what should be included in the general database model.

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 Confirmed Unique – the user decides the two entities are unique.

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.

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.

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 I have quite simple and straightforward requirements this time.

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 a generic and extensible database 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.

19 Online Resources for Learning About Database Design Errors

We all make mistakes, and we can all learn from other people’s mistakes. In this post, we’ll take a look at numerous online resources for avoiding poor database design that can lead to many problems and cost both time and money. And in an upcoming article, we’ll tell you where to find tips and best practices. Database Design Errors and Mistakes to Avoid There are numerous online resources to help database designers avoid common errors and mistakes.

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

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 Carpooling So, we need to design a data model for a ride-share (aka carpooling) website.

Problem Set 2 – Identifying Entities and Attributes

function toggleDiv(id) { $("#hide-show-div-" + id).toggle(); var button = $("#hide-show-button-" + id); var label = button.prop('value'); if (label == 'Hide Solution') { button.prop("value", "Show Solution"); } else { button.prop("value", "Hide Solution"); } } 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: Countries Description: 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.

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

function toggleDiv(id) { $("#hide-show-div-" + id).toggle(); var button = $("#hide-show-button-" + id); var label = button.prop('value'); if (label == 'Hide Solution') { button.prop("value", "Show Solution"); } else { button.prop("value", "Hide Solution"); } } 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 School Mr. Trotter, the proprietor of a rapidly-growing language school, wants to introduce a new system in his company.

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: MyISAM or InnoDB. But let’s try to be a little more flexible and imagine how different storage engines can be used. The Initial Data Model To begin, let’s build a simplified data model for a CRM (customer relationship management) system that we’ll use to illustrate the point.

Database Modeling

I wrote a song about dental floss but did anyone’s teeth get cleaner? Frank Zappa When 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.

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

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 What will the data in our system be about?

Naming Conventions in Database Modeling

What’s In A Name? The Database Edition Database models require that objects be named. While several facets of naming an object deserve consideration, in this article we’ll focus on the most important one: defining a convention and sticking to it. Why Use Naming Conventions? Look at the database model below. I went a bit overboard and removed as many traces of a naming convention as I could. This proves my first point: a naming convention is an important part of a well-built data model.

Building an Information Mart With Your Data Vault

In the 3rd post 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 Marts As of Data Vault 2.0, the terminology changed a bit to be more precise.

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–2007 Selling 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.

Database Model for a Messaging System

People love to communicate. We often joke that any software system always evolves into a messaging system. This article will explain the system requirements and step by step approach to design a data model for a messaging system. Requirements in Nutshell The core functionality of a messaging system in an application is to send notifications/messages to a user or a set of users. Our system also allows one to send messages to a user group.

Applying Simple Access Control to a Small Data Model

.KeyLearning { max-width: 90%; padding: 20px 20px 30px 30px; margin: 40px auto; background-color: #643296; color: #ffffff; border: 0px solid #360063; border-radius: 8px; -webkit-border-radius: 8px; -moz-border-radius: 8px; -khtml-border-radius: 8px; } .KeyLearning .KeyLearningTitle { font-size: 1.125em; font-weight: bold; margin-top: 0px; } “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.

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 simply Human Resources (HR) type model that most people should relate to (even if you have never worked with an HR model).

Agile Modeling: Not an Option Anymore

The world is changing. No – the world as we knew it in IT has changed. Big Data & Agile are hot topics. But companies still need to collect, report, and analyze their 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 be agile and either deal directly or indirectly with unstructured and semi structured data?

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 with simple applications like the sieve of Eratosthenes or enumerating the Fibonacci sequence.

Modeling a Database for Recording Sales. Part 1

Storing sales data properly and later combining it can lead to creating a predictive model with a high rate of accuracy. In this and the next few articles we’ll analyze a database design for recording sales. Everyone lives by selling something. Robert Louis Stevenson In today’s world, selling products is ubiquitous. And salespeople who have access to robust tools that leverage historical data to analyze trends and enable an enterprise to adjust business strategies accordingly have an advantage over their competitors.

Tips for Better Database Design

Over the years, working as a data modeler and database architect, I have noticed that there are a couple rules that should be followed during data modeling and development. Here I describe some tips in the hope that they might help you. I have listed the tips in the order that they occur during the project lifecycle rather than listing them by importance or by how common they are.

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. Introduction Before moving ahead with the article, I’d like to explain what identifying or non-identifying mean. 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.

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 Idea In most systems, there is usually a need to have roles and user statuses. Roles are related to rights that users have while using a system after successfully logging in.

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.

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.

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 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. Introduction I like to use a domain driven approach for 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.

Email Confirmation and Recovering Passwords

Modern applications have plenty of authentication features beside registration and login. In this article we will take a look at how to design the database for two such features: email confirmation and password recovery. Email Confirmation What Is It? Most people familiar with the Internet know what an activation email is. An activation email is sent to the user after he or she registers for an account on a website or web application and contains a link that will allow the user into the system.

7 Common Database Design Errors

Why Talk About Errors? Model Setup 1 – Using Invalid Names 2 – Insufficient Column Width 3 – Not Indexing Properly 4 – Not Considering Possible Volume or Traffic 5 – Ignoring Time Zones 6 – Missing Audit Trail 7 – Ignoring Collation Why Talk About Errors? The art of designing a good database is like swimming. It is relatively easy to start and difficult to master.

How to 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 most sensitive process 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 with user name and password.

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 Integer When defining a surrogate primary key for a table, two options are the most common: Integer and UniqueIdentifier (aka.

Database Design: More Than Just an ERD

Database design is the process of producing a detailed model of a database. The start of data modelling is to grasp the business area and functionality being developed. Before Modeling: Talk to the Business People This is a key principle in information technology. We must remember that we provide a service and must deliver 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.

How to Convert a Data Model Between Two Database Management Systems

Even though Vertabelo doesn’t provide a direct conversion of a data model from one database engine to another, this can be easily done in a few simple steps. The user’s question We’ve been developing a web application for a while. Initially, we wanted to use MySQL for our database but finally we decided to choose Postgres. Unfortunately, the entire data model was created for MySQL. How can we quickly migrate it to the new database type?

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

Data modeling or database design is the process of producing a detailed model of 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?

Introduction As I mentioned in my article “OLAP for OLTP practitioners”, I am working on a project that needs to create an analytical database 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.

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.

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.

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 A lot of development teams use waterfall or combine waterfall with iterations.

5 Steps for an Effective Database Model

Database design is the process of producing a detailed model of a database. This model contains the necessary logical (table names, column names) and physical (column datatypes, foreign keys) choices to translate the design into a data definition language (aka SQL), which can be used to create the actual physical database. When I need to create the design for a new database, in other words, the data layer for an application, I follow a few mental steps that I think can help others when they need to go through the same process.

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

An online discussion forum is a site where people can hold conversations in the form of posted messages. 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”.

5 Must-Read Database Modeling Books?

I recently realized that our database modeling library could use a few more advanced titles. 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.

How to Store Employees’ Schedules in a Database

Level: Beginner So many organizations face the common problem of storing employee schedules. No matter what institution: a company, a university or simply an individual, many entities need an application to view schedules. Therefore, I will try to come up with a database model and then, in a future article, we’ll talk about a simple application to store employees’ schedules in a database. Currently the design looks as follows:

How Do You Make Your Database Speak Many Languages?

The Scenario You are the owner of an online store, located in Poland. The majority of your customers are from Poland and they speak Polish. But you want to sell your products abroad too and your international customers mainly speak English. So you want your online store to be available in both Polish and English. You also expect that your products will sell well in France, so you anticipate that you’ll have to prepare a French version of the store as well (and maybe Spanish too, because why not?

UML Notation

UML is popular for its notations. We all know that UML is 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, an entity is represented by a rectangle:

Arrow Notation

Arrow notation has become one of the less recognized notations in entity relationships diagrams in recent years. Let’s discuss its elements. Entity and relationships As 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.

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

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 Part 6 – Crow’s Foot Notation Which ERD notation are you using?

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

iframe.video-plugin { width: 735px; height: 415px; border: 0px solid #CCC; margin: 0px; } @media all and (max-width: 767px) { iframe.video-plugin { width: 250px; height: 141px; } } 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 Description You 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.

Database Design 101

iframe.video-plugin { width: 735px; height: 415px; border: 0px solid #CCC; margin: 0px; } @media all and (max-width: 767px) { iframe.video-plugin { width: 250px; height: 141px; } } 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

iframe.video-plugin { width: 735px; height: 415px; border: 0px solid #CCC; margin: 0px; } @media all and (max-width: 767px) { iframe.video-plugin { width: 250px; height: 141px; } } 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 the nouns and noun phrases you would use to describe the domain.

Give the toys back to your children!

You may not believe it but the items in this picture are not just kids’ toys; they are important parts of 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 to work simultaneously on 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 for distributors who 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 by an external system and updated very frequently – after processing of each order placed by the distributor himself, a member of the distributor group, or a customer assigned to the distributor.