Alright, well let me say that for the most part, when I work with a database, there's no need for any major normalization, but I'm starting to develop bigger applications and want to go about this the right way.
I've read a couple books and countless articles, but am a bit confused on something. Let me layout the scenario.
I have a database for recipes, but let's just focus on the tables that deal with the actual recipes (title, directions, ingredients, category). Here's how I would go about this:
TBL_RECIPES Recipe ID (PK) Recipe Title Directions Category ID (FK)
TBL_CATEGORIES Category ID (PK) Category Title
TBL_INGREDIENTS Recipe ID (FK) Ingredient
That's just a shortened version, of course, as there's more information like the user who submitted it, submission date, rating, etc., but I just want to keep it simple for the sake of this post.
My confusion comes with the actual ingredents table. As you know, ingredients typically look like so:
1/2 cup kosher salt
...OR...
[amount] [unit] [ingedient]
The best way I can relate this is "First Name" and "Last Name," where "Full Name" could be considered atomic, yet "First Name" and "Last Name" would be another way of doing it (and it's how I would do it too).
Would I make a table for amounts and units as well? Part of me says yes, but then I'm unsure as to how I would set up that table, and the other part says no because it seems really overboard.
IF I did make a table for for the amount and unit, this is how I would think it would be setup (and I'll modify the other tables to fit with this setup):
TBL_RECIPES Recipe ID (PK) Recipe Title Directions Category ID (FK)
TBL_CATEGORIES Category ID (PK) Category Title
TBL_INGREDIENTS Recipe ID (FK) Amount ID (FK) Unit ID (FK)
TBL_AMOUNTS Amount ID (PK) Amount
TBL_UNITS Unit ID (PK) Unit
So for example, if I were to fill in those tables with a recipe, this is how I would suspect you would do it:
And, of course, TBL_INGREDIENTS would have, say, 10 fields in it that would correspond to one recipe; but that would mean "Recipe ID" would be a repeating value, right?
This would also mean that, in theory, TBL_AMOUNTS and TBL_UNITS would have every possible unit and amount you could have for a recipe, e.g. 1/3, 1/2, 1/4, 1 and cup, tablespoon, teaspoon, ounce, etc.
Thanks for the reply, I'll look over what you said. Also, excellent point about doing whole numbers and fractions; will also make doing US to SI much easier.
Oh, and this isn't for school, I finished that a couple years ago :)
I'm just starting to get into bigger databases and working with the InnoDB structure of MySQL 5 and interaction with PHP 5. I've always been on version 4x for both and never gave much thought on normalizing my databases, but it makes perfect sense in the long run. Just had a couple issues on how to lay out the tables, and if I was taking it too far (which, it seems, is really hard to do).
Previous Topic
