www.webdeveloper.com
Page 2 of 2 FirstFirst 12
Results 16 to 23 of 23

Thread: Multiple Values in One MySQL Column?

  1. #16
    Join Date
    Nov 2008
    Posts
    2,477
    Quote Originally Posted by svidgen View Post
    For instance, I would argue that storing colors as a comma-delimited string lowers the complexity of accessing a produce from O((M/N)*Log(M)*Log(N)) to (Log(M)) where N is the total number of color assignments used (relationships between a color and a product) and M is the number of products in the products table.

    Log(N) is the cost of the product lookup, assuming a BTREE index.
    Log(M) is the cost of the color lookup, assuming a BTREE index.
    M/N is the average cost of joining the product to its colors.

    So, by denormalizing the data, you're looking at saving a M/N Log(N) lookups, each of which will be a higher cost than the single Log(M) lookup needed for the denormalized form.
    However this is not the end of the story because you now have to post process all your data to get it in a useable form. You also have potential issues with large indexes from TEXT fields used to store the CSV (Postgre in particular has issues), your tables are locked whilst maintaining these larger indexes, and you also have to use more storage for the TEXT field when you could have used an INT. There are more factors involved here.

    The cost is the inability to efficiently search, update, or remove single colors globally across all product rows.
    That's certainly one of the other costs, though there are more.
    The first rule of Tautology Club is the first rule of Tautology Club.

  2. #17
    Join Date
    Jan 2007
    Location
    Wisconsin
    Posts
    2,120
    However this is not the end of the story because you now have to post process all your data to get it in a useable form.
    Well ... yes. And, storing a comma delimited list of colors is no more difficult--if not easier--to process than iterating through a resultset and throwing away redundant data.

    PHP Code:
    // it's a one liner ... 
    $colors preg_split(",\s+"$row['colors']); 
    Table locking? ... only if that field is in your where clause. And my assumption is that you wouldn't do that in this case. In fact, that's the key pressupposition for storing the data that way--it's not indexed!
    Jon Wire

    thepointless.com | rounded corner generator

    I agree with Apple. Flash is just terrible.

    Use CODE tags!

  3. #18
    Join Date
    Nov 2008
    Posts
    2,477
    Quote Originally Posted by svidgen View Post
    Well ... yes. And, storing a comma delimited list of colors is no more difficult--if not easier--to process than iterating through a resultset and throwing away redundant data.
    What forces you to retrieve redundant data? And this is just a simple select, what about when your boss wants to implement stock count, or someone wants to know how many blue items you have in your database? What about when someone decides that your green is really more of a teal and you have to update every row in the table(s) where green is mentioned? What about if you want to remove items from the list? Or search using this data? Or cascade deletes? There are so many issues with this approach that can all be avoided just by properly structuring your data. Personally I think listing values like this is a really bad idea for many reasons. I'm not really interested in arguing the point though, if it works for you, go for it.
    Last edited by Mindzai; 03-12-2010 at 03:15 AM.
    The first rule of Tautology Club is the first rule of Tautology Club.

  4. #19
    Join Date
    Jan 2007
    Location
    Wisconsin
    Posts
    2,120
    And of course, I agree in this case that color probably deserves a separate table. Though, I also think it's important to stress the caveats of normalization, which you are too quick too dismiss, in my opinion. And quite frankly, in a professional setting I have been asked specifically to denormalize a design for analysis querying performance. So, you can imagine I'm a little put off by normalization idealism--it's simply not viable in many real world applications.

    I really didn't want to turn this into a huge debate over this. A word of caution needs to be issued in the shadow of idealim, though.
    Jon Wire

    thepointless.com | rounded corner generator

    I agree with Apple. Flash is just terrible.

    Use CODE tags!

  5. #20
    Join Date
    Oct 2009
    Posts
    13
    Wow, it seems I've sparked quite the discussion, haha.

    I have been doing my research, and learning more. I have two questions, if someone would be willing to help a bit more.

    If I WAS to go with just the one table, how would I actually store the colors in the field with PHP, and how would I also get them from the table? Is it possible to use a - as a delimiter?

    Lets say I have:
    ItemID(PK) | Desc | Colors
    001 | Item Desc | Red-Blue-Green

    How do I add products to the table, as well as grab them to display on page with PHP?

    My second question, is when going the Normalization route, how does my Color Table actually look? For example, what would the basic layout, as well as an example item look like?

    Thank you for any help you can offer, I really do appreciate your time. I'm learning!

  6. #21
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,322
    I'd probably recommend something like a comma or vertical bar "|" as the delimiter, as you could conceivably want to have colors such as "red-violet". Another alternative would be to serialize a PHP array and save that, but I'm not too crazy about that as it ties PHP too closely with the data. (Maybe you could json_encode() it, instead?)

    As far as a more normalized approach, using my earlier reply, a single item that comes in red and blue might be represented as:
    Code:
    item
    ==========
    id  name    description
    --- ------- --------------
    1   foobar  This is a test
    
    item_color
    ===========
    item_id  color_id
    -------- ---------
    1        1
    1        3
    
    color
    ===========
    id  color
    --- ---------
    1   red
    2   green
    3   blue
    ...
    n   other colors....
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  7. #22
    Join Date
    Oct 2009
    Posts
    13
    Hi NogDog,
    Thank you again for the reply.
    I will probably end up going the Normalization route.
    It's about time I just dive in and try it, thanks to everyone who had some words of advice!

  8. #23
    Join Date
    Jan 2007
    Location
    Wisconsin
    Posts
    2,120
    Have fun, and don't forget to index!
    Jon Wire

    thepointless.com | rounded corner generator

    I agree with Apple. Flash is just terrible.

    Use CODE tags!

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

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



Recent Articles