Let’s try designing a database for a photo sharing application. The basic functionality for this application includes users that make posts, showcasing one or many photos per post. Other users can add comments to each post. Believe it or not, just those two sentences of functionality will result in a moderately complex database.
As was mentioned in the last challenge, there should be a table for each “kind” of record in our database. Tables contain uniform data—so the things that go in each table should all be similar.
Determining the first few tables is usually fairly simple. We need a table for each entity, or object, that was mentioned in the description of the application’s functionality including users, posts, photos, and comments. We’ll need the following tables for each category of users, posts, photos, comments.
Let’s talk about the records we might find in each table.
- User ID: A unique numeric identifier for each user.
- Email: User's email address.
- Password Hash: Aversion of the user's password that we can use to verify the user can login to their account.
- Username: Name to be associated with the user's account.
- Post ID: A unique numeric identifier for each post.
- Description: The text that the user will associate with the post.
- Photo ID: A unique numeric identifier for the photo.
- Location: The place where the photo is stored on our computer.
- Comment ID: A unique numeric identifier for the comment.
- Comment Text: The text of the comment.
Now that there are buckets, or tables, for the data in this photo sharing application, we are able to store data when users interact with our application.
For example, when a user registers, a user record can be added to the user table. When a user adds a post, we are now able to add a post record to the post table, as well as one or several photo records to the photo table. When another user comments on a post, we have a place to store comment data for each comment.
You may have noticed a problem with the tables and fields we’ve created. According to the fields in our various tables, there is currently no way to determine which post belongs to which user, which photos belong to which post, which comment was made about which post, nor which comment was made by which user.
In order to store this kind of data, we need to find a way to acknowledge and store the relationships between the tables.
We can store data about the relationships between tables by adding fields to our tables that refer to another record in another table. For example—to represent the relationship that “each post belongs to a single user” we can do the following:
|User ID||Password Hash||Username|
|Post ID||Description||User ID|
We’ll replicate this for the post/photo relationship as well.
|Post ID||Description||User ID|
|Photo ID||Description||Post ID|
This process of fields referring to fields in other tables allows the tables to relate data across the database, while still keeping “like” data together, in its own separate table. All photo records can exist in a table where fields are solely about photo data, but we can refer to the Post ID field in the photo table to understand which post a given photo belongs to.
Database design is a highly complex task that takes a lot of planning and evaluating. Determining the right tables, fields, and relationships is not as straightforward that one might assume, but once data is represented correctly within a database, a web application has a solid foundation to build upon.
Hopefully, you’ve gotten a feel for the kind of critical thinking that’s needed to design a database well. It’s one of the many tasks for web developers that requires very little coding, and instead, careful thought, planning, and collaboration.