Jump to content


Photo

Database design


  • Please log in to reply
4 replies to this topic

#1 Hokus

Hokus
  • Members
  • PipPip
  • Member
  • 23 posts
  • LocationAlameda, CA, USA

Posted 06 November 2003 - 09:31 PM

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.
[!--sizeo:2--][span style="font-size:10pt;line-height:100%"][!--/sizeo--][!--fonto:Arial--][span style="font-family:Arial"][!--/fonto--][!--coloro:blue--][span style="color:blue"][!--/coloro--]"Experience is a wonderful thing. It enables you to recognize a mistake when you make it again".
[!--sizec--][/span][!--/sizec--][!--colorc--][/span][!--/colorc--][!--fontc--][/span][!--/fontc--]

#2 Sephiriz

Sephiriz
  • Members
  • PipPip
  • Member
  • 20 posts
  • LocationNew York

Posted 06 November 2003 - 11:07 PM

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.
Check out the X Community
PHP Manual | Learn XML | Safe PHP Scripting | Regular Expression Help

Current Project(s):
I. phpRetrograde - PHP5 to PHP4.
II. str_replace_count - Replace a string specified amount of times. III. WordStem - Extract word-stems from strings.

#3 Hokus

Hokus
  • Members
  • PipPip
  • Member
  • 23 posts
  • LocationAlameda, CA, USA

Posted 06 November 2003 - 11:19 PM

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+
[!--sizeo:2--][span style="font-size:10pt;line-height:100%"][!--/sizeo--][!--fonto:Arial--][span style="font-family:Arial"][!--/fonto--][!--coloro:blue--][span style="color:blue"][!--/coloro--]"Experience is a wonderful thing. It enables you to recognize a mistake when you make it again".
[!--sizec--][/span][!--/sizec--][!--colorc--][/span][!--/colorc--][!--fontc--][/span][!--/fontc--]

#4 Sephiriz

Sephiriz
  • Members
  • PipPip
  • Member
  • 20 posts
  • LocationNew York

Posted 07 November 2003 - 12:15 PM

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|
Check out the X Community
PHP Manual | Learn XML | Safe PHP Scripting | Regular Expression Help

Current Project(s):
I. phpRetrograde - PHP5 to PHP4.
II. str_replace_count - Replace a string specified amount of times. III. WordStem - Extract word-stems from strings.

#5 Hokus

Hokus
  • Members
  • PipPip
  • Member
  • 23 posts
  • LocationAlameda, CA, USA

Posted 08 November 2003 - 05:33 PM

Thanks man
[!--sizeo:2--][span style="font-size:10pt;line-height:100%"][!--/sizeo--][!--fonto:Arial--][span style="font-family:Arial"][!--/fonto--][!--coloro:blue--][span style="color:blue"][!--/coloro--]"Experience is a wonderful thing. It enables you to recognize a mistake when you make it again".
[!--sizec--][/span][!--/sizec--][!--colorc--][/span][!--/colorc--][!--fontc--][/span][!--/fontc--]




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users