Postgresql – Multi user, allow user to read data and add their own data

database-designpostgresql

I am trying to create a recipe database that can have many users. Each user can enter and save their own recipes, which only they have access to. The recipes are made up with various details including the ingredients that the recipe will use. The ingredients can come from 2 sources, the main 'read only' ingredient list provided by the app or from any 'custom' ingredients that the user has created (likely because the ingredient they wanted was not available in the list provided by the app). In each case the same information is stored about the ingredients (i.e. nutritional information etc.)

So a currently the schema looks something like:

User

  • id
  • name

Recipe

  • id
  • title
  • userId

RecipeIngredient

  • id
  • ingredientId
  • quantity
  • recipeId
  • userId

Ingredient

  • id
  • name
  • nutritionalInfoId

nutritionalInfo

  • id
  • calories
  • fat
  • protein
  • carbs

Category

  • id
  • name
  • userId

Recipe__Category

  • recipeId
  • categoryId

What is the best way to handle this? I did start going down the route of adding a UserIngredient table:

UserIngredient

  • id
  • id
  • name
  • nutritionalInfoId
  • userId

However, this made things more complicated when it came to assigning an ingredient to RecipeIngredient as it was either a relationship to Ingredient if the User had picked one of the apps 'built in' ingredients from the Ingredient table or one of their own from UserIngredient. It felt like there was probably a better way to handle this?

Best Answer

There are many ways to model this, but I would choose a single ingredient table that contains all ingredients. Add a column user_id that is a foreign key to the table of users. Add a special "public" user (perhaps with id 0) that owns all the public ingredients.

Then you could create a unique constraint on ingredient(id, user_id) and reference that in the recipeingredient table. The only drawback is that you will need a second user_id column in recipeingredient that references recipe(id, user_id). Perhaps call one user ID column recipe_user_id and the other ingredient_user_id.

Then it is easy to add a check constraint on recipeingredient that makes sure that (recipe_user_id = ingredient_user_id OR ingredient_user_id = 0), so that nobody can use somebody else's private ingredients.

Related Question