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)