List:General Discussion« Previous MessageNext Message »
From:Mark Goodge Date:August 10 2010 7:34am
Subject:Re: searching serialized data stored in mysql
View as plain text  
On 09/08/2010 18:33, Norman Khine wrote:
> hello, i have a table called checkout, this has a row called products
> which has contains a python dictionary data, like
>
> http://pastie.org/1082137
>
> {products: [{productId: 123, productName: APPLE,
>              productPrice: 2.34, productUrl: http://appple-fruits.net,
> productDescription: "nice juicy apples"},
>                   {productId: 333, productName: ORANGE,
>              productPrice: 4.21, productUrl: http://appple-fruits.net,
> productDescription: "nice juicy oranges"},
>              ...]}
>
>
> what will be the correct way to make a search on this data, for
> example if i want to search for a range of products with a price
> between €2 - €4
>
> is this the correct way to store this type of data?

No, it isn't, not if you're going to be searching for individual 
elements of the array. Your products table should really have separate 
columns for productId, productName, productPrice, productUrl and 
ProductDescription. If you want to be able to store arbitrary key=>value 
pairs then a separate table with columns for productId, keyName and 
keyValue would be a useful way of doing it.

Storing serialized data in single MySQL column is really only useful if 
that data will never be directly manipulated by MySQL itself - that is, 
if its only ever being used as the input to a separate program that 
handles all the searching and manipulation.

Having said that, I've just looked at the URL you link to 
(http://pastie.org/1082137) and what that's demonstrating isn't an 
example of a products table, it's an example of a ecommerce checkout 
table where the cart contents are a single column of serialized data 
within the cart line. Personally, that's not the way I'd do it[1], but 
it is a perfectly valid method if you start from the assumption that 
you're never going to want to find individual orders by searching the 
contents of the order. If you're looking at this as an example of a 
product table that you would use to search for products, then you're 
misunderstanding the example being given.

[1] I'd have a cart table with one line per cart, and then a separate 
cart_contents table with one line per product and a cart_id column which 
links it to the cart table. That also allows a separate cart_address 
table which can have multiple addresses per cart (eg, billing address, 
delivery address).

Mark
-- 
http://mark.goodge.co.uk
Thread
searching serialized data stored in mysqlNorman Khine9 Aug
  • Re: searching serialized data stored in mysqlMark Goodge10 Aug