I want to normalize a database schema for efficient transactions. I want to make sure all tables in the schema are in First Normal Form (1NF).

There is no universal definition of 1NF; some definitions require only atomicity such that it may be impossible to violate 1NF if using a relational database such as MySQL (where there is no direct concept of tables within tables). Other definitions of 1NF restrict any attribute from containing a NULL value.

Practical examples make this much easier to understand. Let’s consider the follow table:

mysql> SELECT username, domain, another_domain FROM foobar_users;
+-----------------+--------+----------------+
| username        | domain | another_domain |
+-----------------+--------+----------------+
| bjones          | XYZ    |                |
| jsmith          | XYZ    |                |
| bjones, hmiller | ABC    | XYZ            |
| gwashington     | ABC    |                |
+-----------------+--------+----------------+
4 rows in set (0.00 sec)

This table violates what a reasonable data architect refers to as 1NF; namely, a table in 1NF has no repeating columns and no repeating groups.

The third row overloads the username column with two users (comma separated), and there are repeating columns domain and another_domain. The column order (domain, another_domain) may need to match the overloaded username field.

Putting this in 1NF should look like this:

mysql> SELECT username, domain, role FROM foobar_users;
+-------------+--------+-------+
| username    | domain | role  |
+-------------+--------+-------+
| bjones      | ABC    | admin |
| bjones      | XYZ    | admin |
| gwashington | ABC    | user  |
| hmiller     | ABC    | user  |
| jsmith      | XYZ    | user  |
+-------------+--------+-------+
5 rows in set (0.00 sec)

Now let’s consider the following DDL:

CREATE TABLE foobar_users (
    username VARCHAR(20) NOT NULL,
    domain VARCHAR(20) NOT NULL,
    dept VARCHAR(20),
    status VARCHAR(12),
    login_id VARCHAR(20),
    login_pw VARCHAR(32),
    role VARCHAR(12) NOT NULL,
    website VARCHAR(255),
    partner VARCHAR(20),
    PRIMARY KEY(username, domain, role)
);

The tables in the schema are now normalized to 1NF, next, I’d like to normalize to Second Normal Form (2NF)