Which fields in my database diagram should I separate into relation tables?

Question

I am struggling with the issue of trying to understand whether or not data should be separated into different tables or kept within the same, in a database logical diagram.

My database is split into 3 parts: users (can be registered users who can post, view or like recipes; and admins who review published recipes and can accept or block users), recipes (which contains data like: dish name, category, dificulty, duration, servings, execution, view count and state) and ingredients.

Recipes and ingredients have a many : many relationship so I have connected those two with a relation table called "Recipe_Ingredient".

Users to recipes has a 1 : many relationship (one user can create many recipes, but each recipe can only be created by one user), but also a relation table connecting them because of the favorites section (i.e. one user can have many recipes as favorite, and each recipe can also be flagged as favorite by many users).

But my actual problem is: I have separated from the table recipes a few fields (see image below for reference) such as Category, dificulty, and state. Is this acceptable or are these fields better off kept within table recipes?

Photo of what I have so far, for reference

Sorry for the long message. Thank you so much for your help!

Answer 1

I think the tables as you have them are mostly fine because as your data increases that's when you see the value of the design. Here are a few ideas you can use for making these decisions if looking for optimizations:

1. Which tables are simply used for referential integrity with set values that will never change? I would think state and difficulty. The values in the recipe table like state should just show the state value so a lookup isn't needed. Sometimes these types of values that will not change end up in name-value pair tables(fieldname, fieldvalue, fieldtext) and the integrity handled on the front-end. Of course this means that when I look at the recipe table, I can see the difficulty and state right away without going to look up the value elsewhere.
2. If you had to move this into reporting or say analytics, do you have enough descriptors for an end-user to use? This might be a good reason to keep the description for difficulty.