I need help creating the schema for a database to hold information relative to a web user's shopping cart/session. Each session will have a session ID, a time stamp holding time of creation, an expiry time stamp, a boolean indicating whether or not the purchase was completed, and then a list of products, and their associated quantities and prices. Every user will have only one ID, origination time stamp, expiry time stamp, and purchase boolean, but they may reference one or many products with varying quantities and prices.
My initial thought was to have a session table and a product table, but I can't seem to get past thinking the session ID and the product information will become redundant in big orders and across different sessions. In short, if different rows of the product table consistently reference repeat product numbers and session IDs, is that going to get messy?
Am I on the right track? Or can you propose an alternative design pattern that I have yet to consider?
Please let me know if you need more information. Thank you in advance!