imagine this scenario:
I want to run a database that keeps track of all the purchases that every customer makes. So i'll have a purchases table along a customers table and a prodcuts table. The thing is that one purchase is made of many objects. To keep track of this my idea is to make another table objectslist that contains: a primary key as as generic id, a foreign key as the purchaseid that references the purchase table, and the product id as a foreign key from the products table.
The thing becomes tricky for 2 reasons: inserting a purchase can be troublesome because i've got to update 3 tables at once. Querying for a specific purchase and all the objects on the cart involves multiple joins and i haven't figured out how to do this yet.
If there's any other simple idea let me know. Thanks