My team is working on a billing module, where our users should be able to add line items to the invoice and store these line items in our database. These items can be edited after the initial creation.
The invoice item will look something like this.
LineItemName
EnteredBy
Quantity
CostPerUnit
Subtotal
One of our team members suggests that we store the data for this row in a JSON column, rather than in multiple SQL columns. His argument is that it will be easier to store all position data in a single JSON column instead of writing code to determine which positions can be deleted, updated, reordered, or otherwise processed from the initial state of the database.
I have not worked with JSON columns before, and from what I can say, when using these functions there are a number of performance problems, as well as some additional difficulties when building queries from a JSON data column. We will have to report on these positions, so performance is certainly a concern. We are also launching SQL Server 2012, which, as I understand it, does not contain native support for JSON columns if we are not upgrading to SQL Server 2016. Side note: we will most likely move on to MYSQL over the next 2-3 years.
- , ? , , .