Previously, I normalized my table to Second Normal Form (2NF). Now, I want to continue to normalize such that the tables in my schema are in Third Normal Form (3NF).

3NF requires that every non-prime attribute is directly dependent on every candidate key. Fortunately, normalizing to 2NF accomplished this for all but one table, i.e.,

-- 2NF, but not 3NF
CREATE TABLE foobar_logins (
    username VARCHAR(20) NOT NULL,
    domain VARCHAR(20) NOT NULL,
    login_id VARCHAR(20) NOT NULL,
    login_pw VARCHAR(32),
    PRIMARY KEY(username, domain)
);

-- already 3NF (status and dept directly depend on username)
CREATE TABLE foobar_users (
    username VARCHAR(20) NOT NULL,
    dept VARCHAR(20) NOT NULL,
    status VARCHAR(12) NOT NULL,
    PRIMARY KEY(username)
);

-- already 3NF (there are no non-key attributes)
CREATE TABLE foobar_partners (
    username VARCHAR(20) NOT NULL,
    domain VARCHAR(20) NOT NULL,
    website VARCHAR(255) NOT NULL,
    partner VARCHAR(20) NOT NULL,
    PRIMARY KEY(username, domain, website, partner)
);

-- already 3NF (there are no non-key attributes)
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)
);

There exists a transitive dependence on the login_pw, i.e., the login_pw does not depend on the username/domain primary key except transitively through login_id. This violates 3NF and this table can be normalized as follows:

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

The other tables are technically in 3NF but one is not in Boyce-Codd Normal Form (BCNF). BCNF is a slightly stronger version of 3NF. A table in 3NF would need partial overlapping keys to even possibly not be in BCNF. One of our tables exhibits exactly this issue, and intuitively it doesn’t look normalized (even though it technically meets the 3NF requirement), i.e.,

-- technically 3NF, but not BCNF
CREATE TABLE foobar_partners (
    username VARCHAR(20) NOT NULL,
    domain VARCHAR(20) NOT NULL,
    website VARCHAR(255),
    partner VARCHAR(20)
);

In this case, a website and a partner are slightly related in that a partner is defined by a website registration. For the sake of brevity, a BCNF compliant version of the above table would look like this:

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

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

Putting this all together, the BCNF compliant schema looks 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_partner_map (
    username VARCHAR(20) NOT NULL,
    domain VARCHAR(20) NOT NULL,
    partner VARCHAR(20) NULL,
    PRIMARY KEY(username, domain, partner)
);

The tables in the schema are now normalized to BCNF, next, I’d like to normalize to Fourth Normal Form