Jump to content

Archived

This topic is now archived and is closed to further replies.

Hokus

Database design

Recommended Posts

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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+

Share this post


Link to post
Share on other sites

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|

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.