Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    PimpCup is offline Novice
    Windows 10 Access 2019
    Join Date
    Sep 2022
    Posts
    10

    Trouble with handling subcategories or differentiating classes of similar items

    I'm trying to figure out something with a database I'm currently trying to structure, but instead of getting into that specifically, I'll use a simple example to demonstrate the trouble I'm encountering.

    Let's say I have a database for keeping track of orders of fruit. I'll have an Orders Table, with OrderID, CustomerID, Fruit, quantity... stuff like that. For the fruit, I want to categorize them as apples, oranges, and grapes. Then, each subcategory of fruit could be broken down further, with varieties of apples, varieties of oranges, and varieties of grapes.

    Feasibly, each type of fruit would have a table, and each variety of fruit would be a record in that table. The different types of fruit (apples, oranges, and grapes) could be kept track of in another table, and each fruit table could have field that is the same for every record. That would provide a simple way of keeping track of the total number of tables there are for types of Fruit.

    Here is a diagram of the tables for this example database:

    Click image for larger version. 

Name:	fruitdatabaseexample.png 
Views:	39 
Size:	16.8 KB 
ID:	48775



    The problem I am having is this: If I want to add a new apple variety in the Apples table (preferably with a form), how do I make a new FruitID to use for the new record that will be added to the Apples table? Forms are pretty much restricted to one record at a time. There would probably be a way to do this with VBA, but can't help but feel I'm missing a simpler solution somewhere.

    I could just add a new record to the Fruit table first, and then use the newly generated ID, but because FruitID has a one-to-one relationship with the records for fruit varieties in all of the individual fruit tables, that seems like it creates the potential of generating extraneous FruitID records that don't directly tie to a record in one of the tables for each type of fruit.

    Are there significant drawbacks to however would try to implement this? Does this table structure itself need to be reexamined?

    Any thoughts and feedback are appreciated.

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Please step back and describe " an Order for apples I received/made today".
    I want you to describe what goes on the Order; the level of detail involved.

    Where does variety fit???
    I quickly found these apple varieties.

    • Gala.
    • Red Delicious.
    • Fuji.
    • Granny Smith.
    • Honeycrisp.
    • Golden Delicious.
    • McIntosh.
    • Pink Lady


    Also, since you have shown CustomerID in the Order table, please show Customer table on your relationships.
    In your business, does a customer have only 1 item per Order. If not then I think you will need Order and OrderDetail tables. But you know your business better than readers.

    Just found this re Fruit Types: (Don't know the relevance to your situation)

    Types of fruit

    • apples and pears.
    • citrus – oranges, grapefruits, mandarins and limes.
    • stone fruit – nectarines, apricots, peaches and plums.
    • tropical and exotic – bananas and mangoes.
    • berries – strawberries, raspberries, blueberries, kiwifruit and passionfruit.
    • melons – watermelons, rockmelons and honeydew melons.

    Top 10 Types of Oranges

    • Navel Orange.
    • Blood Orange.
    • Tangerine.
    • Acid-less Orange.
    • Mandarin.
    • Seville Orange.
    • Bergamot Orange.
    • Clementine.


    Last edited by orange; 09-24-2022 at 04:49 PM.

  3. #3
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    One fruit per table is not a normalized db?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  4. #4
    PimpCup is offline Novice
    Windows 10 Access 2019
    Join Date
    Sep 2022
    Posts
    10
    Quote Originally Posted by orange View Post
    Please step back and describe " an Order for apples I received/made today".
    I want you to describe what goes on the Order; the level of detail involved.

    Where does variety fit???
    I quickly found these apple varieties.

    • Gala.
    • Red Delicious.
    • Fuji.
    • Granny Smith.
    • Honeycrisp.
    • Golden Delicious.
    • McIntosh.
    • Pink Lady


    Also, since you have shown CustomerID in the Order table, please show Customer table on your relationships.
    In your business, does a customer have only 1 item per Order. If not then I think you will need Order and OrderDetail tables. But you know your business better than readers.
    Maybe using an example was not the best idea on my part...

    It's sort of modeled after how my table design looks in a particular portion of a database I'm trying (or rather struggling) to build, so I don't really have an answer for any depth of questions regarding things like customerID in the Orders table - it's just a shallow example.

    If I post what I have from the database I am trying to construct, I would have an answer for all those sorts of questions, and maybe that would ultimately provide you, and the others active on this forum, better insight into what I am trying to do. I've been grappling with it for so long that my head is starting to spin, and I very well might be stuck on the wrong path.

    As a moderator, would you recommend that I start a new thread for that, or would you rather we keep it going here? Let me know, and I'll do post it wherever you advise me to do so.

  5. #5
    PimpCup is offline Novice
    Windows 10 Access 2019
    Join Date
    Sep 2022
    Posts
    10
    Quote Originally Posted by Welshgasman View Post
    One fruit per table is not a normalized db?
    How would I change this table structure so that it would be normalized?

  6. #6
    xps35's Avatar
    xps35 is online now Competent Performer
    Windows 10 Office 365
    Join Date
    Jun 2022
    Location
    Schiedam, NL
    Posts
    229
    Quote Originally Posted by PimpCup View Post
    How would I change this table structure so that it would be normalized?
    I think you should have one fruit table with fruit type (apple, orange, grape) and variety. In your current model if you decide to sell bananas, you have rebuild your database. In the suggested model you simply add entries to tables.

  7. #7
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Y'all should consider that pseudo data and situations often leads to spending time on a solution only to find that it doesn't work because it won't fit the real situation. Has happened to me. Just an FYI.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    PimpCup is offline Novice
    Windows 10 Access 2019
    Join Date
    Sep 2022
    Posts
    10
    Quote Originally Posted by xps35 View Post
    I think you should have one fruit table with fruit type (apple, orange, grape) and variety. In your current model if you decide to sell bananas, you have rebuild your database. In the suggested model you simply add entries to tables.
    Like:

    Fruits
    • FruitID
    • FruitType (Apple, Orange, Grape, etc.)
    • FruitVariety (Gala, Valencia, Concord, etc.)


    Is that right?

    If so, what if I decided to add bananas, but also wanted to record some other qualifier specific about bananas (other than variety), that none of the other fruits had? Would I need a new column in the Fruits table to address that, and simply have null values in that column for pretty much all other fruits that aren't bananas?

  9. #9
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    PimpCup,

    I am pointing you to an old thread started by a person who was self proclaimed absolute beginner, struggling with lots of things. If you read through some of the dialog, I am sure you'll see yourself in what is being discussed. You may get some ideas regarding direction, things to do/try etc.

    Good luck.

  10. #10
    xps35's Avatar
    xps35 is online now Competent Performer
    Windows 10 Office 365
    Join Date
    Jun 2022
    Location
    Schiedam, NL
    Posts
    229
    Quote Originally Posted by PimpCup View Post
    If so, what if I decided to add bananas, but also wanted to record some other qualifier specific about bananas (other than variety), that none of the other fruits had? Would I need a new column in the Fruits table to address that, and simply have null values in that column for pretty much all other fruits that aren't bananas?
    That is a solution. In addition you can hide the field on your form(s) for non-bananas.

  11. #11
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by PimpCup View Post
    ...If so, what if I decided to add bananas, but also wanted to record some other qualifier specific about bananas (other than variety), that none of the other fruits had? Would I need a new column in the Fruits table to address that, and simply have null values in that column for pretty much all other fruits that aren't bananas?
    No! You don't add fields - you add records to tables! You need:
    tblFruitTypes: FruitTypeID, FruiytType (You have there records e.g. for apples, oranges, and grapes. When you need to add bananas, you add a new record here.);
    tblFruits: FruitID, FruitTypeID, FruitVariety (FruitTypeID is here a foreign key and is linking FruitType from tblFruitTypes with this registered specific fruit. FruitVariety is a text field which specifies the variety of this specific fruit. The table must have an unique index based on FruitTypeID and FruitVariety, in addition to unique primary key based on FruitID, to prevent registering same variety of FruitType several times. It is on you to decide, does this additional index allow nulls for variety field or not. I.e. is it possible to register general bananas, leaving the variety field empty, or you have always to declare the variety too. And with second option, you always can declare e.g. "Undefined" as variety for every fruit type. When you need to split some variety with some additional info, you simply add a new variety with expanded info. E.g. you have apples of variety "Granny Smith". You can always add apples of variety "Granny Smith (low quality)"NB! This table will have a separate record for every recorded variety of every fruit type!). To register new fruit varieties, you need a form (bound or unbound, as you wish) where you select fruit type, and which has a linked continuous subform with tblFruits as source, where all registered varieties of this fruit type are displayed. When you add a new record into this subform, it will be automatically linked to active fruit type in parent form.

    When you register orders, then in order details table you will have a record for every FruitID of ordered fruits (plus info about quantities, prices, etc.). NB! In order details table, not in orders table. In orders table, you have general order info, like supplier ID, order date, delivery term, delivery address, etc. An of-course, the order details form (a continuous one) will be subform of orders form (a single one).

    Edit: In form where user registers e.g. order details, it will be reasonable to use a combo box to select a fruit of specific type and variety from tblFruits. My advise is you use as RowSource of this combo a query like
    Code:
    SELECT f.FruitID, ft.FruitType & ": " & f.FruitVariety As FruitName FROM tblFruits f LEFT JOIN tblFruitTypes ft ON ft.FruitTypeID = f.FruitTypeID ORDER BY 2
    You set 1st column as linked one (i.e. combo returns FruitID), set the column count to 2, and column widths to "0, 2.5" (i.e. the combo dispalys FruitName only - like "Apple: Granny Smith".).
    Last edited by ArviLaanemets; 09-23-2022 at 04:31 AM. Reason: Additiona info

  12. #12
    PimpCup is offline Novice
    Windows 10 Access 2019
    Join Date
    Sep 2022
    Posts
    10
    Quote Originally Posted by Micron View Post
    Y'all should consider that pseudo data and situations often leads to spending time on a solution only to find that it doesn't work because it won't fit the real situation. Has happened to me. Just an FYI.
    I can see where that can go wrong, if for instance, my framing of the issue itself is where my trouble truly starts. I suspect that's likely happening here.

  13. #13
    PimpCup is offline Novice
    Windows 10 Access 2019
    Join Date
    Sep 2022
    Posts
    10
    Quote Originally Posted by orange View Post
    PimpCup,

    I am pointing you to an old thread started by a person who was self proclaimed absolute beginner, struggling with lots of things. If you read through some of the dialog, I am sure you'll see yourself in what is being discussed. You may get some ideas regarding direction, things to do/try etc.

    Good luck.
    Thank you, I'll have a look there and try to work through some things.

    Admittedly, I don't have experience with databases, and I could use a fresh perspective on looking at things to maybe help me think about things in a different way.

  14. #14
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    PimpCup,

    Here's a link to a RogerCarlson tutorial(Class Information System). If you download and work through this (45-60 minutes), you will learn much about database concepts and design. The procedure he uses will lead you from a "Business description" to a database design (tables/attributes and relationships). What you learn can be used with any database. Best use of an hour based on your current needs and database experience.

  15. #15
    PimpCup is offline Novice
    Windows 10 Access 2019
    Join Date
    Sep 2022
    Posts
    10
    Quote Originally Posted by orange View Post
    PimpCup,

    Here's a link to a RogerCarlson tutorial(Class Information System). If you download and work through this (45-60 minutes), you will learn much about database concepts and design. The procedure he uses will lead you from a "Business description" to a database design (tables/attributes and relationships). What you learn can be used with any database. Best use of an hour based on your current needs and database experience.
    Thanks Orange.

    I worked through the tutorial you linked, and some others on the same site. The example with the catering seemed to get me to the solution for the example I posted here. This is what a restructuring of it looks like:

    Click image for larger version. 

Name:	fruitdatabasecorrected.png 
Views:	10 
Size:	7.3 KB 
ID:	48850

    That's also assuming that there can be one or more kinds of fruit for a given order, which was a bit ambiguous from the way I had structured the tables initially.

    I've also been working through some things on the actual database I am constructing, and realize that the example problem I had in the post here doesn't really reflect the intricacies of my particular situation.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 11-15-2019, 07:23 AM
  2. need query to extract not similar items from two tables
    By learning_graccess in forum Queries
    Replies: 1
    Last Post: 04-02-2012, 04:52 PM
  3. Addition of similar coded items in query
    By shanky365 in forum Queries
    Replies: 1
    Last Post: 08-23-2011, 11:50 PM
  4. Grouping Similar Items
    By igendreau in forum Queries
    Replies: 4
    Last Post: 07-29-2011, 06:53 AM
  5. similar data rows trouble
    By andyf80 in forum Database Design
    Replies: 3
    Last Post: 06-11-2010, 10:06 AM

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
  •  
Other Forums: Microsoft Office Forums