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

5NF requires that every join dependency in a table is implied by the candidate keys.

All of our 4NF tables meet the 5NF requirement, but when we consider the implicit functional requirements, one table fails 5NF:

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

This table is in 4NF since there are no non-trivial multivalued dependencies. However, there is a join dependency not implied by the candidate keys, i.e., {{username, domain} {username, role} {domain, role}}. In other words, if a user is granted access to a role in a domain, then the user has that role in all domains that support that role– not all roles are supported by all domains. It is this particular caveat that causes an otherwise normalized structure to fail 5NF.

A 5NF version would look like this (note that the foobar_domain_map in the 4NF article covers the {username, domain} join dependency):

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)
);

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

CREATE TABLE foobar_users (
    username VARCHAR(20) NOT NULL PRIMARY KEY,
    dept VARCHAR(20) NOT NULL,
    status VARCHAR(12) 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 5NF, next, I’d like to normalize to Sixth Normal Form (6NF)