Jump to content


Photo

Normalizing a Payment DB?


  • Please log in to reply
4 replies to this topic

#1 justravis

justravis
  • Members
  • PipPipPip
  • Advanced Member
  • 115 posts
  • LocationSan Diego, CA

Posted 10 July 2003 - 07:28 AM

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
PHP version 5.2.6 | MySQL version 5.0.51a-community

#2 shivabharat

shivabharat
  • Members
  • PipPipPip
  • Advanced Member
  • 371 posts
  • LocationChennai, India

Posted 12 July 2003 - 08:24 AM

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
Knowledge --- Reading Enriches Mind But Sharing Enhances It.[br][br]Note: Before you request help enusre that you have had a look at the tutorials @phpfreaks

#3 justravis

justravis
  • Members
  • PipPipPip
  • Advanced Member
  • 115 posts
  • LocationSan Diego, CA

Posted 12 July 2003 - 06:13 PM

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?
PHP version 5.2.6 | MySQL version 5.0.51a-community

#4 gizmola

gizmola
  • Administrators
  • Advanced Member
  • 4,664 posts
  • LocationLos Angeles, CA USA

Posted 12 July 2003 - 07:57 PM

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.

#5 justravis

justravis
  • Members
  • PipPipPip
  • Advanced Member
  • 115 posts
  • LocationSan Diego, CA

Posted 17 July 2003 - 06:59 AM

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.
PHP version 5.2.6 | MySQL version 5.0.51a-community




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users