[This is an excerpt of the Silenceit Database seminar.]
Any serious web developer understands the importance of a strong database design for even the simplest of web applications. An incorrectly structured database leads to additional complicated code, poor database engine performance and can become a complete nightmare upon updating, upgrading or maintaining a program. This article will cover some of the basics of database design and relevant tools.
Database components can be broken up into two categories:
- Objects (Examples: people, items, model…); and
- Relationships: “A connection between 2 objects, unique or not”
Relationships can be categorized into three types:
- one to one (1:1) (John Doe has 1 Social insurance number);
- one to many (1:n) (John Doe has several phone numbers); and
- many to many (n:m) (Several people work in various departments)
These relationships usually dictate the breakdown of the database tables. Most, if not all (1:1) relationships can be represented in one table, each item becoming a column. All (1:n) relationships require two seperate tables, one for each object. The (n) object table always contains the id column that relates back to the (1) object table. Finally, (n:m) relationships require a third table that connects the two objects.
Now, that might appear as clear as mud so lets go over a couple examples:
Example 1: People and Phone Numbers
Starting with people, they generally have one given name, one surname and one social insurance number (SIN). They may however have several phone numbers:
- People
- Given Name (1:1)
- Surname (1:1)
- SIN (1:1)
- Phone number (1:n)
Therefore, the following table structure would be correct:
People
| peopleId | givenName | surname | SIN |
| 1 | Bob | Hardy | 123456789 |
| 2 | John | Smith | 987654321 |
Phone Number
| peopleId | phoneNumber |
| 1 | 613-783-3456 |
| 2 | 613-987-2345 |
| 1 | 819-345-7584 |
| 2 | 536-234-9879 |
*underline denotes primary id.
Some may argue that it is much easier to have one phone number column and simply divide the numbers with some type of delimeter such as a pipe (|). I strongly recommend against this as it presents several problems: the most important being that it cuts out the built-in database engine functionality. It would require the programmer to write special functions to create/edit/delete that one field, reducing performance as well as introducing another piece of code to debug.
Example 2: Company Projects
This example also has two objects, but they are related via a third table due to a (n:m) relationship. This example is meant to illustrate several employees that work on several projects. One must keep in mind that in this case, more then one employee can work on the same project and a single employee can work on several projects. I’ve left out the various (1:1) fields that may have been associated with each object.
Employees
| empId | givenName | surname | sin |
| 1 | Bob | Hardy | 123456789 |
| 2 | John | Smith | 987654321 |
Projects
| projectId | Name |
| 1 | Taking over the world |
| 2 | spreading gossip |
| 3 | Website |
| 4 | Stamps |
EmpToProject
| empId | ProjectId |
| 1 | 1 |
| 1 | 3 |
| 2 | 2 |
*Note: Some People may be more useful then others.
Database Diagrams
The examples illustrated were quite simple and did not require a database diagram to correctly visualize the database. However, most system are much more complicated and require a programmer to lay the database out on paper. There are various ways of doing this: entity relationship and uml diagrams are good examples (a quick image search will return several). Many programs exist to complete this, but if your a MySQL user like myself, I recommend MySQL workbench. Once you’ve completed the diagram, you can simply export it into actual sql queries to create the database, and the reverse is also supported.
