Normalization, 4NF

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)

This entry was posted in data arch., mysql. Bookmark the permalink.

Comments are closed.