An Introduction to Database Normalization (part 2) - Querying the MySQL database (
Page 3 of 4 )
As you
likely already know, interaction with an SQL database takes place through
queries. A query is nothing more than a request for information from a database,
this request being a phrase constructed from various keywords and table / column
references. In this section, I’ll demonstrate using queries how database
normalization makes an administrator’s life considerably easier. To begin,
assume that the news table has been filled with
this information.
Using this information, assume I
execute the following query:
mysql>SELECT n.title, a.name FROM news n, administrators a WHERE
->n.admin_id = a.admin_id AND a.admin_id = 2;
Resulting in:
| title |
name |
| Nuevo Sitio: www.ziobudda.net |
Michel |
Knowing that the admin_id ‘2’ maps
to ‘Michel’, we can build our query without worrying that Michel will be
misspelled. Furthermore, if the administrator later decides to changes his name
to just the initials ‘M.M.’, the query will not need to be changed because the
criteria is based upon an admin_id and not a name. Additionally, this name
change will not require the consumption of potentially magnanimous amounts of
resources, as would be the case if the name were included along with each row of
the
news table.
Considering another example, consider the
query:
mysql>SELECT n.title FROM news n, categories c WHERE
->n.category_id = c.category_id AND c.category_id = 2;
This yields:
| title |
name |
| Nuovo Sito: www.phpitalia.com |
New Sites |
| Nuevo Sitio: www.ziobudda.net |
New Sites |
Now suppose that I want to update
one of the category names found in the categories table, in particular I want to
change the name ‘New Sites’ to ‘Great Sites’. All that I need to do is update
one row found in the
categories table:
mysql>update categories set name = ‘Great Sites’
->where category_id = ‘2’;
Again, executing the previous SELECT query would result
in:
| title |
name |
| Nuovo Sito: www.phpitalia.com |
Great Sites |
| Nuevo Sitio: www.ziobudda.net |
Great Sites |
Of course, your Web application is not likely to repeat the category
name, but this will certainly make a difference when using dynamically-named
table headers.
/ 8 
