Results 1 to 11 of 11

Help with Pizza DB Design

  1. #1
    frankos72 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Posts
    11

    Help with Pizza DB Design

    Im trying to design a simple pizza order system. Starting with a database, Id like it to be rather dynamic.



    The customer, delivery, ordered etc is all pretty simple to me, but Im running into problems figuring out how to do some of the more complex pizza types.

    I have the following tables:
    Code:
    Table Name:      Columns:             Description
    Menu:                  PizzaID
                               Pizza Name         Cheese, 1 Top, 2 Top, 3 Top, Veggie Lover, Deluxe, Meat Lover etc
                               Pizza Descripton
                               Pizza Price
    
    Ingredients:         IngID
                              IngName             Pepperoni, Sausage, Ham, Motz, Cheddar, Onions, Olives, Thin, Thick
                              IngCatID             ID From a row on IngCategories Table
                              
    IngCategories:    CatID
                              CatName
    I though Id use a Menu_Ingredients join table to do a many to many to connect the menu to the ingredients. It seems simple at first, but it starts to break down when I try to conceptualize a 2 meat or 3 meat pizza. For example, a Meat Lover may automatically contain 3 specific meats, but the 3 Top is a choice of 3, so it seems there needs to be some intermediary step between the Menu_Ingredients table and the Menu Table. Something that says how many ingredients does this pizza get? But then what about other parts that are always 1 to 1? Every pizza has 1 crust and 1 sauce. It seems I may be in over my head on this one. Any help would be appreciated.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    7,550
    you have the tMenu table with the name
    PizzaID = 3
    Name = MeatLovers

    and you need a subtable tMenuItems to say whats in it:
    PizzaID, Ingred
    3, pepperoni
    3, sausage
    3, beef

    then when the meat lover is ordered, all items in tMenuItems are copied to the tOrderItems table.

    you can also design a form with regular pizza with 2 list boxes: 1 for item to add, 1 for items chosen.
    user can dbl-click Available item, it runs an append query to add it to the itemChosen for the 1 pizza.

  3. #3
    Join Date
    Apr 2017
    Posts
    889
    A possible (part of) database structure.
    Attached Files Attached Files

  4. #4
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    7,550
    I made a pizza model...

    Click image for larger version. 

Name:	pizza model.jpg 
Views:	43 
Size:	118.0 KB 
ID:	36789

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    13,828

  6. #6
    frankos72 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Posts
    11
    Quote Originally Posted by ranman256 View Post
    you have the tMenu table with the name
    PizzaID = 3
    Name = MeatLovers

    and you need a subtable tMenuItems to say whats in it:
    PizzaID, Ingred
    3, pepperoni
    3, sausage
    3, beef

    then when the meat lover is ordered, all items in tMenuItems are copied to the tOrderItems table.

    you can also design a form with regular pizza with 2 list boxes: 1 for item to add, 1 for items chosen.
    user can dbl-click Available item, it runs an append query to add it to the itemChosen for the 1 pizza.
    Ran man, this is starting to touch on what the real problem is, my interface. I'm think my problem is more in how to dynamically get the appriate rows from the menu table to the order details table. Here is a deeper example:

    Start with ran man s example of meat lovers. For simplification, we will say no substitutions or deletions. Meat lovers consists of hand tossed crust, regular sauce, motz cheese, pep, sausage, ham. So on the interface, we start a new order, the order is and other items are entered into the order details table. Now on the tOrderItems table it will automatically create a row for each of the 6 listed ingredients. Easy.

    Now let's get one step harder. 2 top pizza special. I still have a order details with the basic info and then in TOrderItems table, it will need to add a credit, cheese and sauce (we will make these set for now) and then 2 meats. How in the world do I make the interface ask what 2 toppings? How does it know to only ask for 2, not 3?

    Next level of complexity:. Family feast combo. 2 1 topping pizzas, cheeseybread or dessert pizza and a 2liter of soda for 25 bucks. So now I need 2 1 topping and somehow it needs to know to only ask for 1 topping. Each will add a line for crust, cheese, sauce and topping to the trderitems table Then I have the choice of cheesy or dessert, each one comes with it's on list of ingredients that will be added to the TOrderItems table depending on the selection. Then they have their choice of half a dozen different sodas. One of which will become a line on the tOrderItems table.

    So for this last one, the t order items table may look like. (For ease of reading in substituting item name for item ID.

    Order ID, itemId, qty, measure
    21, lg crust, 1, each
    21, reg sauce, 6, ounces
    21, motz, 16, oz
    21, pep, 32, slices
    21, lg thin crust, 1, each
    21, reg sauce, 6, ounces
    21, motz, 16, oz
    21, hamburger, 5, oz
    21, sm reg crust, 1, ea
    21, cinnamon apple filling, 4, ounces
    21, icing drizzle, 2, ounces
    21, Coke 2l, 1, ea.

    I just cannot figure out how to make the program know when I chose family feast that the ui will somehow walk me through making the necessary choices while automatically adding the fixed ingredients.




    Sent from my SM-N950U using Tapatalk

  7. #7
    frankos72 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Posts
    11
    I can build a custom interface, that has a form specifically for toppings when I can add and delete at will. Then a form for cheese one for sauce and 1 for crust.

    But I was hoping to make it more dynamic duo the same UI can build something like a sandwich or handle something like the family feast?

    I feel like the DB is just a recursive design, it's the ui that I'm confused about.

    Maybe I'm in the wrong sun forum. I'll look at the topics and try to find one that is for UI design.

    Sent from my SM-N950U using Tapatalk

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    13,828
    It may very well be a recursive design, but my thinking is you should get some model on paper and test it. Work through some of your "pizza types" and adjust the model until it satisfies your needs. As one of our contributors often says - "if you can't describe it, no one can build it", and "you're not ready to do it in Access, until you can do it on paper".
    Developing with physical Access(or any DBMS) without a model and test scenarios (blueprint) rarely results in an efficient database design. I'm not saying it can't or doesn't happen, it can, but it is a long, circuitous process.

    If you have order forms, or a menu or marketing literature -these may be very helpful in your design and testing.

    Good luck.

  9. #9
    frankos72 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Posts
    11
    Quote Originally Posted by orange View Post
    It may very well be a recursive design, but my thinking is you should get some model on paper and test it. Work through some of your "pizza types" and adjust the model until it satisfies your needs. As one of our contributors often says - "if you can't describe it, no one can build it", and "you're not ready to do it in Access, until you can do it on paper".
    Developing with physical Access(or any DBMS) without a model and test scenarios (blueprint) rarely results in an efficient database design. I'm not saying it can't or doesn't happen, it can, but it is a long, circuitous process.

    If you have order forms, or a menu or marketing literature -these may be very helpful in your design and testing.

    Good luck.
    Okay, here is a model of the menu and ingredients for each item on paper with sample data. I've highlighted in Orange all the different elements that are adding up to the family feast. There are details missing like the build up of the dessert pizza and cheesy bread, but based on the model, it shouldn't be necessary.

    I'm thinking it might be possible to simplify further by combining the tMenu_Items_Join and tItems_in_Items_Join tables into 1.... But this sure seems like it would work. I can easily imagine how to build a UI to take an order based on this menu. I cannot for the life of me figure out how to build a UI to put the data in the table outside of just manually putting the right info in the right tables, which sounds like a bad idea to me.

    So at this point, I have to ask.
    1. Will this DB work as designed?
    2. Will it work if I combine tMenu_Items_Join and tItems_in_Items_Join tables into 1 table?
    3. If either of the above are true, what am I missing? How can it be improved?
    4. How does one design a UI to put this menu data into the system?


    Thanks again! for your help and direction, you all are great!
    Attached Files Attached Files

  10. #10
    frankos72 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Posts
    11
    Well, no new responses. I'm sorry if it's too big of a problem. I've bitten off a lot, but that's the only way I can think to learn.

    I'm going to attempt to simplify the design, We'll see how it goes. Any Input is greatly appreciated.

  11. #11
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    13,828
    frankos72,

    I did a little "googling" for a pizzeria data model. I found a few things, but of course your situation is customized to you and your requirements. One thing to be aware of with any model/database involving prices is that prices change with time; prices may change if you have a loyalty program; prices may change if you have a special event...

    I found an overview model (it's only a part of your request as I see it).

    Click image for larger version. 

Name:	PizzaOrderDraftModel.png 
Views:	15 
Size:	81.0 KB 
ID:	36897

    and I also found this link that describes (as I understand it) an effort by a group of students to analyze and design an online pizzeria management system. I'm including the link because it puts many of the steps involved into context and may help you identify and "flesh out" your requirements and model. It is NOT a solution to your db design, but might help identify the activities involved.

    You may find some of the free data models at Barry Williams' site helpful.
    You may also find info at these links helpful:
    -Database Planning and Design
    -Stump the model

    Good luck with your project.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Cannot find Pizza.jpg
    By Lou_Reed in forum Access
    Replies: 3
    Last Post: 03-30-2016, 01:55 AM
  2. Web design
    By jaleebi22 in forum General Chat
    Replies: 1
    Last Post: 05-18-2015, 02:13 AM
  3. Design vs Article (=Design+Materials) problem
    By emihir0 in forum Database Design
    Replies: 14
    Last Post: 04-20-2015, 03:03 PM
  4. Table Design -- want to avoid a design that creates blank fields
    By snowboarder234 in forum Database Design
    Replies: 4
    Last Post: 10-08-2012, 10:13 AM
  5. Pizza store database help!
    By zagorette in forum Database Design
    Replies: 2
    Last Post: 06-30-2009, 10:47 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums