Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cardnal0602
Starting Member
11 Posts |
 Posted - 2009-01-13 : 14:43:00
|
| Hey I have a little problem designing an add-on to my existing Subscription database, and wondering if you may help me with some design theory.We sell automobile virtual tours.Currently, the Subscription ordering is very simple. The user sets up each virtual tour they want, pays a fee, and gets to use the individual virtual tour for 6 months. When the 6 months is up, a tickler will alert them and ask them to renew. 1 Virtual Tour: lasts 6 monthsThis data is stored in an Orders Table as follows:ORDERS-----------------------orderID, intdateOrdered, datetimeetc...-----------------------I can run a simple procedure query each day to determine who's tour is about to expire and alert them via email. This part of the database is working great, however we need to add one more feature:In addition to the existing feature, we want to allow the user to pay Monthly or Yearly and set up as many virtual tours as they want. The tours will only be viewable online as long as they have been paid up for the month or year, respectively.So the following is the final synopsis of what I need:a) 1 Virtual Tour: lasts 6 monthsb) Unlimited Tours: (monthly payment)c) Unlimited Tours: (yearly payment)I am in need of some db design insight as to how I may efficiently put this together within my existing database.Any help is greatly appreciated.Thanks! |
|
|
jordanam
Yak Posting Veteran
62 Posts |
Posted - 2009-01-28 : 11:34:47
|
| Hoo boy... this opens up lots of worms. There are a million ways to approach this, and they depend largely on the rest of the data model and some business logic that isn't provided. Consider the below as a very rough starting point.If a person places an ORDER, it sounds to me like that ORDER represents a single TOUR. Each TOUR has a PAY SCHEDULE (or TOUR TYPE). If you assign an ORDER END DATE (or TERMINATION DATE) at the time of ORDER, you can then calculate the exact dates payment will be due, as well as the amount -- potentially populating them in another table. That would be a very simple way of approaching this. If an ORDER represents any number of TOURS, the above model fails. As a bonus to defining a PAY SCHEDULE, rather than run a stored procedure to try to determine dynamically every run if a reminder should be sent, this model would allow you to create a reminders table, and just send reminders from that as often as you would like.Maybe that will help you frame it... |
 |
|
|
|
|
|
|
|
Managing Online Payments