Shopping carts, product tables and database relations
I have a simple web app with a shopping cart type system. I have one table holding all product details (ie. productname, description, price, etc.), another which lists order items (this one contains product_id, order_id) and finally the order table itself (holding date, user_id, payment details, order status, etc.)
This setup makes it easy to query the order list and get details for the products related to the order and makes it easy to assign as many items to each order as I want.
Now the problem, I need to add a second type of product to the database with very different product details. I can't put the new products in the existing product table. But putting them in a new table would make it impossible to link them to an order in the current setup.
Would my best bet be to strip the product details from the product table itself and move them to another relational table so I can use the basic product table for everything?
Hope I haven't made this sound too confusing, any thoughts would be greatly appreciated
Maybe I didn't explain quite right or maybe it was too much of a newbie thing to answer but an Single Table Inheritance relation was the answer here.. Just thought I'd post it here for future generations