I've been using Postman to test out my API routes as I create them today, which I only really got to account creation and logging in (not using proper authentication methods, just wanted to make sure things were working.) At the moment, I'm using SQLite3 in order to create a functional prototype.
However, I quickly realized that the moment I try to do further regarding the full nature of my application, I would be very lost trying to freehand the database. So, here we are, trying to entirely scope out our database before implementing it.
I wanted to draw an ER diagram, but it's kinda hard to do that when you're not 100% sure what you want yet, and especially when you forgot all your pencils and can only do it in pen.
Scheming the Schema
First things first, users. We need a table of users because it links the rest of our information to the user, and that's how we know what to show a client. We want to keep track of the user's id (autoincremented for simplicity), their username, password (salted and hashed), their email, and potentially store their name.
My initial thought was to store a pantry as an weak object to a user in the database, however I quickly realized that there's no point. We should just use the user's id as our pantry id across the database.
Next, we have items. The issue with items is that I want users to be able to create and store their own items into the database, and still have them be interactable with recipes, but I also want to store and maintain a "master" list of items that are recommended to users based on their text input.
There's two approaches that I can see to this: a single table, and two tables.
The single table approach involves selecting all items with a specific user_id (the "admin id") as the Master List of items. The two table approach involves separating the master list and the user created list into two tables.
The benefits of having one single table is that it's a lot simpler to just query a single table and filter out all results that have the admin id, however querying one enormous table might not be the most efficient idea when we want our autofill to be quick and snappy for that user experience bonus.
So, what happens if we use two tables? We can simply join them when we really need to, but that might be a little complicated. Why?
We want to have a table that maps users to instances of items, but that might be really complicated the item could be on the master list or the custom item list. Suddenly, we would have to handle a lot of logic in order to make sure that we can find the correct item.
So, in order to simplify the connection between the pantry and the user and items, we should really only have a single item table. This table should contain stuff like item id, item name, expiration duration (apple lasts on average 2 weeks, etc.), and category
Next, we have recipes. These will require a recipe_id, recipe name, description, a user_id (null if created by admin), and creation_date.
Then, we can make a table that references both recipes and items. For now, we'll use the composite key of (recipe_id, item_id) as the primary key, and the other arguments will include stuff like quantity and units.
There's potential to add having friends and being able to see each other's public recipes as an option, but I really want the basic functionality to work first.
So, for today I have managed to implement the basic schema for sous' functionality. Tomorrow, I want to add the ability to access these from the API so that our front end can start communicating with it. That means writing a lot of tests I imagine.