Should a table always have a surrogate key? Is it really best practice, or should we stick to natural keys? Let’s dig in to see if there’s one clear answer. (more…)
Database Design
Triggers, Apps, or CDC? Pros and Cons of 3 Data Audit Techniques
People are in your data. They’re looking at it, even changing it. Should they be stopped? Did they break something? If so, who broke it? When? These are common questions that come up (especially after someone breaks something), and DBAs are often called upon to solve the problem. Here are three great ways to start identifying your data gremlins: (more…)
Make Fuzzy Data Safe by Modeling your Matching Rules
We tie data to dimensions using matching rules that are either deterministic or probabilistic.
Deterministic matching is what we use to find an ‘exact’ match. (more…)
The Entity-Attribute-Value Model
The entity-attribute-value model is useful for situations where attributes are dynamically added to or removed from an entity. It is normally composed of three tables: (more…)
Top 10 (or 11) Data Mistakes
Over my time I’ve seen many unique technical solutions in databases. And now I can share some of the worst ones with you!
Disclaimer: I may have been the architect of some of these. Sorry DBA team! (more…)
A Key to Keys
A reference for all the different types of keys used in logical and physical database design.
These tables will be used to illustrate all the different keys: (more…)
Boyce/Codd Normal Form (BCNF)
In my article on 3NF we saw that it was the same as 2NF but with one additional restriction. BCNF, similarly, is 3NF with one more restriction:
BCNF:
“Relvar R is in Boyce/Codd normal form (BCNF) if and only if, for every nontrivial FD X→Y that holds in R, X is a superkey.”
Date, C.J. Database Design & Relational Theory. Sebastopol: O,Reilly Media, Inc., 2012. Print. (Emphasis theirs)
Third Normal Form (3NF)
2NF, 3NF, and BCNF are rules for evaluating functional depencies, or FD for short. If you are not familiar with the concept of functional dependency, please see my post on 2NF where I describe it in detail.
If you’re ready to tackle 3NF, then let’s begin! (more…)
Second Normal Form (2NF)
Second Normal Form (2NF) is where we start analyzing the relationships of columns within a table to each other. (more…)
First Normal Form (1NF)
Definition
Each row contains one value, of the appropriate type, for each column, and there are no duplicate rows.
This is a simple rewording of the formal definition:
“Let relation r have attributes A1, …, An, of types T1, …, Tn, respectively. Then r is in first normal form (1NF) if and only if, for all tuples t appearing in r, the value of attribute Ai in t is of type Ti (i= 1, …, n).”
Date, C.J. Database Design & Relational Theory. Sebastopol: O,Reilly Media, Inc., 2012. Print. (Emphasis theirs)
