Jmac's avatar
Level 5

Serialize object-properties as JSON in MySQL or other ways? Best practice?

I am looking into modernizing a legacy PHP/MySQL/SQL-soup for a client who runs a printing service. You can upload your images and order a print on a canvas, wall paper, calendars and so forth.

One thing the customer struggles with today is to add more products easily, a more flexible approach.

As you can imagine every product shares some properties and restrictions regarding sizes and options, but there are many unique properties.

The current design of the DB is a field on the order_rows-table for each property. If the want to add a new product (like a printed calendar) means that they most likely have to add a field or two in the table to store same special properties unique for the product.

PHP-wise I am thinking of refactoring to Laravel an use approach with a Product_Interface which classes like Product_Canvas, Product_Wallpaper extends. Each class can hold their own special properties (size, materials, mountings, crop information) and validations (eg. cannot be wider than 30 cm).

BUT what's the best way to save the user's special properties for a products in a relational database when a customer has landed an order for printing? Easiest way would be just to serialize these properties to JSON and store it to a field. That's it?

Or...

Break down each property and store in a separate table with product_id, property_key, value (which reminds me of Wordpress' post_meta-table...). Looks nicer but a lot more difficult or query.

Create a new table for each Product?

I guess my question boils down this: how store if there is no 1:1 relation between an object's property and fields in a database-table?

What do you reckon? What's your experiences?

Thanks in advance!

0 likes
0 replies

Please or to participate in this conversation.