0

I'm working on an iOS shopping application. And I use python to write my backend.

As for the cart part, I want to store a bunch of product ids in a list object.

By the way, I use SQLAlchemy to process sql queries.

As I don't have much experience in database design, I wonder what is the best way to store a list object into mysql.

I think this is a one-many relationship issue and I'm wondering whether I need a third table except for a CartItem table to store products' ids and a Product table to store product's id?

Someone said that serialization is a choice. What is serialization?

Or can I just split list elements by comma and store as a string?

I've referred some question answers like

Store list of values (such as interests)

store list of IDs mysql

How to store a dynamic List into MySQL column efficiently?

Community
  • 1
  • 1
jinglei
  • 3,269
  • 11
  • 27
  • 46

2 Answers2

0

I recently did an app using python / sql alchemy for someone to place an order from a website. What I did was, create 2 tables. One stored the items that the user was ordering/ordered, and the other contained the order information like date ordered, address, special instructions, order number. It was a 1-many relationship. When the user was adding the items they were placed in 'in progress' status, when he paid for them they were placed in 'completed' status. That way I knew what was in his cart at all times and what items had already been ordered. I kept track of all shipments / orders using the other table.

The webapp is up and running here, you will have to login to test the ordering functionality.

Tikkaty
  • 772
  • 1
  • 8
  • 24
0

Saving a single string with comma separated id's is possible, but I think it's not a clean solution. It's actually an example of serialization = translation of an object into a format that can be stored and reconstructed later (wikipedia). Especially in simple example like this, I'd suggest using a third table:

I don't understand what you store in table CartItem, but I'd use a schema similar to this:

TABLE product
-------------
id | price | name  | ...
1  | 10    | socks | ...
2  | 34    | shirt | ... 

TABLE user
-------------
id | name | ...
1  | John | ...

TABLE user_cart
-------------
user_id | product_id | count | ...
1       | 1          | 5     | ...
1       | 2          | 1     | ...

Single row in table user_cart is used to store one product/user relation.

Using SQLAlchemy you will be able to get user's cart by simply calling e.g. User.cart_items after some configuration. Check backref.

David Viktora
  • 605
  • 8
  • 11