Database design

[Hokus]

Hello, I\'m building a food-recipe database.

 

Now, i want to arrange my recipes in different categories i.e. Appetizers,

Main Courses, Soups etc.

That\'s not much of a problem.

 

However, here is what i would like some sugguestions on:

 

Say I arrange categories in different tables. <Appetizers> <Soups> etc.

what if i want to add a new recipe to the database, and it is a recipe which belongs under both catetories (a soup that is also and appetizer).

I don\'t want to write the recipe twice, ones to both tables.

 

Then, when i want to retrieve information, how can i destinct a recipe which is both a soup AND an appetizer. ?

 

would you recommend me to write all recipes into one table, and then destinct them by category ID\'s ? (e.i. \"a\" for appetizer, \"as\" for appetizer AND soup.) or is there a easier way to acheve this functionality ?

 

Thanks.

[Sephiriz]

If I were you, I would do one table.

 

First row is ID, which is primary and auto-increment, assuring that every row is different.

 

Columns would be:

ID| Name| Recipe | Appetizers | Soups| etc.|

 

So, if it is an appetizer, you put in a 1 for appetizer, or 1 for soup if its a soup. If it\'s both, put 1 in both.

 

Then when getting the information from table, WHERE Appetizer = 1, etc.

 

I hope this is what you were talking about, and this may not be most efficient, feel free to improve.

 

Also! You could just make a colum called Type, and store Appetizer, Soups, etc. in it, which would save you a few columns. So instead, you would go WHERE Type = Appetizer.

[Hokus]

That\'s a good idea, never thought of that before.

 

However, what if I have say 60 different catagories.

then i have to make 60 cols + all name, text, ID etc.

 

SQL then have to look through all the recipes to find which ones are marked \"1\" in that specific category.

 

Won\'t that make the DB search extreamly slow ?

 

amount of recipes will eventually be 10,000+

[Sephiriz]

So stick with using one column called Type, where you store the type of recipe it is.

 

Another alternative, although I don\'t believe is necessary, is creating a table of types of food, each with a unique ID, and using that ID under type to refer to that food type in the other table.

 

ID|Name|Recipe|Type|

1 |Bagel|blah...|Breakfast|

 

If you made another table for food types:

 

TABLE Food_types

ID|Name|

1| Breakfast|

---------------------

TABLE Recipes

ID|Name|Recipe|Type|

1| Bagel| Blah | 1 |

 

I personally would just stick with:

 

TABLE Recipes

ID|Name|Recipe|Type|

1 | Bagel| Blah | Breakfast|

[Hokus]

Thanks man