I’m presenting a small series of articles that describes database normalization. But the topic is already covered quite well by Wikipedia’s Database Normalization article. And so in this series I’m going to explain database normalization by using examples. Actually by counter-example. The goal is to come up with counter-examples that break the rules so bad that you’ll never forget the rules for each normal form.
DB Normalization is a good thing
But I’m not going to explain why. At least not in depth. That’s a topic for another day. Let me just say that SQL Server – and Oracle and DB2 and the rest – were designed to support Normalized Databases. Designing Normalized databases makes the best use of SQL Server and doing so avoids a lot of messy problems.
Before We Start…
I’m not going to jump into first normal form (1NF). That’s next article. All database schemas that are 1NF must be relational. So in this article I’m looking at information stores that aren’t relational.
I start by giving an example of an information container. It’s not a database schema, It’s not even a data model (strictly).
So as an information store, the brain is not super-reliable but it has it’s place. I chose this example because if you’re not in I.T., the brain still holds a ton of information!
- To the theoretical physicist, it has a lot information by virtue of the fact that it’s a physical object.
- The chemist and biologist would be fascinated by the information stored in the DNA of every cell.
- And of course if connected to a living body, the brain has information for everyone else, in the usual sense (just ask!)
The Relational Database Model
Getting back to databases. Specifically ones that aren’t relational.
It’s surprising that DB normalization was defined at the exact same time as the Relational Database Model (Well, not exactly the same time. They were a chapter apart in Codd’s paper A Relational Model of Data for Large Shared Data Banks 1970)
So for a schema to even be considered for first normal form, it has to be relational. And while SQL Server is called an RDBMS, it turns out to be surprisingly easy to define tables that aren’t relational. Take this example:
-- not a relational table CREATE TABLE Employee ( EmployeeNumber INT NOT NULL, FirstName NVARCHAR (50) NOT NULL, LastName NVARCHAR (50) NOT NULL )
There’s no primary key! If your tables are missing primary keys, then you’re automatically disqualified. No 1NF for you! (Incidentally the same goes for missing foreign keys.)
In the next few articles I leave the non-relational stuff behind. We’ll look at examples of relational tables (don’t worry, there’s still a lot of ridiculousness coming).