Day 5: Designing a database
Now we’ve got user stories to implement with our app, we start translating them into design, coding, and database.
Any web app, that is, any website that requires the user to log in, has a database behind the curtain that keeps the records of user information. That is why the user’s personal information can be retrieved and shown after logging in.
The first task to build an application is to design a database, that is, define what each row and each column of tables refers to.
User table
A table of users is the simplest. Each row represents an individual user. The first column indicates his/her email address. Since we plan to rely on Google accounts for logging in, we don’t need a column for passwords. Other columns may refer to each user’s information retrieved from their Google accounts such as their names. Finally, one column is dedicated to the user ID, a serial number assigned to each user when they sign up.
Place table
The core feature of My Ideal Map App is to allow each user to save the places of their interest. So a table of places is obviously needed. In this table, each row represents a place saved by a particular user. The user will provide the name of their saved place and a note on it. So one column in this table refers to place names, another to notes on the places. We also need to show these saved places on a map. For this purpose, we need to record the latitude and longitude of each place. So another pair of columns indicate the latitude and longitude, extracted from Google Maps API.
In addition, the place table should include a column for the user ID. The same place may be saved by multiple users, but we should distinguish them as each user may name it differently and most likely write down a different note on it. We treat the same place saved by multiple users as different rows with the user ID column having a different number.
Finally, each saved place is assigned the place ID.
Place type table
One major feature of My Ideal Map App is to allow the user to see only a particular type of places on the map, say, “cafes” when they get thirsty or “restaurants open in the evening” when it’s a dinner time.
To enable this feature, we need another table for user-specific place types. Each row is a particular user’s place type. Columns refer to the name of a place type, whether or not it’s shown on the map, and the color of the place mark icons for the place type. As with the table of places, we also need a column of user ID so we can differentiate a place type named “cafes” by multiple users. Finally, each user-specific place type is assigned the place type ID.
Place and Place-type pair table
A saved place can have multiple place types while a place type can have multiple places. To represent this kind of “many-to-many” relationship between two tables, we need another table in which each row represents the pair of two rows, one from one table and the other from the other table.
Columns in this table refer to the place ID and the place type ID, and each pair is assigned the pair ID.
This way, when the user wants to see their favorite cafes around, for example, our app will consult to this table and retrieve the rows in which the place type ID column has a value for “cafes”. These rows have the place ID values with which the app will then consult to the place table to retrieve the latitude/longitude, name, and note of each place tagged with “cafes”.
Entity-Relationship Diagrams
If you find it difficult to follow what I have written above, it’s not your fault. The database design is difficult to describe in words only. So app developers use what’s known as an entity-relationship diagram (ERD). For our app, it looks like this:
To understand how to read this diagram, I recommend watching the clear and concise tutorial on ERD by Lucidchart.
