First normal form

The first stage in normalising your table is called "first normal form", usually abbreviated to 1NF. To convert your database to 1NF, you need to identify repeating groups in your tables and split them off into new tables. For example, a table "books" might have several authors - in an unnormalised form (UNF), you would need to either have one row for each author, or you would have an attribute "Authors" containing the names of all three authors.

Splitting authors off into their own table would allow you query more accurately. The following table shows a table, bookauthorlist, containing book numbers and authors:

+--------+-----------------+
| BookNo | Author          |
+--------+-----------------+
|    100 | Jim Sansom      |
|    110 | Fred Neerlingen |
|    110 | Sara Neerlingen |
|    113 | Peter Brown     |
|    114 | Jim Sansom      |
|    114 | Timothy Wright  |
|    114 | Henry Sanders   |
+--------+-----------------+

While that is a little better than having multiple rows in a books table or having all authors crammed into one field, it is still not perfect. To truly be 1NF, Author would be identified as a repeating group, and would be split off into its own table, "authors". Here each author would only appear once, with an ID number.

So, we would have books, bookauthorlist, and authors. Books would have every book in there, with an ID number for that book. Bookauthorlist would contain an ID number for a book and an ID number for an author, which each combination having its own row. Finally, authors would contain each book author, along with an ID number in each.

Once properly in 1NF, the database is much, much more powerful - for example, as we know the unique ID number of any given author, as well as having a list of all the books that author has been involved in, we can create queries that show all books by an author. That kind of flexibility is not possible using UNF tables, so you should at the very least aim for 1NF. 2NF, however, is even better...

Want to learn PHP 7?

Hacking with PHP has been fully updated for PHP 7, and is now available as a downloadable PDF. Get over 1200 pages of hands-on PHP learning today!

If this was helpful, please take a moment to tell others about Hacking with PHP by tweeting about it!

Next chapter: Second normal form >>

Previous chapter: Why not separate data?

Jump to:

Home: Table of Contents