#RevTWIL: Database Design: Normal Forms and How to Glean the Design from a Description

At Revelry, we believe in sharing and learning from one another (beliefs that are rooted in our Core Values). Among the many things we do to encourage internal knowledge exchange is to have a different team member present at each week’s Engineering Meeting. These presentations don’t have to be lengthy, formal, or complicated; in fact, many are brief, but also powerful in that they teach our Revelers something new, inspire conversation, and/or  encourage collaboration.

In the spirit of sharing, we share our This Week I Learned presentations (aka RevTWILs”) here. We hope you find them helpful.

THIS WEEK: Senior software engineer Brandon Bennett shares about database design; specifically, normal forms and how to glean the design from a description.

First, a Few Definitions

Primary Key: A column or attribute used to uniquely identify a row. Must be unique, stable, and irreducible.

Anomalies: Ways data can get out of sync with itself. Three flavors: insertion, deletion, and update anomalies. 

Functional Dependencies: A one-way relationship wherein for each unique value of A only one value of B exists.

Multivalued Dependencies: A one-way relationship wherein for each unique value of A more than one value of B exists.

Transitive Dependency: When A and B have a functional dependency and B and C also have a functional dependency. A and C have a transitive dependency.

Database Normalization: First Normal Form (1NF)

There are five important rules for 1NF:

1. Using row order to convey information is not permitted.
2. Mixing data types within the same column is not permitted.
3. Having a table without a primary key is not permitted.
4. Repeating groups are not permitted.

Database Normalization: Second Normal Form (2NF)

Each non-key attribute in the table must be dependent on the entire primary key.

Database Normalization: Third Normal Form (3NF) – Boyce-Codd Normal Form

Each attribute in the table must depend on the key, the whole key and nothing but the key.

Boyce–Codd Normal Form (BCNF) – Based on functional dependencies that take into account all candidate keys in a relation. However, BCNF has two additional constraints compared with the general definition of 3NF:

1. The table should be in the 3rd Normal Form.
2. X should be a superkey for every functional dependency (FD) X−>Y in a given relation. 

Database Normalization: Fourth Normal Form (4NF)

The only kind of multivalued dependency allowed in the table are multivalued dependencies on the key.

Database Normalization: Fifth Normal Form (5NF)

It must not be possible to describe the table as being the logical result of joining some other tables together.

How to Glean Database Design from Feature Descriptions

• Nouns are usually tables, especially if they are subjects. “A Teacher teaches students.” Tables would be teachers and students.
• Being or possessive verbs usually mean the object is an attribute. “A product has a price.” Price is a column on the products table.
• Action verbs usually show a relationship between two tables. “A teacher prepares lesson plans.” Tables would be teachers and lesson_plans with a one to many relationship.
• Adjectives are also usually multivalued attributes. “We sell a red dress.” If we had a dresses table, color would be an attribute with red as a possible value.

If you’re a more visual learner, check out this YouTube video. It’s well done.

---

Brandon Bennett RevTWIL TWIL