Database Schema, Write queries

[JessePeplinski]

Schema can be found here: https://console.firebase.google.com/project/kitchen-assistant-8b1db/database/kitchen-assistant-8b1db/data

Resources

• Video Series - SQL to Firebase
• Structure Data
• Save Data
• Retrieve Data

Todo

• Create initial schema
  • Add users? article here
• Flatten and refine schema
• Decide on endpoints
• Import dummy data to DB
• Upload query.js file to github api repo
• Test queries against database

Write Queries

Consideration: How do we account for duplicate locations? Not allow them? Or append something to the end of them? Same goes for items.

Don't allow duplicate locations, but add a count field to the schema for items.

• Locations

  • List all locations
  • Add
  • Update
  • Delete

• Items

  • List all items
  • Add
  • Update
  • Delete

• Expiration Dates

  • List all expiration dates
  • Add
  • Update
  • Delete

• Grocery List / Cart

  • List all items in cart
  • Add
  • Update
  • Delete
  • Purchase

Zach's Proposed Schema

JSON Representation

Use this website to validate and format JSON.

{
	"user_id": "SOME-BIG-ID",
	"locations": {
		"fridge": {
			"items": {
				"milk": {
					"expiration_date": "12/12/2022",
					"food-group": "dairy",
					"other_field": "datadatadata"
				},
				"butter": {
					"expiration_date": "12/12/2022",
					"food-group": "dairy",
					"other_field": "datadatadata"
				}
			}
		},
		"pantry": {
			"items": {
				"bread": {
					"expiration_date": "08/08/2020"
				}
			}
		}
	},
	"groceryList": {
		"eggs": {
			"price": 2.99
		},
		"chicken": {
			"price": 8.99
		}
	}
}

Endpoints

Brackets <something> indicate uniqueness.

/locations/

List of all locations.

{
     "TODO": "Example of endpoint here"
}

/locations/<location-name>

List of a specific location name.

{
     "TODO": "Example of endpoint here"
}

/locations/<location-name>/items/

List of all items at a location.

{
     "TODO": "Example of endpoint here"
}

/locations/<location-name>/items/<item-id>

List of the details of a specific item.

{
     "TODO": "Example of endpoint here"
}

/grocery-list/

List of all items in your shopping cart / grocery list.

{
     "TODO": "Example of endpoint here"
}

Q: Do we want these separate (list / cart)?

Zach comments from email

Zach: Giving each item its own sub collection may or may not be the best way to go about storing this as the user may not always input item details, something to discuss.
Jesse: Assume the user won’t do anything you expect them to. We can discuss later.

Zach: Lastly there is a grocery list….should discuss what happens after the user goes grocery shopping, do they delete their whole existing grocery list afterwards, or do we save it for future reference? If we save it, we will need to add a datefield to grocery list and then let user choose latest grocery list or previous one.
Jesse: Save it. They should be able to see previous shopping history. If time, make predictions on what they will buy next. Probably out of scope.

Zach: Maybe we pull some item details from a nutrition app or something (maybe something like myfitnesspal has an API). We could pull details like food group or carb/protein/fats count. Having that data could be allow for some fun data analysis. For example the user will be able to say “okay google how many grams of protein are in my fridge”. Not useful for every user but a cool feature for those who like it…also gives you some cool data analytics you wouldn’t otherwise know.
Jesse: Love it. Let’s do it. Adding to issues.