3 Replies - 1098 Views - Last Post: 10 December 2012 - 02:48 PM Rate Topic: -----

#1 DanielVCK  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 10-December 12

Database menu item+ingredients tables

Posted 10 December 2012 - 02:35 PM

Ok. Right away i'm sorry if I shouldn't have made this topic or if theres an answer somewhere on here but I did do a quick look and to be honest i'm not even sure what I should search to find an answer.

Basically, I'm doing some A-level database work. The database I need to create is for an over-the-phone ordering system for a food delivery service. I need to keep track of stock, menu and ingredients.
The issue I have is that I need the database to be normalised to 3rd form.

How would I go about including a menu table AND an ingredients table while still doing this? Right now I have an ingredients table: Ingredient ID, Ingredient, Stock Level, and a Menu table: Menu ID, Item, Price, Ingredient 1, Ingredient 2, Ingredient 3...
But if an item on the menu only includes 2 ingredients then I have wasted space. I've tried but I can't think of a way to list all of an item's ingredients without possible wasted space.

I'm very sorry if i'm rambling or if this shouldn't have been posted in the first place, i'm just here because i'm struggling and it's beginning to frustrate me.

Is This A Good Question/Topic? 0
  • +

Replies To: Database menu item+ingredients tables

#2 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3730
  • View blog
  • Posts: 6,017
  • Joined: 08-June 10

Re: Database menu item+ingredients tables

Posted 10 December 2012 - 02:41 PM

Hey.

To fix the sort of 1NF violation you are describing, you'd want to set up a Many-To-Many (N:M) relationship between the tables, so that you could list all the ingredients needed for a menu item without having to create multiple identical fields in the menu table.

I wrote a RDBMS Design tutorial that may help you. The example I show in the N:M description and the table structures in the 2NF examples show the sort of layout you should be aiming for.
Was This Post Helpful? 1
  • +
  • -

#3 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3576
  • View blog
  • Posts: 10,439
  • Joined: 08-June 10

Re: Database menu item+ingredients tables

Posted 10 December 2012 - 02:46 PM

View PostDanielVCK, on 10 December 2012 - 10:35 PM, said:

But if an item on the menu only includes 2 ingredients then I have wasted space. I've tried but I can't think of a way to list all of an item's ingredients without possible wasted space.

Im not that of an expert, but I would use a link table that lists (via FK) what ingredients (resp. their IDs) belong to a Menu (ID).
Was This Post Helpful? 1
  • +
  • -

#4 DanielVCK  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 10-December 12

Re: Database menu item+ingredients tables

Posted 10 December 2012 - 02:48 PM

I feel like such a jackass for making this at all. I spent 3 seconds searching and found my answer, then I come back and you've told me it aswell.
So, to be clear here, I should have another table, lets call it FoodIngredients for simplicity, which contains: FoodID and Ingredient ID, which I can then link to their own respective tables.

Putting it into access, this lets me see which ingredients each food item contains and which food items each ingredient is used in. Terrific, and no wasted space.

Thank you very much.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1