Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2004
    Posts
    3

    Product matrix structure question

    I am developing a web application where I need to store some data about suppliers.

    For each supplier, I need to store a matrix of true/false objects - product types down the side, and product grades along the top, so that for any product type and grade that the company supplies, a tick can be placed in a box to represent the product.

    The product types and product grades are kept in other tables in the database.

    I thought about having a separate field for each combination of product type and grade, however this would mean having nearly 150 fields and is not particularly expandable - ie if new product types or grades were added to the database then someone would have to go and create new fields in this table to accommodate them.

    Any suggestions as to the best structure to use here would be most appreciated.

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    values (value)
    tableA (name, value)

    Example:

    values
    -------
    1
    2
    3
    4

    tableA
    --------

    name1 1
    name1 2
    name1 3
    name2 1
    name2 2
    etc.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Jul 2004
    Posts
    3
    Okay, for future reference, I've come up with this:

    Have a supplier table with a numerical ID, and details of the suppliers.
    Have a product form table with a numerical ID, and details of each type of product.
    Have a grades table with a numerical ID, and details of each product grade available.

    Then, have a Supply table with a supplyID, supplierID, productID, and gradeID. A row in this table would describe the position of ticks in that "matrix" I described while having its own unique ID number.

    Thanks anyway.

  4. #4
    Join Date
    Oct 2003
    Posts
    706
    In my opinion, a matrix-style table structure is not the way to do it. (I know that you don't plan on storing it that way... I'm not addressing you.)

    The information should be stored in tables of simple lists: {this_product, this_grade, is_sold_by_this_supplier , for_this_price, etc...}

    The information is then presented in a matrix of check boxes, and the user's inputs are then parsed and processed by procedural code. There is code to build the visual matrix, and more code to process the returned data when the user presses "OK."

    Don't try to slough-off the work to the DBMS by means of a grid-structured table.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  5. #5
    Join Date
    Jul 2004
    Posts
    3
    Totally agree. I knew this was bad and knew to avoid it.

  6. #6
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    A grid matrix violates first normal form: Eliminate repeating groups.

  7. #7
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    The structure that I posted, was not a grid matrix structure. Instead, it was a simple many-many.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

Posting Permissions

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