Database schema of GroceriStar (current release)
Attaching this schema again. Yep, I know — it is not ideal. Do you have any ideas, how we can make it better(example — I think favorite part is not good).
Let’s discuss this here.
Main Topics
- Ingredient Model
- Item Model
- Favorite Attribute/Field
- Purchased Attribute/Field
Ingredients
I want to store ingredient name. For example = “milk”. But we should remember, that “non-fat milk” — is a separate ingredient.
We need to keep in mind that we have a lot of functionality, related to GROUP methods. Like displaying all ingredients at departments, merging same ingredients, displaying only purchased things, etc.
We need to have the ability to move ingredient between departments.
How we can handle this flags: is ingredient healthy or is an ingredient for vegans?
Departments
Each Grocery List has Departments with ingredients collection.
The user has the ability to hide(but not delete) Department with all ingredients, related to this department. I think this part should be improved too.
Pre-defined departments and their editing.
Case: We have default ‘Dairy’ department at our Ultimate GL. But user wants to change it to name ‘Milk products’. We’ll need to create a new department, that only visible and can be related to our user, and move all items, that related to that department. And we need to remove id of previous, ‘Dairy’ department.
Grocery table
Grocery table can eliminate Description field as it is already there in ingredient table.
The userfav table can have just GroceryId column and no ingredients_id column. As Grocery table is already having ingredientId as a foreign key.
User table can eliminate ingredientId field as it has a groceryId field as the foreign key. And it can have an itemId field added as a foreign key.
GL connects with user_id. Maybe we need to create a table between this tables? Will this make our cloning functionality easy and clear?
Favorites
Is this field part of Ingredient Model(just name, like pepper). Or to Item Model(ingredient name field, the quantity field, price field)?
I think this attribute is related to User Model and Ingredient Model (not Items)
Favorite and purchased is a quite similar thing. But first is related to a user(you can list all favorite ingredients or maybe items at user profile), purchased always related to grocery list — and you can list all purchased items by Grocery List Id.
Maybe later when a user goes to list of favorite items, pick one of them -> he’ll receive a list with purchases of this item, that was previously made.
Purchased
When the user buys the same thing more than once. How will it be handled in Purchased?
If this will be stored at different grocery lists — there no problem. If we add one ingredient(i.e. milk) twice — we’ll calculate them automatically. so 2 rows became one 1.3L of milk(if milk is similar).
Purchased is hardly relate to grocery list id. So, if you open grocery list and click — purchased — you’ll see only items, related to that list.
If you open ‘Purchased’ an account — you’ll see a collection of grocery lists with items, related to that lists.
But my question was purchased is the list of items stored somewhere. So every grocery list might be stored differently. Right? So what if a user buys the same item with same quantity on different dates. So there will be the same row stored twice. just with a different date. Rest of columns will have same values.
Example: “1 L milk” on “03/12/2018” by user “ Jane Doe”
“1 L milk” on “03/13/2018” by user “Jane Doe”
I got it. if you want — we can create a more advanced schema, that will reduce situation like this.
How item and ingredient are different?
Items can be purchased or not purchased. Maybe we’ll not save items, that were not purchased into payments history.
Items should have order field — so we can keep the history of sorting.
They also should have a favorite flag. Do we need to have a connection between the purchased item and grocery list id? or we’ll just query items with purchased flag=true?
Items should have price field. For now, we didn’t care about currency.
An ingredient is a model that used at current GS release. Each time when the user adds an ingredient to the list — we have a new row.
Item is a model that I plan to use in the next releases. Main goal: add measurements and flags/attributes to an ingredient. So each item can be milk (Ingredient) + 1 L (Qty and measurement) + 1 (ingredient stored at favorite list) + 1 (item is purchased at current shopping list)
So item have more advanced functionality
We should keep in mind, that later we’ll need to add
- measurements (1L, 1lb, 300ml)
- unit conversion(from gram to kilogram)
- ingredient/items calculations(1kg + 1/4 kg = 1.25kg). Also, we’ll work with different small fractions of units.
Case: you have 1L of milk on your grocery list when you add another 1L of milk again — as result, you must have 1 row at list => 2L of milk - Prices and expense tracker
How to store completed, old GL?
What data should we save on favorite items?
Case: if I buy “1 L milk” and “300 ml of milk” — both will be purchased. And I add the first item to favorite. Then second item goes to favorites too. Do we need to have both this records in favorite or maybe save just `milk`?
Thank you for reaching the end
