What is the best way to approach the database design for a group-buying site?
If you are not familiar with group buying, people purchase a deal, and when "n" amount of people purchase the deal, the deal is activated and the product is shipped to the people who ordered the deal. However, the deal does not ship until "n" people buy it. Moreover, the requirement is that a single deal can have many "group-buys", meaning that once "n" amount of people buy the deal, the deal does not close, but rather restarts for "k" amount of times.
What I thought of was to have a "deal" table that has many "group" tables (where each of the groups represents one of the "k" number of times the deal can be group buyed), and each "group" table has "n" amount of order tables. The group is "activated" when the group table has the correct "n" threshold number of orders for that deal.
An example Deal ---> Group ---> order | ----> order | ------> Group
Some problems I have with this design:
Upon initialization of the deal, you have to create all the "n" group rows that will be related to the deal, even though some of the "n" groups may not have any orders (especially if nobody purchases the deal). Thus, someone could easily specify to be able to group-buy the deal 100,000 times (making us create 100,000 rows), even if nobody group buys their product. Plus, if that person wants to delete their deal, we have to delete 100,000 rows.
Keeping track of the groups is a pain. When you process an order how do you know which group to add the order to? What if somebody (or everybody) wants a refund within a group, then which group do we add a new order to, since their might be one group with some people, and another group with some people.