Jump to content

Archived

This topic is now archived and is closed to further replies.

justravis

Normalizing a Payment DB?

Recommended Posts

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.