PHP/MySQL - Storing array data as JSON, bad practice?

Question

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?

Answer 1

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.

Answer 2

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.