Redundant / partially duplicated data in a DB - how to structure?

Question

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
Just mix!

and

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?

Thank you,

JDelage

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.

Answer 1

You might get better luck on stackoverflow as you've not asked a sys admin question.

The recipe examples don't really give any useful pointers on what you're actually trying to do. If you're after info about removing duplication from a database you should look up normalization and go from there. It's a well known process.

If you're this unsure of how to use mysql with your data you need to reassess what you're doing and make sure you're picking the right tool for the job before you end up going off in the wrong direction, only to regret it later.