Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2012
    Posts
    7

    Unanswered: Product Spec. Database design help

    Hi All! Newbie here.

    I'm working on setting up a database for my business that keeps track of my clients, their orders, and invoices for those orders. It's a pretty easy setup but I'm running into one major issue. I haven't figured out how to organize the products and their specific specs in a table(s) and I have not been able to successfully relate them.

    I screen print t shirts and other apparel and my problem is that each product has a unique number of different colors and sizes and I need to be able to specify those on the invoices.

    For example:
    one kind of shirt has a specific product ID, which falls into a category, based on a style. so... product ID = 8000, Category = T-Shirt, style = 100% Cotton. Another might be 15000, Hoody, polyester/cotton mix.

    Obviously I can set up a table and make those records along with all the other pieces I offer, but all of the products come in different colors and sizes. The 8000 might come in Red, Blue, and Green, whereas the 15000 might come in Gold, Pink, Blue, and Orange. Also, the 8000 comes in sizes ranging from S-5XL, and the 15000 only comes in a range from S-2XL. With those two specs which range widely between products, How do I create a usable database for my products?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I suppose it depends on how you operate your business, but to me you have a generic product ID which describes the style, but you have a another two attributes colour and size which must be defined to uniquely identify a specific product.

    I'd probably implement that as a table each for the generic producxts, the colourways and the sizes
    and create an intersection table which associates those 3 elements to define a specific product (the heavyweight cotton teeshirt in Green in XXL). then having created the sepcific products (generic ID + colour + size) then start tracking stock, customer orders, supplier deliveries and so on.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    I'd set it up with Tables/Fields like:

    tblCategory
    CategoryID (PK)
    CategoryName
    CategoryDescription

    tblProduct
    ProductID (PK)
    CategoryID (FK)
    ProductName
    ProductDescription

    tblSizes
    SizeID (PK)
    ProductID (FK)
    ProductSize

    tblColors
    ColorID (PK)
    ProductID (FK)
    ProductColor

    For entering your data you’d have a series of nested Forms, one for each Table: frmCategory ,frmProduct, frmSize and frmColors, and use a Main Form/Subform configuration:

    The frmCategory would be the Main Form to the frmProduct Subform.
    The frmProduct would be the Main Form to the two Subforms, frmSize and frmColors.

    You’d Enter/Select a Category, then a Product, then the Sizes and Colors available for that Product.

    On your Invoice Data Entry Form, you’d have a series of Cascading Comboboxes. After selecting a Category, the Product Combobox would only display Products that come within that Category. Once a Product was selected, the Size and Color Comboboxes would only display those associated with that particular Product.

    Here are a couple of links with examples on doing Cascading Comboboxes:

    Access Tips: Cascading Lists for Access Forms

    Cascading Combo/List Boxes - Microsoft Access / VBA

    If you Google the term along with the "Access" you'll probably get a gazillion hits!

    Linq ;0)>
    Last edited by Missinglinq; 07-23-12 at 15:15.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    The risk using combo boxes (IE of not defining the intersection table which gives the final detailed product description) is that there is no product definition giving specific combinations only whihc are valid
    using an intersection table to define the final detailed product means that you can explcitily model the individual colour combinations in a systematic way. there is no risk of choosing an invalid size/colour combination. once you have defined the possible instances of the detailed products (style, size and colour) you can then go on to handle that business problems associated with the actual instances or usage of the garments
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jul 2012
    Posts
    7
    So What I've done is used cascading value lists to make part of an order form. What I need is a way to specify when I place orders in this fashion:

    1A category: (like T-shirt, Polo, Fleece, etc)
    then
    2A Style: (100% cotton, Polyester, 50/50, etc.)
    then
    3A color for that style: (every style has a different set of colors to choose from)
    then
    4Quantity's per size in that color: (not every style has the same range of sizes. Some go from XS - XL while some are S - 4XL, etc.)

    I've gotten to the part where I can specify which category, and then which style, but I can't get past that. I could really use some help with it. If anyone feels like taking a look I've attached the file, feel free to mess around with it. In the file, I've got the product colors all in one table, though there are only 2 products in there so far. Under the ProductResults table you can see where you can select a category, then a style, then a color (though that part doesn't work yet.) This would help alot of people out over at T-shirt forum as well.
    Attached Files Attached Files
    Last edited by terzdesign; 07-25-12 at 17:49.

  6. #6
    Join Date
    Jul 2012
    Posts
    7
    free bump.

Posting Permissions

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