1NF: Don't Kill the User
Normalization involves a series of rules. The first (i.e., most important) rule is that all data must be accessible via a primary key. You should not kill your user by making them do extra 'programming' work to access the data. The user should not have to know how to parse a compound field in order to access the piece of data they need. The user should not have to know how to loop though the items in a list to find the one they need. The user should not have to access a whole bunch of similar fields to find the value they need.
The implications of the first rule are:
- Every table should have a primary key.
- All compound fields (e.g., Name='John Smith') should be split into separate fields (e.g., FirstName='John' and LastName='Smith').
- All comma delimited fields (e.g., Address='1234 Main St., C, Austin, Tx, 78712' should be split into separate fields (e.g., Street='1234 Main St.', Apt='C', etc).
- All lists (e.g., Dependants='Jane, Timmy, Darlene and Doreen') should be moved into a new table (with one record per item in the original list). The new table should be related back to the original table by a foreign key.
- All repeating fields (e.g., Degree1='High School', Degree2='BA', Degree3='JD', Degree4=Null, Degree5=Null) should be moved into a new table (with one record for each of the original non-null repeating fields. The new table should be related back to the original table by a foreign key.
A database is said to be in first normal form (1NF) if all of the tables satisfy the first rule.