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

  • You can use a trigger on the relevant tables to perform the updates. If you want an example of a store database you can check out AdvertureWorks for SQLServer or You can download OpenCart (it's a PHP based Open-Source eCommerce store, contains everything you said.) – ZeroBased_IX May 3 '15 at 12:17
  • Seems like a reasonable design. A purchase may contain many products and a product may appear in many purchases so it's clearly a n-to-n relationship. What's troubling you with joininig multiple tables? As for updating 3 tables at once, that's what transactions are for. – zgguy May 3 '15 at 12:21
  • If you have the standard Order/OrderItem table layout, you already track all the purchases of every customer. What more do you need? – TommCatt May 4 '15 at 23:34

When you make a purchase, you're really only inserting into two tables: Purchase and ObjectsList.

Querying for a Purchase and the Products in a Purchase is simple:

    Purchase JOIN ObjectsList
        ON ( Purchase.idPurchase = ObjectsList.idPurchase )
    JOIN Product
        ON ( ObjectsList.idProduct = Product.idProduct )
WHERE idPurchase = ?;

I think your design is good, there's nothing wrong with needing to insert into or query multiple tables.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.