Determining the App’s Functionality and Structure - Realtime Web Apps: With HTML5 WebSocket, PHP, and jQuery - page 73

• A unique identifier for the question

• Whether or not the question is answered

• The number of votes for the question

Database Normalization
In order to keep the database clutter-free and eliminate duplicate information, the database needs to be normalized. 3

WhY DataBaSe NOrMaLIZatION IS IMpOrtaNt

the easiest way to show why database normalization is important is to look at a simple example.

imagine a database for a college that needs to track its professors and what classes each one is currently
teaching. it needs to store the professor's name, email address, date of hire, and iD of the class they teach.

if the database is not normalized, it might look something like table 5-1 .

Table 5-1. Table That Has Not Been Normalized

Name Email Hire_date Class

Jane Doe

[e-mail]

2004-08-09 ECON-101

Tom Jones [e-mail] 2007-01-15 MATH-315

this may not look wrong at a glance, but when you start manipulating the data, things start to get a bit wonky.

For example, what happens when a new professor is hired, but doesn't have a class yet? there would be an
empty column for that entry, which could cause issues for any applications that read this data.

Conversely, what if a teacher teaches two or more classes? there would be duplicate data for all but one of the
columns (see table 5-2 ).

Table 5-2. Non-Normalized Table with a Professor Who Teaches Two Classes

Name Email Hire_date Class

[e-mail] Jane Doe 2004-08-09 ECON-101

[e-mail] Jane Doe 2004-08-09 ECON-201

[e-mail] Tom Jones 2007-01-15 MATH-315

there's also the issue of deleting info: if tom Jones stops teaching math-315, and that row is removed from the
database, his other info is lost as well, which probably wasn't the desired outcome.

http://en.wikipedia.org/wiki/Database_normalization

3