For my college project, I have two SQL tables

one has the user data

USERNAME | PHONE NUMBER | RFID CARD NUMBER

other is a product database

PRODUCT NAME |  PRODUCT PRICE | PRODUCT QUANTITY | BARCODE NUMBER 

The idea is when I scan the barcode with an app on the phone, the scanned products, and their prices get added to the user's shopping database.

the shopping database is

USER ID | PRODUCTS SCANNED | EXIT STATUS 

The USER IDis the foreign key of the RFID CARD NUMBER

I wanted to add the product barcode: price as an array/map, the only way I learned to add arrays into SQL is by using the JSON data type.

EXIT STATUS is a boolean column that is true/false based on whether the JSON column is null or not.

I know only the basics of SQL and from what I have read the only way to store an array in MySQL is JSON. but I don't know much about the usage of JSON data type.

Is there any other way to do the exact same approach of storing arrays, or better approaches that I can use for the current requirement?

CodePudding user response：

I recommend creating another table:

SHOPPING CART | PRODUCT | PRICE

The SHOPPING CART column is a foreign key to your shopping table.

The PRODUCT column is a foreign key to your products table.

In this table, you can insert any number of rows for the same shopping cart, with one product per row.

You should copy the price of each product to this table because that records what the customer paid for the product when they purchased it. Just in case the product price changes next week.

This is much better than using JSON. It's easy to insert another row. It's also easy to delete a row, in case the customer puts a product back. It's also easy to do things like SUM() the prices for the shopping cart. Any of those things are harder if you use JSON.

In general, you should try to design databases in normal forms instead of using JSON wherever possible. It makes your code easier for a wider variety of queries, and your data has better integrity if you avoid using JSON.