Previously, I normalized my tables to Fifth Normal Form (5NF). Now, I want to continue to normalize such that the tables in my schema are in Sixth Normal Form (6NF).

6NF requires that each table satisfies only trivial join dependencies.

All of the 5NF tables are already in 6NF, except one (it’s been going unnoticed since 2NF), it’s the leftovers from the original foobar_users table!

-- happily in 5NF, but not 6NF
CREATE TABLE foobar_users (
    username VARCHAR(20) NOT NULL,
    dept VARCHAR(20) NOT NULL,
    status VARCHAR(12) NOT NULL,
    PRIMARY KEY(username)
);

As is, it’s in 5NF because every join dependency is implied by the candidate key. Specifically, the only join dependencies are {{username, dept}, {username, status}} — but this is a non-trivial join dependency, so let’s finish what we started.

A 6NF version would look like this:

CREATE TABLE foobar_users (
    username VARCHAR(20) NOT NULL PRIMARY KEY,
    status VARCHAR(12) NOT NULL
);

CREATE TABLE foobar_dept_map (
    username VARCHAR(20) NOT NULL PRIMARY KEY,
    dept VARCHAR(20) NOT NULL
);

The foobar_users table has come a long way since 1NF.

Putting this all together, a 6NF compliant schema would look like this:

CREATE TABLE foobar_users (
    username VARCHAR(20) NOT NULL PRIMARY KEY,
    status VARCHAR(12) NOT NULL
);

CREATE TABLE foobar_dept_map (
    username VARCHAR(20) NOT NULL PRIMARY KEY,
    dept VARCHAR(20) NOT NULL
);

CREATE TABLE foobar_roles (
    domain VARCHAR(20) NOT NULL,
    role VARCHAR(12) NOT NULL,
    PRIMARY KEY(domain, role)
);

CREATE TABLE foobar_role_map (
    username VARCHAR(20) NOT NULL,
    role VARCHAR(12) NOT NULL,
    PRIMARY KEY(username, role)
);

CREATE TABLE foobar_logins (
    username VARCHAR(20) NOT NULL,
    domain VARCHAR(20) NOT NULL,
    login_id VARCHAR(20) NOT NULL PRIMARY KEY
);

CREATE TABLE foobar_login_pw (
    login_id VARCHAR(20) NOT NULL PRIMARY KEY,
    login_pw VARCHAR(32) NOT NULL
);

CREATE TABLE foobar_partners (
    partner VARCHAR(20) NOT NULL PRIMARY KEY,
    website VARCHAR(255) NOT NULL,
    registration VARCHAR(12) NOT NULL
);

CREATE TABLE foobar_domain_map (
    username VARCHAR(20) NOT NULL,
    domain VARCHAR(20) NOT NULL,
    PRIMARY KEY(username, domain)
);

CREATE TABLE foobar_partner_map (
    username VARCHAR(20) NOT NULL,
    partner VARCHAR(20) NOT NULL,
    PRIMARY KEY(username, partner)
);

The tables in the schema are now normalized to 6NF, next, I want to consider denormalizing