Design doc review, part 2: Database Schema and Sample frontend state

[RosemaryGonzaga]

Hey Nikki, it's clear you put a lot of thought into drafting your schema and frontend state. My hat's off to you for all your great questions!

The design doc rubric is below, along with my feedback. Please let me know if you have any questions!

Database Schema

• [x] Contains correct datatypes
• [x] Contains appropriate constraints/details
  • [x] primary key
  • [x] not null
  • [x] unique
  • [x] indexed
  • [x] foreign key
• [ ] Contains bullet points after the table that state which foreign keys will reference to which table, or references to the associations which will be made
  • [x] foreign key and table name are lowercased, snake_cased and back_ticked
• [x] Correctly formatted
  • [x] schema is written in a table format
  • [x] the table's name are lowercased, snake_cased and back_ticked
  • [x] the table header column names are bolded
  • [x] columns names are lowercased and snaked_cased and back_ticked

Comments

• recipes:

  • Answers to your questions listed below the table:
    • It looks like the Yummly Developer API is no longer accepting new accounts, and at any rate, I wouldn't recommend using the API of the same app that you're cloning. You should store all the info you want to display in your own database.
    • You're absolutely right that ratings would be stored only in a separate table (i.e., in the reviews table)
    • Yes, you'll want separate columns for each of the nutrition macro types. However, to make seeding easier at the beginning of your project, it's totally fine to limit the info to a single calories column for now (you can always add sodium, fat, protein, carbs, fiber columns later)
  • [x] Remove ingredients column; these need to be stored in a separate table, and you can use a recipe_ingredients joins table to link recipes with their ingredients. (One principle of designing a "normalized" database is that each column should only store one value, not a collection of values. If a recipe can have many ingredients and an ingredient can have many recipes, we should use an intermediate join table to facilitate this many-to-many relationship.)
  • [x] Change nutrition column to calories (as mentioned in response to your question above).
  • [x] Add cuisine_id and course_id so that users can search recipes by cuisine or course. (From browsing Yummly, I gather that Yummly only classifies each recipe under a single cuisine and a single course, which means each recipe will only have one associated cuisine_id and course_id.)
  • [x] Remove icon_url. You should consider eventually storing your recipe images in a cloud service (AWS S3) and managing references to those files via Active Storage... which means you won't need to store the icon_url in the recipes table. I wouldn't worry about AWS until towards the end of the 2-week sprint; until then, you can just use locally-stored placeholder images for your recipes.
  • [x] Change description and directions to have a text datatype

• ingredients and recipe_ingredients

  • [x] Add two new tables, ingredients and recipe_ingredients
    • recipe_ingredients can look similar to the design that you proposed for your recipe_tags table (i.e, you need a way to link a recipe with an ingredient)

• cuisines and courses

  • [x] Add two tables to store the standard cuisines and courses that Yummly uses to categorize recipes.

• favorites, or user_preferences

  • [x] Add a favorites or user_preferences table. If the user profile or personalized browse feed is going to be one of your main features, you'll need a place to store the user's preferences. When a user goes through the "personalize your experience" form on Yummly, they're asked to select preferences across many different dimensions: cuisine, allergies, diet, ingredients (to exclude), cooking skills, cooking goals. You should limit it to just two few of those: cuisine, ingredients.
    • Since a user can customize their preferences for more than one type of thing (i.e., they can have both a set of favorite cuisines and a set of excluded ingredients), you should consider making this a polymorphic joins table.
    • The ArtShare API project (W6D3&4) provides an example of how to set up polymorphic associations both in your schema and in your models.
    • The Rails guides also include a primer on polymorphic associations
    • In addition to user_id, preferable_id and preferable_type columns, you may want an additional column to indicate whether the specific preference represents something to include or exclude. (E.g., include the selected cuisines but exclude the selected ingredients)

• Bonus tables

  • [x] Remove bloggers, recipe_tags, hash_tags, reviews, ,likes, flags, or move them to the very bottom of your schema plan under a "BONUS" section.

Sample State

• [ ] State shape is flat!
• [x] State's keys are camelCased
• [x] All keys within the values in the state are accessible in the schema
• [x] Correctly formatted
  • [x] Sample state is rendered with triple backticks, and the language ```javascript...```). This will display the state as a code block instead of a giant line of text
  • [x] Top level slices
    • [x] entities
    • [x] session
    • [x] errors (here or in ui)
    • [x] ui (if needed)
  • [x] Should NOT have nested slices, aka comments inside of posts
    • Some info from other tables is ok, for instance:
    • the author username and imageurl for a post. basically any info that the user can't change
    • like count and a boolean on whether the user likes the post instead of a likes slice

Comments

• Nice job normalizing your state!
• [ ] Make sure any changes you make to the final draft of your schema are reflected in the sample state as well. (e.g., if you add any tables or columns, add corresponding keys to the appropriate slice of state)
  • aside from formatting / naming conventions, there are two main differences to maintain between your database schema and frontend state:
  • [ ] Tables in a normalized database shouldn't store arrays as values, but it's ok to do that in your frontend state, as long as those arrays store ids. You have the right idea with the recipesIds key in your users slice of frontend state. However, conventionally, the keys that store an array of ids should be named as singularResourceIds, e.g., recipeIds and reviewIds.
    • When it comes time to build the recipes feature, you can think about how to send an array of recipeIds back with every request for user info. At that point, you might want to look into ActiveRecord's collection_singular_ids method, which you'll be able to use in your jbuilder. Assuming you define a has_many :saved_recipes association in your User model, Rails will automatically give you a #saved_recipe_ids instance method that generates an array of ids for all the saved_recipes associated with a user.
      • For more info, see the documentation on collection_singular_ids in sections 4.3.1 and 4.3.1.6-7 in the Rails Association Basics guide.
  • [ ] Even though you don't need an icon_url column in your database tables, you'll want to keep the iconUrl key in your frontend state. This will be generated via an association method that you define in your models. (If this doesn't make sense now, that's ok. It will make more sense after you go through the AWS S3 tutorial much later in your project.)
• [ ] To keep state normalized, the ingredients key in your recipes slice of state should point to an array of ingredientIds, not an array of actual ingredient names. The reason this matters is b/c you don't want ingredients to be listed in more than one place. (There should be a single source of truth for every piece of information.) All your ingredients should instead be listed in a separate ingredients slice of state under entities

[petiteoh]

Hi Rosemary! So much awesome feedback. Thank you so much for taking the time! Some questions on the schema below:

1. Would I remove icon_url from the Users table as well? this is meant for them to upload a picture. I am assuming yes since this would not be hosted on AWS.
2. For the polymorphic assoc. table (which terrifies me): a. do I set the preferable as a :boolean? so when selected ingredient(allergies) we change preferable to false? b. did I display the references correctly below this table? Not sure what is the convention.
3. For the time column in my recipes, would the data type be :time? or what would you recommend?

All other updates on my Schema have been reflected as per your guidance. Also, I wasn't sure if those checkboxes are meant for you to check or myself! Apologies if I overwritten some of your notes! Let me know for future note. Thank you so much and I look forward to your feedback. Will go through the State Shape portion a little later after User Auth. Cheers! :)