I'm building a website for which a large amount of user generated data will be stored in a back end db and will be discoverable by all users.
By its nature, a portion of the user entries will be redundant. For the sake of example, let's imagine this data is made of recipes. Suppose the following entries:
Title: Lemony dressing:
- 1 tbsp lemon juice
- 2 tbsp olive oil
Title: Lemony marinade:
- 2 tbsp lemon juice
- 2 tbsp olive oil
- 1 tbsp soy sauce
Just mix the ingredients together!
Clearly, those 2 entries have some redundant information: half the title is shared, the instructions are shared partially, one ingredient is shared exactly, another ingredient is partially shared....
Furthermore, one could easily imagine a recipe made of several sub-recipes, which can also exist on their own. So for example, you could have a marinated flank steak recipe that uses the lemony marinade as a step. So recipes can be defined recursively.
My question is how should I structure my SQL recipe database? My gut feeling is that it makes sense to store the entry "lemon juice" in an Ingredients table, the quantity "2 tbsp" in a Quantity one, and then use their keys in the Recipe table.
But how far should I go? Should I split "lemon juice" into two entries, in order to prevent duplication with "orange juice" and "lemon zest"? After all, ingredients can also be defined recursively. So should I store "lemon", "orange", "juice", and "zest" separately and only assemble them ("lemon juice", "orange zest", etc) later? Or should I store "orange juice" and "lemon juice" knowing that I am duplicating "juice"? Is it a function of word size?
But then instructions on how to use the ingredients are going to have some overlap. Does it make sense then to store each word separately? Why not letters?
I'm not sure how to consider the trade-offs like this. I suppose that if I minimize the redundancy of the info in the tables, there must be some cost. Is it performance wise, or is it a cost paid at conception?
PS: This question is not meant to ask for a specific implementation of a recipe db. It's a more general question on how to store info that is partially duplicated.