Previously, I normalized my tables to Third Normal Form (3NF) and Boyce-Codd Normal Form (BCNF). Now, I want to continue to normalize such that the tables in my schema are in Fourth Normal Form (4NF).

4NF requires for any non-trivial multivalued dependencies X->Y, that X is a superkey. In other words, there are not multiple many-to-many relationships stored in a single table.

Most of our BCNF tables are already in 4NF, one table is not, i.e.,

-- BCNF, but not 4NF
CREATE TABLE foobar_partner_map (
    username VARCHAR(20) NOT NULL,
    domain VARCHAR(20) NOT NULL,
    partner VARCHAR(20) NULL,
    PRIMARY KEY(username, domain, partner)
);

There is no relationship between domain and partner, yet there is a many-to-many relationship between username and partner, and another many-to-many relationship between username and domain. A 4NF version of this table would like this:

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

Putting this all together, a 4NF 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_role_map (
    username VARCHAR(20) NOT NULL,
    domain VARCHAR(20) NOT NULL,
    role VARCHAR(12) NOT NULL,
    PRIMARY KEY(username, domain, 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 4NF, next, I’d like to normalize to Fifth Normal Form (5NF)