I want to normalize a database schema for efficient transactions. I want to make sure all tables in the schema are in First Normal Form (1NF).<\/p>\n
There is no universal definition of 1NF; some definitions require only atomicity such that it may be impossible to violate 1NF if using a relational database such as MySQL (where there is no direct concept of tables within tables). Other definitions of 1NF restrict any attribute from containing a NULL value.<\/p>\n
Practical examples make this much easier to understand. Let’s consider the follow table:<\/p>\n
mysql> SELECT username, domain, another_domain FROM foobar_users;\n+-----------------+--------+----------------+\n| username | domain | another_domain |\n+-----------------+--------+----------------+\n| bjones | XYZ | |\n| jsmith | XYZ | |\n| bjones, hmiller | ABC | XYZ |\n| gwashington | ABC | |\n+-----------------+--------+----------------+\n4 rows in set (0.00 sec)\n\n<\/pre>\nThis table violates what a reasonable data architect refers to as 1NF; namely, a table in 1NF has no repeating columns and no repeating groups.<\/p>\n
The third row overloads the username column with two users (comma separated), and there are repeating columns domain<\/em> and another_domain<\/em>. The column order (domain, another_domain) may need to match the overloaded username field.<\/p>\n
Putting this in 1NF should look like this:<\/p>\n
mysql> SELECT username, domain, role FROM foobar_users;\n+-------------+--------+-------+\n| username | domain | role |\n+-------------+--------+-------+\n| bjones | ABC | admin |\n| bjones | XYZ | admin |\n| gwashington | ABC | user |\n| hmiller | ABC | user |\n| jsmith | XYZ | user |\n+-------------+--------+-------+\n5 rows in set (0.00 sec)\n<\/pre>\nNow let’s consider the following DDL:<\/p>\n
CREATE TABLE foobar_users (\n username VARCHAR(20) NOT NULL,\n domain VARCHAR(20) NOT NULL,\n dept VARCHAR(20),\n status VARCHAR(12),\n login_id VARCHAR(20),\n login_pw VARCHAR(32),\n role VARCHAR(12) NOT NULL,\n website VARCHAR(255),\n partner VARCHAR(20),\n PRIMARY KEY(username, domain, role)\n);\n<\/pre>\n