title: How to record customer purchases?
body: <p><a href="https://i.stack.imgur.com/PhCXy.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/PhCXy.png" alt="Simplified ERD" /></a></p>
  <p>I am creating a database which keeps track of customers, items, transactions, and more. I am having trouble describing the relationship between <code>Customers</code> and <code>Items</code> which is represented by <code>buys</code>. The relationship, <code>buys</code>, represents a <code>Customer</code> purchasing <code>Items</code> at a register. The transaction, <code>buys</code>, may include one item, multiple items, and/or the same item multiple times in a single transaction; additionally, multiple <code>Customers</code> may purchase the same item on separate occasions.</p>
  <p><em><strong>What attributes does the relationship <code>buys</code> need in order to keep track of all the items purchased?</strong></em></p>
  <p>Currently, I am proposing an attribute <code>buys.id</code>; however, it seems a composite key is necessary. The keys could (<code>buys.id</code>, <code>Customer.id</code>, <code>Items.id</code>), yet if a customer were to add the same item twice, then these rules would break. I would also include a <code>date</code> attribute. Additionally, a <code>quantity</code> attribute could be added to relationship.</p>
  <p>The relationship could be described like so:
  <a href="https://i.stack.imgur.com/wZ3Mx.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/wZ3Mx.png" alt="Relationship 2" /></a></p>
  <p>It does appear that this would be able to satisfy all the rules laid forth, but it seems a bit cumbersome.</p>
  <p><em><strong>Is there a way to improve this design?</strong></em></p>
  <p>(I need a refresher on this stuff if anybody knows a good book or video)</p>
  <p>EDIT:</p>
  <p><a href="https://stackoverflow.com/a/10367237/6742108">This answer from another post could answer this question.</a></p>
  <p>I'm using MariaDB and python.</p>