Building a Web Application: Database Design

Once we have our Requirements completed, we can begin modeling our data. Developing a data model can be a very straightforward task. Somewhat like programming, database design is “object oriented”. You determine what your objects are – otherwise known as entities – and then define the relationship between those entities.

The diagram that you will end up with is called an Entity Relationship Diagram, or ERD for short.

Entities

The first step is to define the entities. Break it down based on how you expect to store information. Anything that is a noun is likely to be an entity. Everything else will be attributes. Tasks, for example, are an entity. User Preferences on the other hand are descriptive. They describe Users. As a result, we’ll have a Users entity that stores user preferences.

Once you have all the entities defined, we can move onto defining the relationships between them. In doing so, we’ll likely find other entities that have to be created to pull off what we need.

Here's my quick list of entities. It's not everything that will end up in the final list of tables but it is a starting point for the next step.

Relationships

Items can be related to each other in various way:

• one-to-one
• one-to-many
• many-to-many

In a situation where an entity has a one-to-one relationship in both directions, chances are it can be combined into one database table. For instance, each site only has one site owner. Therefore, site owner information could be contained within the site table.

Many-to-many relationships cannot be represented in a relational database and will need to be resolved into one-to-many relationships. To illustrate this point, we have Users and we have Groups. I want a user to belong to more than one group. Likewise, a group can contain more than one user. As a result, we create a lookup table that stores a key from both tables.

The one-to-many scenario is the most common scenario. Examples:

• a milestone may have many tasks but a task will only have one milestone.
• a user may have multiple to-do items but a to-do item will only belong to one user.

Refine your relationships. Review your diagram, resolve many-to-many relationships and remove redundant relationships.

Here's the ERD with the relationships mapped out. Notice how I've resolved my many-to-many relationships into lookup tables. I dropped the Sites entity which I'll discuss further in my next entry.

Defining attributes

Now that we have our entities and how they are related to each other, it’s time to set the attributes of each entity. These attributes will be the field names in the database. Going back to our User entity, we can define attributes such as name, address and phone number.

Normalization

Normalization is the process of reducing the amount of duplicate types of information in a table. For example, let’s say we wanted to store multiple phone numbers for a user. We might have a work, home and cell number. Instead of storing this information as three fields in one table, we can move the information to a separate table and then create a relationship with the user table. In doing so, we can now add an unlimited number of phone numbers instead of limiting ourselves to three.

Read more

Admittedly, this is a basic overview of these concepts. For more information, I highly recommend reading up on the following topics:

• Introduction to Data Modeling
• Entity Relationship Diagrams
• Normalization
• Data Dictionary - Data dictionaries are text documents that describe in detail what each
• UML – UML is a Unified Modeling Language which can be used for data modeling.

Also, software such as Visio can help you develop ERD’s and will automatically export the information into the database server of your choice.

Next up, I discuss some of the database design decisions that I’ve made and why.