Jump to content

Database design


Hokus

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.

Link to comment
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.

Link to comment
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+

Link to comment
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|

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.