Design patterns

Using Configuration Tables to Define the Actual Workflow

by Jean-Marc Reynaud
5 Apr 2016 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.
Read more
Design patterns

Using Workflow Patterns to Manage the State of Any Entity

by Jean-Marc Reynaud
15 Mar 2016 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.
Read more
Design patterns

Security Approaches in Data Modeling. Part 3

by Andrew Wolfe
8 Mar 2016 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.
Read more
Design patterns

How to Store Authentication Data in a Database. Part 4

by Patrycja Dybka
16 Feb 2016 Handling user registration and authentication isn’t an easy task. Both users and developers understand this. Users have to select and remember another username and password (taking into account different password policies, of course). A developer’s responsibility is to make this process secure and to properly protect the user’s data. So it’s no surprise that logging in with one account – Facebook or Google, for example – is extremely popular.
Read more
Design patterns

“Is It Secret? Is It Safe?” Handling Sensitive Data in Your Data Modeling

by Andrew Wolfe
9 Feb 2016 Early in the movie “The Fellowship of the Ring”, the wizard Gandalf asks the hero Frodo this question: “Is it secret? Is it safe?” We may not have a magic ring to protect, but we’re asking the same question. But we’re talking about information. This is the second in a multi-part series on how to apply information security principles and techniques as part of data modeling. This series uses a simple data model designed to manage non-commercial clubs as an example of security approaches.
Read more
Design patterns

The Reference Data Pattern: Extensible and Flexible

by Jean-Marc Reynaud
4 Feb 2016 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.
Read more
Design patterns

Logging in With External Services

by Leandro Toniut
14 Jan 2016 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.
Read more
Design patterns

Building an Information Mart With Your Data Vault

by Kent Graziano
17 Nov 2015 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.
Read more
Design patterns

The Business Data Vault

by Kent Graziano
3 Nov 2015 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.
Read more
Design patterns

Applying Simple Access Control to a Small Data Model

by Andrew Wolfe
27 Oct 2015 “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?
Read more