Results 1 to 6 of 6

Set up of product variant database with large number of product attributes

  1. #1
    User_Wookie is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2019
    Posts
    3

    Set up of product variant database with large number of product attributes

    DB_Test.pdf
    Hi all,

    Firstly, thanks for taking the time to look at this! It never ceases to amaze how cool it is that people take the time to share knowledge with strangers over the interwebs you are all superstars.

    Right, with that out of the way here is the background.
    Obviously I am a DB newbie, my aim is to create a database to record the different attributes of our product range. Typically we design a product, produce it, and then make iterative changes to it during subsequent production runs (although not all production runs will have changes).
    The number of attributes that we want to capture is around 300 and will no doubt grow over time. The attributes can be arranged into categories.

    Initial plan was to keep it to two tables
    tbl_Products: Where product specific information which does not change was kept (such as name, sku, original design date)
    tbl_Variants: Where the attributes of the product variant are kept.
    (time for all the DB masters to grit their teeth)
    Creating a new product variant in tbl_Variants would involve copying the entire previous version (as the majority of attributes wouldn't change)




    My vision of the UI for managing the entry/editing of data would be a Navigation form where you could select the Product/Variant from cascading dropdowns then use the navigation tabs to view the different categories of attributes, as viewing all the attributes would be overwhelming.
    The queries I have are:

    1. Is the proposed DB structure okay for what I am trying to achieve?
    2. Is there an easy way to assign categories to the attribute fields? I tried creating tables where I would list out the attributes and assign them category (tbl_Attribute_Mapping and tbl_Categories), however I donít know how to relate them to the Field names in a query (ie a query that returns all fields that are assigned a specific category). Or do I need to go through and assign individual fields to each "Category" query that I will need to run in order to display attributes relating to a category?

    Thanks again for your time

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,354
    My 2 cents:
    Initial plan was to keep it to two tables
    Mistake #1. Have as many tables as it takes to achieve the goal and abandon any such thinking. Without knowing much about the business, I can see tables for products, a lookup table for categories and maybe one for variants. Then there might be a junction table to join categories to variations and/or products to variations. It is not clear if every product can include any variant (thus 300 variations x 100 products = 30,000 "products") or not.

    You would not use a navigation form; rather a tab control on a form. Tables come first, then queries to support forms, then forms and reports. IMHO don't put forms before queries as a form that turns out to be useless because of its recordsource is no good at all.

    You've posted far more about how you think it should be designed than you have about the business info. That is the part we need to understand in order to provide meaningful suggestions or comments on your ideas.
    Or do I need to go through and assign individual fields to each "Category" query that I will need to run
    If a table contains a category field with a repeated category value for each attribute in that category, then 1 query retrieves every attribute based on the category criteria. There's no need for multiple queries for this.

    I find it odd that any base item that can be produced in variations would ever have the same sku.
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.

  3. #3
    User_Wookie is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2019
    Posts
    3
    Thanks so much for taking the time to reply, appreciate the feedback.

    Mistake #1. Have as many tables as it takes to achieve the goal and abandon any such thinking.
    IMHO don't put forms before queries as a form that turns out to be useless because of its recordsource is no good at all.
    Good calls thanks

    It is not clear if every product can include any variant (thus 300 variations x 100 products = 30,000 "products") or not.
    You've posted far more about how you think it should be designed than you have about the business info. That is the part we need to understand in order to provide meaningful suggestions or comments on your ideas.
    :

    Apologies, so we manufacture a range of products which have a set of common attributes, such as number of windows, flooring material, roof material etc. Depending on customer feedback we will make incremental changes to the products, the majority of elements are the same but there are small changes. The idea is that if we make a change to the product, we create a new variant with the updated attributes. A single product can have multiple variants, but not the other way around. The common element between products is that they share the same attributes.

    When looking at the attributes they can be categorised so "number of windows", "window material" would be under the category "Windows"

    If a table contains a category field with a repeated category value for each attribute in that category, then 1 query retrieves every attribute based on the category criteria. There's no need for multiple queries for this.
    Okay thanks

    I find it odd that any base item that can be produced in variations would ever have the same sku.
    This is driven by our retailers, they don't want to change SKU every time we make a small change to a product, because in their heads it is the same product.

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,354
    Then maybe one approach is have all the fixed attributes pertaining to the sku (one window, plywood floor...) and have the variations and add-ons as notes? Or variations are linked to categories as you already stated. I see that you're uncovering some of the mystery about the business but you must be holding back for some reason. You didn't even mention what the product is?? Hard to provide focused help without that understanding.

    Good luck with the db.

  5. #5
    User_Wookie is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2019
    Posts
    3
    I can see tables for products, a lookup table for categories and maybe one for variants. Then there might be a junction table to join categories to variations and/or products to variations
    Something more like this? See V2 doc

    V2.pdfYou didn't even mention what the product is?? Hard to provide focused help without that understanding.
    Edit: Sorry I didn't see your other post, thanks for your time. I will pull together an example of the data we are capturing, hopefully that will provide some clarity.

  6. #6
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,354
    usually you don't link lookup tables to anything. They provide the row source (list) for a combo or listbox, which you bind to your table. Establishing a relationship can cause issues - like if you attempt to add an item to a table and a related value is needed elsewhere first.
    In the meantime, good luck with your project.

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

Similar Threads

  1. Price List Structure - Numerous Attributes for Each Product
    By Sunny8760 in forum Database Design
    Replies: 12
    Last Post: 06-21-2018, 12:20 AM
  2. Replies: 6
    Last Post: 02-28-2015, 11:57 AM
  3. Replies: 3
    Last Post: 01-18-2015, 06:05 PM
  4. Replies: 2
    Last Post: 08-14-2014, 11:49 AM
  5. Replies: 3
    Last Post: 02-26-2013, 05:07 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