Author: Jean-Marc Reynaud

Freelance Data Modeler

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.

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.

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.

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.

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

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.