storing orders / wishlist for e-commerce site

[chronister]

I am creating an e-commerce site for fun. I have never tackled this kind of thing, so what the hey.

 

I am trying to figure out how to store product id's in the orders and wishlist tables.

 

they are set up like so.

 

orders

 

order_id | user_id | product_id | date_ordered | date_shipped

 

products

 

product_id  | name | description |  price | cat_id

 

wishlist

 

wishlist_id | user_id | product_id | date_created | timestamp

 

Each wishlist or order entry is going to generally have more than one product, what is the best way to store them? Should I simply store each product id seperated by a comma in the product_id field, or is there a better way to approach this.

 

Thanks for the input

 

Nate

[chronister]

*bump*

 

anyone?

[ted_chou12]

you can separate it by comma, or the better way i think is to change to a new line \r\n since your products might have comma in their name in the future, so it would be more convenient and less limitations, entering a new column every time would make it very messy.

Ted

[chronister]

Well I am going to store the product ID, not the title so my db would look like this.

 

orders

 

order_id    |  user_id |  product_id        |      date_ordered      |    date_shipped   

 

1557          45          15,52,19,266,447          1175230800              1175662800

 

Then I am thinking I would run the query and do an explode on the product_id string to break each of the products out individually

 

Or would it be better to have a separate table that holds an order id, and a product id. That way I can store a product id by itself for better searching and indexing.

 

I also saw the serialize / unserialize functions. That would allow me to store an array in the db, but would prohibit searching for a particular product id in an order.

 

Thanks for the assistance Ted