database design for composite elements
<p>I'm building a site that tracks donations and sales of items in a school auction.</p>

<p>Items can be sold individually or in lots, which are just groups of items bundled for sale as a single unit (like a gift certificate for a dinner Item bundled with a gift certificate for movie tickets Item).</p>

<p>Both of these things (Items and Lots) share fields like name, description, value. But Items have additional fields, like the donor, restrictions of use, type of item, etc. </p>

<p>I started by creating a table called Lot and an association table that lets Lots contain 1+ Items. </p>

<p>That works great for Lots. But that leaves me with a problem:</p>

<p>When Buyers win I need to record the win and the price. I'm doing that with a Win table that associates the Buyer with the Lot and the winning price. </p>

<p>But how do I deal with all the Items that aren't assigned to Lots? Should every item be in a Lot, just singly? That would make sense because it would work with the Win table scheme above, but I would need to automatically create a Lot for every Item that isn't already in another Lot. Which seems weird. </p>

<p>I'm sure this is a simple problem, but I can't figure it out!</p>

<p>Thanks!</p>