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

Normalizing the Database

to make this database more manageable, it should be normalized, which would break the data into two tables:
one for professor data (see table 5-3 ) and one to map professors to the classes they teach (see table 5-4 ).

Table 5-3. Professors Table Without the Class Information

Name Email Hire_date

Jane Doe

[e-mail]

2004-08-09

Tom Jones

[e-mail]

2007-01-15

Table 5-4. New Table Maps Professors to the Classes They Teach

Professor Class

Jane Doe ECON-101

Jane Doe ECON-201

Tom Jones MATH-315

By splitting the data into two tables, it's now possible to remove tom Jones as the teacher of math 315 without
losing his personal data, and it's also possible to have Jane Doe teach two classes without duplicating all her
other data.

this table still isn't ideal, however, because it presents difficulties in updating the professor's name. For instance,
if another teacher called Jane Doe is hired, how do you uniquely identify which one was being referred to in the
table that maps professors to classes being taught? it could be further normalized to use a numeric iD for the
professors rather than their names, which solves that issue (see tables 5-5 and 5-6 ).

Table 5-5. The Professors Table with an ID Field Added

ID Name Email Hire_date

[e-mail] 1 Jane Doe 2004-08-09

[e-mail] 2 Tom Jones 2007-01-15

Table 5-6. Class Info Table Now Uses the Professors' IDs Rather Than Their Names

Professor_ID Class

1 ECON-101

1 ECON-201

2 MATH-315

at this point, this database is adequately normalized and shouldn't present any issues when adding, modifying,
or deleting data in the future.