9.8.4 First normal formThis is NOT the latest copy of this book; click here for the latest version.
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 see this stuff in print? PHP in a Nutshell takes the core topics covered here, adds in thousands of edits from the editorial team and myself, and combines them to make an unbeatable reference for PHP programmers at all levels.
My latest book has hundreds more tips on how to use PHP, Apache, and MySQL, plus Perl, Python, shell scripts, performance tuning, and more!
|
