Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2009
    Posts
    6

    Unanswered: newbie DB modeling, columns when max requirements unknown

    Hi Everyone
    This is my first post here. I am just planning my first serious DB and I am stuck and don't really even know what keywords to search for to solve my problem. Please help me model this.

    I suppose if we had an ice cream store we could model a flavour table and a container table. If you had vanilla ice cream from the flavour table you could choose plain cone, flavoured cone or cup from the container table. In this case the options that accompany vanilla are finite and known, in this case 3.

    What I can't figure out is a good way to model this if the options to the original product do not have a consistent value. I sell laboratory instrumentation and I would like to offer parts as well as the instrument itself. In some cases there might only be 3 additional parts to offer but in other case it might be close to 100. In our ice cream DB we knew we could get by with four columns ID, plain cone, flavoured cone and cup. If I don't know how many parts will be offered with a given instrument do I need to create the maximum number of columns? If I only offer 3 parts with one instrument do I need to have 97 empty columns if I want to have another instrument with 100 parts?

    Thanks for reading my long post-Patrick

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by DadOnTheEdge
    In our ice cream DB we knew we could get by with four columns ID, plain cone, flavoured cone and cup.
    this is a poor design -- you don't use columns for the variations, you need to use rows

    so if you have 100 parts, you have 100 rows

    simple, yes?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2009
    Posts
    6
    Thanks for your response r937.

    I suck at forums, I probably did not provide enough information...

    I would like to offer many instruments each with potentially many parts. If I model the options in rows would I not need a table for each instrument? If that's the case I can do this, I am just wondering if it will be hard to maintain the DB with 100+ tables.

    Thanks again-Patrick

  4. #4
    Join Date
    May 2002
    Location
    General Deheza, Cba, Arg.
    Posts
    276
    Hi Patrick... sorry but my english... this is very bad, and may be not understand you.

    But.. you have a ice cream store (please send me 1 kilo of choclate ice cream)... lol

    and you need a sales model, when you have in each sale item a combination of products, for example vanilla ire cream with a cone, in this case 2 productos..


    a simple model...

    salesTable:
    idSale
    dateSale
    (and more data of the each sale)

    detailSalesTable:
    idDetailSale
    idSale
    priceDetailSale
    (and more data of the each detail sale)

    productsDetalisSalesTable:
    idDetailsale
    idProduct
    price
    (and more data of the each product detail sale)

    productsTable:
    idProduct
    price
    (and more data of the each product sale)

    in this case the table products contains ice creams, cone, etc, etc, etc
    tell me if this "model" help u.
    and very look...

  5. #5
    Join Date
    Jul 2009
    Posts
    6
    Gracias Achiola!

    Yes this helps, thanks. It's helping me see new things. Do you think that this would work?

    If I have multiple instruments to sell each with multiple parts could I do this

    Instrument table
    Parts table

    let's say we have two instruments in the instrument table:
    cheap instrument
    expensive instrument

    and then in the parts table we have two columns
    instrument name and parts name with four rows
    cheap instrument keyboard-part
    Expensive instrument door-part
    Cheap instrument tubing-part
    Expensive instrument power cord-part

    Could I enter a bunch of parts and then just group them by instrument name and then do some sort of join back to the instrument table?

    Thanks again Achiola, your English is great, mine is not and it is my first language :-(

  6. #6
    Join Date
    Jul 2009
    Posts
    6
    Achiola might be busy now. Could anyone else let me know if this is the right approach?

    Thanks in advance-Patrick

  7. #7
    Join Date
    May 2002
    Location
    General Deheza, Cba, Arg.
    Posts
    276
    yep, this model can do it, but not with names columns, for it have the ID.
    for example a autonumber column.

    And then U can join all tables.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by DadOnTheEdge
    Gracias Achiola!

    Yes this helps, thanks. It's helping me see new things. Do you think that this would work?

    If I have multiple instruments to sell each with multiple parts could I do this

    Instrument table
    Parts table

    let's say we have two instruments in the instrument table:
    cheap instrument
    expensive instrument

    and then in the parts table we have two columns
    instrument name and parts name with four rows
    cheap instrument keyboard-part
    Expensive instrument door-part
    Cheap instrument tubing-part
    Expensive instrument power cord-part

    Could I enter a bunch of parts and then just group them by instrument name and then do some sort of join back to the instrument table?

    Thanks again Achiola, your English is great, mine is not and it is my first language :-(
    I suspect you still haven't 'got' it yet
    so you have a product table...
    a product is made of many parts.....
    you have a another table beneaths that which identifies what parts go into that product.
    however taking a step back a part in itself is also a product (how else could you treat somethting, say a power cord, which may be sold separately to the main product, or is common across a range of products

    so you may have
    table: products
    ID 'PK (Primary Key)
    description
    blah-di-blah

    table: goesinto
    ProductId ' (FK: foreign key) identifies what product/part is
    ParentId ' (FK: foreign key) identifies what this prosuct/part is used in
    blah-di-blah
    ..you'd put other columns here say for example the quantity of product_id used in parent_id. in this case both ProductID and ParentID are the Primary key in this table as the combination of the two elements defiens what makes this row unique (ie you can only ever have one productID / parentID combination.. you can have lots of productid's matched with one parentid, you can have lots of papentid's defined with different productids (ie what parentid is comprised of. there is nothign stopping you rolling up producs into sub assemblies eg product (parentid) X is made up of prodcuts (1,5,2 & 10), product ZQ is made up product A,F, C, X. its also possible for say a product to be used in more that one sub assmebly ( eg you may decide to standardise on a M8 Hex bolt and that may be used for sub assemblies and the final assembly

    if you have different versions of the same part you may need to define a verision/build id as part of the primary key of your goesinto table. eg version 1 had ......., version 2 replaced the IEC cord with a Telefunken cord
    productid ' identifies what product/part is
    parentid ' identifies what this prosuct/part is used in
    BuildNo ' identifies the specific build of this product

    again taking a step back think about what you are proposing
    you are making a direct map between each product (the cheap and the expensive). for data modelling purposes that doesn't matter.. but what happens if say you introduce 2 more instruments, a 'value' instrument and a 'luxury' instrument. you don't want to have to revist the data model and make chanegs to the application. you want to make htose changes in data only. its part fo the science of problem ownership, when the user makes a change to their business practices you need to do as much as possible to make certain its the users problem to make data changes, than your problem to make db & application changes.

    I think you may well benefit form reading up on normalisation
    there are 2 excellent web pages on this
    Fundamentals of Relational Database Design -- r937.com
    and
    The Relational Data Model, Normalisation and effective Database Design

    HTH
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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