Converting Design to Tables question

Databases
1

Hello Everyone,

I have a question regarding converting a design which uses some type of list or multivalued attribute into tables. For instance, let’s say I was trying to model a recipe. The recipe would be the entity, and it would have ingredients, so in this case an ingredient list. Now if I didn’t want to store all of the ingredients in one field, how could I do this? In this case, every recipe has a different number of ingredients, which could be a lot or a little, there’s really no telling. If anything needs clarification let me know. Normally Recipe would have it’s own table and the multivalued attribute would be in a separate table, but in this case the problem is the lack of knowing how many different values, or ingredients there would be. Maybe I’m thinking about this the wrong way.

Is there any way to do this?

2

multivalued attributes are a no-no in relational database tables (they violate first normal form)

since an ingredient can be used in multiple recipes, what you have is a classic many-to-many relationship

you’ll need one table for the recipes (with description, etc.), and one table for the ingredients (with description, etc.)

and then you’ll need an association or relationship or linking or junction table (the terminology varies) –

CREATE TABLE recipe_ingredients
( recipe_id INTEGER NOT NULL 
, ingred_id INTEGER NOT NULL 
, PRIMARY KEY ( recipe_id , ingred_id )
, qty SMALLINT
, uom VARCHAR(9)
);

so the primary key means you can only identify each ingredient in each recipe once, and there are as many rows for a given recipe as there are ingredients in it, or, put another way, there are as many rows for a given ingredient as there are recipes it is used in

the qty and uom (unit of measure) columns are data attributes which indicate how much, e.g. 2 cups, 1 pinch

3

Thank you Rudy,

multivalued attributes are a no-no in relational database tables (they violate first normal form)

since an ingredient can be used in multiple recipes, what you have is a classic many-to-many relationship

you’ll need one table for the recipes (with description, etc.), and one table for the ingredients (with description, etc.)

I had known this much, but I was a bit out of it when I wrote the question. It’s nice to see some clarification about that though. I took a DB class a while ago, but haven’t done a lot with them since, so my knowledge is a bit rusty. However, what you are saying about the relationship table does sound familiar. Thanks a lot for the informative response, it’s really nice having such a knowledgeable database guy like you on the Sitepoint forums :slight_smile: