Storing a list in Mysql Database
I have a list of 60+ products, I want to store the products subscribed by a client in a MySQL table. What is the best way to do this? As of now I am storing products as JSON under products column, as it is easy to parse from different languages. Like this:
{"products": ["SM", "NDVI"]} In this case the client has subscribed to 2 of our products. Another method I can think of is having a varchar the size of total products(60+) in the products column. Example let’s say there are 5 products (Just for the ease of explanation):
00000 ---> Original String 10010 ---> User has subscribed for product 1 and 4 What is the best way to implement this?
The standard relational SQL way to handle this problem would be to maintain three tables:
clients (id, name, ...) products (id, name, ...) client_products (client_id, product_id) -- primary key is client_id, product_id The third table above is called a junction table, because it exists to store relationships between clients and the the products to which they have subscribed. For example, to use your sample data, client_products might have the following entries:
client_id | product_id 1 | 1 1 | 2 And in products:
id | name 1 | SM 2 | NDVI 
