Results 1 to 10 of 10
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Table design for bakery products

    I'm looking for a little advice from the experienced database builders in the Lounge. I've started work on a database for a small community bakery. First up: a table for products. We have at least three pie flavors: apple, cherry, and blueberry. Each of those pies comes in regular and no-sugar-added varieties. Which of the following is the better approach—

    (a) Just three records—one for each flavor and pull the "regular" and "no sugar added" attributes from a separate "Varieties" table, or
    (b) Six records—two for each flavor: One for regular and a second for no-sugar-added.

    I'm leaning toward (a), but I'm trying to discern the advantages of each approach. Thanks!

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Caesar,

    I hate to say this but you can't design tables until you decide what you want to get out of, or what you to do, with your database. That is, inventory, sales, cost of goods sold, production schedule, etc. Once the outputs are known then you can go about designing the tables that will contain the appropriate data to accomplish those tasks. That's just the long winded version of saying we need more information to make an informed attempt at answering your question. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post
    Yeah, I didn't give you much to go on—my apologies. The database will track this small bakery's orders. The table I wrote about tracks all their products, to include the aforementioned pies. Right now, I have three related tables. "tbl_BakeryProducts" lists items such as those pies. "tbl_BakeryProductCategories" lists pies, cakes, breads, and so on. "tbl_BakeryProductVarieties" lists attributes such as chocolate, vanilla, and caramel. I've also built a select query, a report, and the beginnings of a form. I've built databases before, but it's been years since I tackled a project even this complex.

    The bakery exists at two locations: The actual bake shop where they make their products, and a small retail store. For years, they've kept track of their customers' orders on hand-written ledgers, sticky notes, and so on. What the owner wants is an application to take orders (typically over the phone), modify orders when the customer calls back a second or third time, and retain a digital record of those orders. In my mind, I can see an Access form that will allow an employee to easily enter details of the customer's order and that will permit easy retrieval of orders by customer name, order number, order date, and so on.

    I'm just getting started with these tables. I've got miles to go. I've attached a zipped copy of what I've built so far. I use Access 2013. (For now, kindly ignore the three tables that refer to "customers.") I'll be grateful for any advice.
    Country-Village-Bakeshop.zip

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    Look at the NorthWind database that MS has, it's free, it may have been installed with your version of Office.
    Off the top of my head the schema I think you need is:
    Customers, OrderHeader, OrderLines as the basic scheme, to support the tables you currently have.
    In the OrderLines you will need to relate to a product (tblBakeryProducts is the idea here).
    Your form would have Customer as the Main form,
    OrderHeader as a subform,
    OrderLines as a subform to OrderHeader form.
    Customer table to contain
    OrderHeader to contain the OrderID (primary key), OrderNo, DateOrdered, CustomerID (primary key to Customers table) etc.
    OrderLines to contain OrderLinesID (primary key), OrderID (primary key of OrderHeader table), BakeryProductsID (primary key to tblBakeryProducts), Quantity, etc.
    That should give you a starting point.

  5. The Following User Says Thank You to patt For This Useful Post:

    Caesar3 (2015-02-28)

  6. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post
    Thank you, Patt! I now have the NorthWind sample database, and I'm studying it closely. It appears that every object in the navigation pane (I'm looking at the expanded Supporting Objects group) is a shortcut. Why the shortcuts? Where are the "real" tables and such?

    Also, can you point me to a source that walks through and discusses the structure of this sample database?

  7. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    I don't know what you have because the Northwind database has no shortcuts (what ever that is), they may link to another database for it's tables though, it's been a long time for me there.

  8. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post
    I did say shortcut, didn't I? Well, if an icon on my desktop wears a tiny, curvy arrow, I'd recognize it as a shortcut, but shortcut is the wrong word when we're discussing database objects. Sorry. What I see are icons for objects in the navigation pane, including a long string of tables. It seems every one of these icons is a link (not a shortcut). It's what I expect to see with a database table that links to an Excel spreadsheet or a table in another database. So I'm just curious where the "real" objects live. It makes no difference, of course—it's just a curiosity.

  9. #8
    WS Lounge VIP Browni's Avatar
    Join Date
    Dec 2009
    Location
    Rochdale, UK
    Posts
    1,651
    Thanks
    38
    Thanked 161 Times in 139 Posts
    It's a custom menu.

    In the navigation pane if you create a new group any links you create in that group will have the little shortcut overlay.

    They will point to the internal database unless externally linked as the top three of my screenshot shows with an additional arrow.

    Screenshot (4).png

  10. The Following User Says Thank You to Browni For This Useful Post:

    Caesar3 (2015-03-04)

  11. #9
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    Browni has nailed it, you said that the shortcuts were for the table, forms, etc, not the database file itself.

  12. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post
    To Patt and Browni: My sincere thanks to both of you! I've used Access often over the years, but this is my first venture with Access 2013. I'll probably have more questions, but you've surely set me in the right direction. The NorthWind sample database is proving very helpful!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •