current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 4 down vote favorite

I was wondering whether or not storing an array as a JSON string in a mysql text field is good practice.

I am creating an invoice which allows the user to add an unlimited number of products to the invoice. When the form is submitted, it takes out all the blank items and such, but I will typically be left with 2-5 items depending. Each item has a sku, price, name, and description.

My options for this situation are (1) to create a new product table, add each item as a new row, link it with the invoice table, and call both tables when accessing data. Or (2) store all the product data as a single JSON text field in the invoice table, and then I am not creating or accessing another table.

Since I am pretty rigid with MySQL programming, I get the feeling that using JSON in MySQL would be frowned upon. Am I right? Can someone shed some light on this?

share|improve this question

4 Answers 4

up vote 12 down vote

If all you need is just to store - then it is not a bad practice.

But if you need to perform any sort of processing, sorting or something similar - you need to normalize it.

share|improve this answer
    
Awesome, thanks for the answer. This actually makes perfect sense now, and until I need to allow searching/sorting by products, then I think I will be ok. –  Tim Aug 8 '11 at 6:04
    
If you start implementing database logic, then you've done something wrong. That's my rule of thumb when working with SQL databases. –  tjameson Aug 8 '11 at 6:05
    
I don't see when this would be ok though. You are basically right with what you say, but when you are saving products like this, you are bound to want to know which orders have sku xxx. Or try to easily add a productline to an order. Or do anything at all really. It's not really hard to add a table that has the rows-per-order, with an id, orderid, sku, price, name, descripiton field. Maybe even more later on. I don't see any reason to NOT do that. Its not like joining this table will cause performance issues. –  Nanne Aug 8 '11 at 6:13
1  
Occams Razor says that the best sollution is the one with the least new assumptions. It would be fair to say that if you start saving orders that you should save them as orders. So an order has several rows. Only when there is a reason to not-save them as they are (for instance: you are facebook, or you have performance issues), then you need to think about something else, like the json-in-a-table. But even then you might want another sollution. So occams razor says: just do it the 'default' way (normalized), unless you have a reason to do something like this. –  Nanne Aug 8 '11 at 8:25
1  
@Nanne: default way is the one that doesn't bloat our code, queries and schema. And storing everything-in-json doesn't violate 3rd NF (since we treat that field as an atomic property) in this case. –  zerkms Aug 8 '11 at 8:30
up vote 6 down vote

Because others have answered your question more directly, I'm going to take a fringe approach and address future maintainability instead.

Storing a variable number of items that are just begging to be a database entity (SKU, Price, Name, Description) as JSON may be fine now, but it's going to lead to a ton of data duplication.

Instead, do what you said and create a table for all the products. Then create another table for invoices_have_products. You can then pull every row from invoices_have_products where the invoice ID matches, and then pull every row from products where the product ID matches the rows you pulled from invoices_have_products.

It might get a little tedious right now, but when all your data is in neat tables and easily queryable, you'll be much happier. Think about the nightmare of running reports on millions of text fields with JSON. Absolutely horrifying.

To answer a part of your question: No, I don't think this is good practice and it looks a little bit like bad practice, to be honest.

share|improve this answer
up vote 0 down vote

I'm no MySQL expert, but I think it depends on what you want to accomplish. Do you want the products which you have saved to an invoice to be searchable? If so, you are better off going with a relational database structure.

If you don't have the need to search, you could store the data as a JSON string, or simply as a serialized array, and save yourself from using JSON at all.

Depends on your needs really.

share|improve this answer
up vote 0 down vote

If you store it in JSON you won't be able to query it using SQL. For example I cannot find out all the invoices which bought more than 3 of Product A.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.