justravis Posted July 10, 2003 Share Posted July 10, 2003 I\'m confused on how to create a db that tracks recuring and one time charges. Just dont know where one time charges fits in the puzzle. Any help would be greatly appreciated. Clients will be charged different prices for the same service. If prices go up, I want the db to still store fees paid in the past. That is why I did not combine the Subscrible & Transaction tables. Heres is the structure thus far: Organization a) Id B) name Contact a) Id B) Org_id c) Name d) Phone e) Email f) Address g) City h) State i) zip mop (method of payment) a) id B) org_id c) card_nbr d) exp_date e) verification_nbr Service a) Id B) name Subscribe a) Id B) Inactive c) Org_ID d) Serv_ID e) cost Transaction a) id B) Subscribe_id c) date Quote Link to comment Share on other sites More sharing options...
shivabharat Posted July 12, 2003 Share Posted July 12, 2003 We can help you better if you can explain the purpouse of each table Organization a) Id - Primary key B) name Contact a) Id - Foreign key to Organixation table B) Org_id // Not needed you have the ID in org table which can be used c) Name d) Phone e) Email f) Address g) City h) State i) zip mop (method of payment) a) id - Foreign key to Organixation table B) org_id // Not needed c) card_nbr d) exp_date e) verification_nbr Quote Link to comment Share on other sites More sharing options...
justravis Posted July 12, 2003 Author Share Posted July 12, 2003 thanx for reply. all the org_id are foreign keys to organization table. Dont u think this is best since one org could have multiple contacts & credit cards? Quote Link to comment Share on other sites More sharing options...
gizmola Posted July 12, 2003 Share Posted July 12, 2003 You did fairly well. The only thing I see you missing is a column for the actual amount of the transaction in transaction. Transaction should be your \"actual\" table, where the amount is stored. In that way, one time transactions and subscriptions can co-exist. If pricing changes over time, this won\'t matter, because the history of what was paid in the past will still be in The Transaction table. For one-time payments you can leave subscribe_id empty or better yet have a special Subscribe row (id 1 perhaps) that you use for all one time transactions. Quote Link to comment Share on other sites More sharing options...
justravis Posted July 17, 2003 Author Share Posted July 17, 2003 oops..i thought i replied...sorry ammount column...wut a concept...sometimes its easy to miss the obvious. I also missed the fact that the Transaction table tracks passed charges. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.