Dropbox has a great referral incentive program that gives a storage bonus based on whether a person is referred to Dropbox or refers others to Dropbox. What data base design is good for managing information of this nature but with a slight twist.
Say that getting referred to a service gives you a fixed storage bonus of 100 MB.
Further, say that a referrer gets a storage bonus equivalent to the purchased amount by the referral.
So if Tom refers Jerry, Jerry gets 100 MB. If Jerry buys another 300 MB, Tom gets 300 MB of storage free of charge for as long as Jerry is paying for the incremental 300 MB. Tom might also refer other people and get more free storage according to the same terms and Jerry can do the same. Given that the people that are paying at any given time is changing, you need to track what each person is entitled to based on their referrals and the amount their referrals are purchasing at any given point in time.
Any suggestions?
Best Answer
It's a Multi-Level Marketing system! Jeff Moden has written a a pair of articles here and here on efficient implementation of Hierarchical Reporting against a SQL Server database. There are a number of ways to store the hierarchical information but the two main ones are Adjacency List (each child has a parent foreign key) and Nested Sets (each parent stores details of its child hierarchy). Adjacency List is more intuitive and faster to update, while Nested Sets provides faster reporting.
Jeff has explained this topic far better than I can, and developed efficient SQL Server algorithms for converting an large Adjacency List tree into a Nested Set representation,
and then to report hierarchical subtotals (as you require here) from the Nested Set representation: