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.
Originally Posted by svidgen
That's certainly one of the other costs, though there are more.
is the inability to efficiently search, update, or remove single colors globally across all product rows.
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.
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.
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!
// it's a one liner ...
$colors = preg_split(",\s+", $row['colors']);
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.
Originally Posted by svidgen
Last edited by Mindzai; 03-12-2010 at 04:15 AM.
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.
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!
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:
id name description
--- ------- --------------
1 foobar This is a test
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
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!
Have fun, and don't forget to index!
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
Tags for this Thread