Results 1 to 8 of 8
  1. #1
    Join Date
    May 2006
    Posts
    3

    Unhappy Unanswered: Problem with multiple product tables - HELP

    Good Day,

    I have been trying to design a database and it has provided me with a slight problem. I want to be able to create a different table for different category of products. The reason for this is that each table will hold specific data fields for that given category of product. For example a table for toners will have unique data fields like productid, colour, capacity, yield etc, a table for harddrives will have unique data fields like productid, size, speed, interface etc.

    All of these tables have to then be connected to a master table probably called products. This table will have the generic data fields productID manufacturer, model, price, categoryID etc.

    Obviously, it is the category data field that would be used determine which table is linked to the product and hence which unique data needs to be filled out. However I cannot get my head around how to do this.

    If I had the following data how would I go about linking it; bearing in mind the data held for each product has different datafields dependant on which category it falls under:

    Toner
    1 Yellow High 6,000
    2 Cyan High 6,000
    3 Black Std 4,500
    4 Magen Std 3,000

    Harddrive
    5 40GB 5,400 IDE
    6 60GB 5,400 IDE
    7 80GB 7,200 IDE
    8 300GB 7,200 SATA

    Products
    1 Epson c900 80 Toner
    2 Epson C1100 90 Toner
    3 Epson C1100 30 Toner
    4 Epson C900 40 Toner
    5 Maxtor pl40 40 Harddrive
    6 IBM desks 50 Harddrive
    7 Maxtor pl80 60 Harddrive
    8 Quantu Fire 70 Harddrive

    I would rather avoid having one table that has data fields that would corrospond to each products unique requirements of data as this would leave plenty of blanks in the table.

    The numbers at the front of each table are obviously wrong but I do not know how else to depict the problem.

    It has been a while since doing databases at university and I am really struggling here. I hope the above is coherent and understandable. Please help and if you have any questions please let me know. A solution to this would be most grateful.

    Thanks

    CeeFar

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    A WAY to do this:

    TonerTbl
    --------
    ProductID

    HardDriveTbl
    ------------
    ProductID

    ProductsTbl
    -----------
    ProductID
    ProductTypeID

    ...

    ProductTypeTbl
    ---------------
    ProductTypeID
    ProductTblName

    Make sense?

    You would have to do some compound queries to get the data or have to run queries in 2 stages with some VBA code ...

    Heck, Rudy might have a better idea than what I put forth ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Apr 2006
    Posts
    157
    Tbl_Product (master)

    id
    categid
    specsdesc1 /* like height, width, etc
    specs1 /* value of the height or width or...
    specsdesc2
    specs2
    ...

    Tbl_Categdata (generic / common data)

    id
    manufacturer
    model
    ...

    categdata : master relation 1:M


    so the master table for products would include everything uniquely tied to
    the particular product and the common info like serial/batch nos. would fall into the categories table.

    hope this helps

    syntaxerror

  4. #4
    Join Date
    May 2006
    Posts
    3

    Need a little more info - Thanks

    Thanks for the info M Owen and SyntaxError.

    M Owen - I like the look of your solution, but I would really struggle with retrieving data. I see where you are going with the solutio but could you please describe the sort of compound query I would need to run in order to find a particular product? If for instanace I was searching for toners and I wanted to find all products that matched the Epson C900, what would I need to write in the query in order to get the results I want? I have some knowledge of SQL so you could write it in SQL format. Many thanks

    SyntaxError - If I have read your solution right, you are suggesting to include all of the unique fields for all of the products in one table! Have I read this right? If so, I would rather avoid having one table that holds lots of datafields. If I have read it wrong, could you please elaborate a bit more on your solution. Thank you.

    Again, thanks for your quick solution ideas. Look forward to your responses.

    Kind regards

    Casey

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    The potential problem with M Owens solution is that you are creating special sub categories. a "ferinstance", what happens if another media storage mechanism is launched (ssay USB pen drives) do you design a new table and new product category for them?, what about other products which might crossover 2 or more categories.

    Unbless you have soem really wacky requirement to me all the products you have identified sit in one table a products table
    if you have a need to difffernetiate then have a product type table with a FK pointing to that in the product table (categories of product type)
    if you index the priodcut descrption using an index which will allow a full text search then thats the place for other information. you could also use a product details section similarly indexed.

    I'd also include a manufacturer, in some cases the manufacturer may be irrelevant (eg hard disck drives if all that is required is a 250Gb disk drive doesit neccesarily matter who makes it, hwoever a toner refill for a laser printer manufacturer is important)

    for things like printer consumables you probably need the manufacturers original part number, so you can spot alternatives. for printers you probably need some form of lookup table so you can see which pritners use which components (eg a Canon EPS laser cart fits the following HP/Brother/Canon printers).

    there are good arguments foir using M Owens model, you just need to be very very clear in your mind what products required the extra table. An alterantive method is to store the additional parameters as a key pair combination in the the sub table

    eg
    tblproduct - contains all common data for the products
    tblProductKey - contains details of all vaid key pairs (eg Disk Capacity, Toner Colour,
    tbpprodExt
    ItemID-pk
    prodID-fk to product
    KeyID-fk to tblProdKey
    Parameter - cpuld be extended to be a numeric and string parameter

    I'm not reccomending that as a solution, but it does occaasioanlly have its merits.
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    But then again Mark I'm not trying to sell that design ... The BIG issue is how to solve the attributes dilemma. Since each "product" has its own unique attributes do you try and accomodate then ALL in 1 table where you'd have generic columns for attribute 1,2,3,4,... defined as Text sized 255 (or even a memo) as default? Or do you make a specific table for each unique product with its attribute definitions (what I adhoced in a bad way)? Or do you have a hybrid of a common product table with a variable # of child records each detailing a specific attribute?


    Thinking about it more ... Setting up something like:

    ProductsTbl
    -----------
    ProductID
    ProductName
    Qty
    ...

    ProductAttributesTbl
    -------------------
    ProductID
    AttributeNdx
    Description

    Real world:

    ProductsTbl
    -----------
    1 This Toner 40
    2 That Toner 20
    3 WD HardDrive 5

    ProductAttributesTbl
    -------------------
    1 1 HP 8100 Cyan cartridge
    1 2 Dimensions X x Y x Z
    2 1 Canon Black
    3 1 WD 40G 7200 rpm


    THIS is much better design ...

    The even better design would be to split off the Attribute description into another table to normalize the descriptions ...

    ProductAttributesTbl
    -------------------
    ProductID
    AttributeNdx
    AttributeID

    AttributeTbl
    -----------
    AttributeID
    Description


    And boys and girls for those of you who don't know this: the ProductAttributes table would be considered a junction table (thanks to Rudy for that clarification)

    CeeFar: To your question: This new design makes for an easy linear query ... Just a couple of INNER JOINS on these tables with a pattern match on the attribute description ...
    Back to Access ... ADO is not the way to go for speed ...

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    agree, but thats hardly in the spirit of RFH wuestions though
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    May 2006
    Posts
    3

    Thumbs up Absolutely FANTASTIC !!!

    You guys are wonderful. Thank you very much

    You just made my day and saved me a lot of brain cells

Posting Permissions

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