loading...

Normality for a Flexible World

quantumsink profile image QuantumSink ・2 min read

It might be contradictory to view both normality and flexibility in the same vein, but we look to normality in database design to create flexible change. Besides, the idea of normality is a lot like nesting if statements. If you know what those are, anyway. If not, then here's a quick rundown of the first two normal forms:

First Normal Form:

We want to make sure that the data we present is necessary to have separately. For instance, look at this table.

Alt Text

First, let us ask if our table has a primary key.
Well, I wouldn't really get far without making one after the diagrams I've already shown, so yes, we have established "BehaviorID" as a primary key.

Are any columns able to be represented as two other columns?
I would say so. Instances can certainly arise in a messy setup, such as design a column to contain more than one piece of info, but most of the time, I would say you would recognize an issue like this immediately. Always make sure that a column is made to contain one type of data.

Then, finally, do any columns repeat their groups?
Not at all! We can safely establish this table as First Normal Form.

Second Normal Form:

To constitute a Second Normal Form, we need to keep all the components of First Normal in check. Then, we examine whether or not all columns refer to the primary key of the table. If anything could be representing something that isn't the primary key, it's likely that that data can be represented as a different entity (table) in the database.

Alt Text

Take this table for instance. As the OccupationName may relate to the CompanyID, all other columns can be related to the OccupationName, not the CompanyID. Thus, we must delegate that information into a new table by altering this one, then creating a new table to represent Occupations.

And yet, how will we do that? Watch the two videos below, and we'll get into how we ALTER tables in SQL, and have a live look at "Normality".

Discussion

pic
Editor guide