Contents

The first normal form (1NF)

The first normal form states that a database table is a representation of an entity in the system you are building. Examples of entities are order, customer, booking, hotel, product, etc. Each row in the database table represents one instance of an entity. For example in a customer table each row respresents one customer.

Primary key

Rule: each table has a primary key, consisting of the smallest possible number of fields.

As you know, a primary key can consist of multiple fields. You may for example choose the combination of a birthdate, first name and last name as a primary key (and hope very hard this combination is always unique). It would be a much better choice to choose someones social security number as a primary key, because this is only one field and it uniquely identifies a person.

Beter yet, when there is no obvious candidate primary key, assign a surrogate key to your table ba creating an auto-increment id field.

Atomicity

Rule: fields are not duplicated in a row and each field contains only one value.

Take for example a website for car collectors on which every car collector that registers with the site can registers his or her cars. The table below registers the cars of the users that registered with the website.

Non-atomic design

Horizontal duplication of fields is bad design.

Duplication fields horizontally is bad design. With this design you can only store five cars and if you have less than 5 cars you are wasting database space with empty columns.

Another bad solution is putting multiple values in on cell.

non-atomic-design-2

Multiple values in one cell

The right solution here is to split off the cars into a separate car table and include a foreign key that references the user table.

Row order should not matter

Rule: The order of rows in a table should not matter.

You may be inclined to use the order of rows in a customer table to determine which customer first registered. For this purpose you should create date and time fields that store the customer's subscription date and time. Row order will inevitably change when customers are deleted, modified and inserted. That is why you should never rely on the order of rows in a table.

The next page of this database design tutorial discusses the second normal form (2NF)