SitePoint Sponsor

User Tag List

Results 1 to 8 of 8

Thread: Blackout brain

  1. #1
    SitePoint Member Razzinator's Avatar
    Join Date
    Apr 2010
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Blackout brain

    Database design that I can't seem to wrap my head around.
    Adding MySQL/PHP on a 10 year old html/css/js site. Current avg page size of about 75k.

    I Have:

    1100 transfers //paint by number patterns
    another
    700 transfers catagorized by a sku#

    120 different paints // each transfer recommends up to 28 paints

    I need to end up with a detail page of a transfer that includes an (html)table
    with the up to 28 recommended colors. Each color (png background) includes
    checkbox for inclusion with the transfer order.

    Any comments welcome something will turn the light on....

    razz

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    What exactly is the question?

  3. #3
    SitePoint Member Razzinator's Avatar
    Join Date
    Apr 2010
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Paints
    _id
    name
    number
    price
    discription
    image1
    image2

    Transfers
    _id
    name
    number
    price
    discription
    image1
    image2

    Quilt sets // 6 to 12 blocks each... but each block available also
    _id
    name
    number
    price
    discription
    image1
    image2

    In the column to point to paints needed I thought of using an array of color numbers (p101, p102, p103) etc.

    But in Kevin Yanks phpmysql5 book he states:
    "Once again, many inexperienced developers begin to think of ways to store several
    values in a single column, because the obvious solution is to add a category column
    to the joke table and use it to list the IDs of those categories to which each joke
    belongs. A second rule of thumb would be useful here: if you need to store multiple
    values in a single field, your design is probably flawed."

    Do I need 28 columns for links what if she comes up with something with more colors, or all paints for a quilt of 12 blocks.

    as I see the join tables are only dealing with a couple items only and it appears to be just one to one vs linking up 28/120 possible paints to 1800 patterns calling on 28/120 paints.

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Multiple values in one column is a no-no. But so is the paint1, paint2, paint3 ... paint28 column solution. These are clear indications that your database design has to be normalized.

    What is the relation between the tables?

    1 paint has 1 to n transfers? 1 transfer has 1 to n paints?
    Idem for the paint and quilt sets tables, and the transfer and quilt set tables.

    Once you've defined the relation between the tables, you then can decide if you need some joining tables.

  5. #5
    SitePoint Member Razzinator's Avatar
    Join Date
    Apr 2010
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    1 transfer has 1 to n paints

    how to point to 28 variable rows from the paint table, in a transfer or quilt row.

  6. #6
    SitePoint Member Razzinator's Avatar
    Join Date
    Apr 2010
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    transpnt table
    transfer._id // 1 colomn
    paint._id //28 columns

    admin can input an array while adding a transfer.

    am I on the right track now?

  7. #7
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by Razzinator View Post
    transpnt table
    transfer._id // 1 colomn
    paint._id //28 columns

    admin can input an array while adding a transfer.

    am I on the right track now?
    A bit I think

    So let's take the transfer and paint tables:

    Transfers
    _id
    name
    number
    price
    discription
    image1
    image2

    Paints
    _id
    name
    number
    price
    discription
    image1
    image2

    1 transfer can have up to 28 paints, right? And surely a paint can be used in more than 1 transfer.
    So you need a transpnt table (as you said), which will have only 2 columns:
    transfer_id
    paint_id

    In this table you'll save a row for each transfer-paint combination that is inserted by the admin.

    Attention: the way you store data in your database, and the way you display it to the user, or the way you let the user insert it, isn't always 1-on-1.

    In this case, if you want to limit the max number of paints for 1 transfer to 28, you'll have to impose that limit in your PHP code (or whatever language you use). And if you want to have the user insert the paints like this:
    Code:
    value 1, value 2, value 3, ...
    that's ok too. Again, in your PHP code you'll have to sanitize and validate any user input anyway, and you can also turn a comma separated value string like that in single values that you'll insert in the table.

    Are you following so far?

  8. #8
    SitePoint Member Razzinator's Avatar
    Join Date
    Apr 2010
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Think I got it now. Thank you. I will run some tests and be back l8r I'm sure.

    If I can't screw it down, I can always screw it up!


Bookmarks

Posting Permissions

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