Continuing on the Computer Science Pro track on Codecademy, I just finished the Databases module which was taught in PostgreSQL. For my 3rd portfolio project, I made a database from scratch for boba!
The requirements were:
- Research the topic of your choosing to create a entity relationship diagram for your database
- Create the schema of the database using PostgreSQL
- Populate your database with some data
- Update your database to ensure your data stays intact. This could include adding constraints and roles
- Update your database to ensure your database stays fast. This could include adding indexes, normalizing your database, and actively monitoring and maintaining your database.
A week or so ago, Codecademy had assigned a similar project. So I already had a starting boba database that would be perfect for Objectives #3 and #4.
The Original Database
When I first brainstormed how I wanted to set up the database, I thought about what main purpose I wanted it to serve. We already have Google Maps which contained all the relevant info of where boba shops were and when they would be open. But I realized it was a little harder to search for a specific boba drink. What if I was in the mood for boba with agar agar pearls? I knew from experience to go to Gong Cha. But what if there were no Gong Cha around? (Believe me this happens sometimes!).
So, the purpose of this database is to make it easier for people to find a specific boba drink.
I started with a quick list of the tables I would need:
shops, drinks, toppings
I decided to make a separate schedules table because I thought the info would be hard to edit, or query, if it was just stored as one text column in the shops table.
I also made a menus table and a menus_items table to address the many-to-many relationship between drinks and shops because I assumed there would be overlap. Drinks like classic milk tea could be found at multiple stores. Toppings like pearls, lychee jelly, etc. would not be specific to one store either.
After making these tables, I inputted three shops and parts of their menu to test out the queries. That was the end of the initial project requirements from Codecademy (Objectives #1 to #3).
v1 schema:
https://dbdesigner.page.link/mNtY4XqKyuy1Ss4Y7
Improvements / Changes
After the initial database was done, I continued through the rest of the Database module - learning things like indexes, security, storage and speed, etc.
I realized I was probably making my database too complicated - especially on the data entry side. I was inputting a menu id, drink id, and topping id into menus_items which resulted in a lot of unique drinks that were probably not necessary to someone's search. (Does anyone really need to know all the possible combinations for a classic milk tea and more?)
I also received feedback from a vegan friend who was more interested in knowing which shops offered dairy alternatives.
I started with a dairy_alternatives table that was sort of like a menu add-on. That made me realize I should have treated the toppings table as a menu add-on too.
I decided to scrap the whole menus_items table and made three separate tables to address the drinks, toppings, and dairy alternatives (if any).
In this new schema, people can still search for drinks. The toppings and dairy alternatives would just be separate searches. I figure this setup would be more useful and help manage storage over time (imagine I was actually trying to put tons of shops' menu data into this database rather than just 10 shops' data).
Anyway, you can check out the schema and code below where I also did some EXPLAIN ANALYZE for query searching as well as looking at storage use and indexes. I know I have a long way to go, but hopefully this second version is an improvement on my original database!
v2 schema
https://dbdesigner.page.link/DWgXQcZ1tWev71kLA
Git
https://github.com/pwong09/postgreSQL/tree/main/boba_database
Top comments (0)