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.
Best Answer
This is an idea I came up with that can help (see attached image).
The Deals table is simple. It has an ID that defines the details of a deal (like product name, origin, etc). That info through the ID is passed onto a deal_instance table thats designed to setup the individual sales to that deal. This is where your "K" comes in.
Then I added a customer deals table because any number of customers can buy any number of deals and vice versa, but in the customer deals table, the number bought is recorded so you know which customer bought which deal and how many times.
Then you use that information to find out when n people bought the deal and how many times. Once that value is met, then you go on and create another instance for the same deal just by adding another record to the deal_instance table using the same deal ID. If I'm not mistaken, the left most line needs to be an arrow, not a slash. (1 to many relationship).