structure of a table

[ducker]

I was having a discussion with a friend of mine about this... and I couldn't come up with the best explination. Hopefuly someone here can point me in the right direction.

He has a table

Recipies(Id, name, item1, quantity1, item2, quantity2, item3, quantity3, item4, quantity4)

I don't believe that this is the proper way to create a table. What happens if you happen to come across a Recipie that needs 6 items?? or more??
After researching it, I believe it violates 2NF. Is this correct?

Instead I think a better structure would be

Recipies(Id, name)

RecipiesIngredients(Recipies.ID, Ingredients.ID, quantity)

Ingredients (Id, Name)

The problem my friends sees with this method is the fact that the Xref table will get giant.

In the example... Speed is of utmost importance, as the tables will most likely contain thousands of records and be queried against frequently.

Thanks for any input.

-Mike

 

[Mile-O]

Instead I think a better structure would be

Recipies(Id, name)

RecipiesIngredients(Recipies.ID, Ingredients.ID, quantity)

Ingredients (Id, Name)

You are correct but you shouldn't use ID as the name for your primary keys. Use the entity type and ID. So it should be:

tblRecipes
RecipeID
RecipeName

tblIngredients
IngredientID
Ingredient

tblRecipesToIngredients
RecipeID
IngredientID
Quantity


Don't use Name as a field name because it is a reserved word in Access.

Speed is of utmost importance, as the tables will most likely contain thousands of records and be queried against frequently.

The above will work as the friend's suggestion is too problematic.

 

[ducker]

I realize about the names of tables, I just threw the example together.

The above will work as the friend's suggestion is too problematic.

What aspect of this is too problematic? He acknowledges the fact that he might have to add fields to the table potentially in the future, but he is concerned about a HUGE crossreference table.

Might you have any links to any additional information information stressing the importance of breaking a table down like this as I don't have much to stand on if he has no problem adding fields to a table if he so needs the fields, in order to get faster performance.

 

[ducker]

I'm not sure if this plays in to effect here, but does it matter that this example isn't to be done in access, but in mysql, accessing it through a php script?

The php script will essentially be building the form (part of the html page) as it runs. So in effect the amount of modifications one must do to the form aren't quite as extensive as if you were utalizing a front end such as MS Access.

Thanks for the great input thus far.

-Mike

 

[wazz]

no, it doesn't matter.

 

[The_Doc_Man]

Pat's post is absolutely correct. (As usual...)

When you debate your friend about normalization, consider this as well as what Pat told you.

The normalization rules have evolved precisely because everybody USED to make the mistakes that these rules now allow us to evolve. Someone went through the trouble to derive PROOFS of how each of these normalization rules help in a specific, provable way.

Proofs along these lines generally involve simple concepts: Computability (finiteness of algorithm) and Space minimization (compactness of storage). When you can guarantee normalized tables, you can make mathematically precise statements about those tables to the point of generalization. You can demonstrate that, with a finite normalized table, a query CAN find all answers to a given "question." You can show that a normalized table structure is always equal to or smaller than the equivalent unnormalized version of the table. (The "or smaller" is USUALLY the case; it is quite rare to normalize and see absolutely no space improvements anywhere.)

If you are more technically minded and really wanted to follow up on the topic, you can try web searches on "Interrogatory Logic" - which is the question-oriented complement of "Assertory Logic." The latter topic is the subset of logic that includes but is not limited to the more traditional syllogisms that you took in your introductory logic courses. De Morgan's Theorem. Logical Implication. That sort of stuff.

Without this Interrogatory Logic, no one could prove that a query is even possible because there is no formal method of defining the result-set of a query without it. I.e. does the answer produced by the query actually answer the question that was asked? And the normalization "rules" have been derived as ways to reduce tables to the point that such proofs and determinations become possible.

Stated another way, the normalization rules are a short-hand form of DB experience. You know what experience is, don't you? With apologies to Ambrose Bierce, "Experience" is that attribute which allows you to recognize a mistake when you make it again.

 

[ducker]

After continueing my discussion with my friend it seems as though I have slightly misrepresented the situation.

In the case... there is never more then 4 ingredients. In addition those 4 columns of the table are indexed and stored in to memory, thus giving him faster access to the record matches.

I've been developing in access for quite some time, and feel I have a fairly good handle on how to create them and organize them. Yet in this case I'm still confused.

Again, this data isn't in an access database but on a webserver, contain thousands of records and will be queried against million of times a day. Having all of those columns indexed provides him with the added speed of the results. Users are not inputing any data, simply quering against it and displaying it.

my skills in php and mysql data manipulation aren't up to par yet so I can't reconfigure his data to try it the normalized way; but does any of this additional information change the base question? Are there circumstances where 3NF might not be the best route to go?