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

2NF requires that all non-key attributes depend on the whole of the key and not a subset of a compound key. The 1NF example can elucidate:

-- 1NF
CREATE TABLE foobar_users (
    username VARCHAR(20) NOT NULL,
    domain VARCHAR(20) NOT NULL,
    role VARCHAR(12) NOT NULL,
    dept VARCHAR(20), --depends on user (but not domain or role)
    status VARCHAR(12), --depends on user (but not domain or role) 
    login_id VARCHAR(20), --depends on user/domain (but not role)
    login_pw VARCHAR(32), --depends on login_id (transitively to user/domain)
    website VARCHAR(255), --does not depend on role
    partner VARCHAR(20), -- does not depend on role
    PRIMARY KEY(username, domain, role)
);

This table is un-normalized and highly inefficient. Indexes would need to be added to keep query performance reasonable, indexes that in a normalized schema would not be necessary. Furthermore, because of the attribute dependencies, update statements will be considerably difficult if you wish to maintain data integrity.

To normalize this table, separate the attributes that are not functionally dependent on the primary key into separate tables, i.e.,

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

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

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,
    login_pw VARCHAR(32),
    PRIMARY KEY(username, domain, login_id)
);

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