problem with payments for multiple jobs

[antonyx]

hello all.. trying to resolve an issue in my system..

basically i run a taxi firm.. for those that dont know.

this issue deals with credit card payments, and in particular payments for multiple transfers.

lets say you are booking 2 journeys..

the first journey is on 2nd June from London Heathrow to your hotel in Kensington

the return journey is on 5th June from your hotel back to Heathrow

you want to pay for both journeys at once with your credit card.

both transfers cost £40 each, 40x2 is £80 plus 5% transaction fee is £84.

we keep the credit card details on file to save our passengers time when they want to pay for their journey using the same card.

so lets say we have a job table.. and a credit card table..

Card Table
cardid (pk)
cardtype
nameoncard
last4digits
secnumber
expirydate
startdate
issuenumber

Job Table
job id (pk)
jobdate
jobtime
jobnotes

should i create a new transaction table which holds the jobids that were paid for and the cardid that paid for it..?

shall i include the price of the job in the jobtable.. and store the transaction fee in the transaction table?

if this is clear i would like to hear a few opinions on this.. i will be thinking hard about this over the next few days so i might come to some conclusion but what do you guys think
cheers.

the reason im asking this is because at the moment i am storing the credit card details.. pricing information and job details in the job table..

when i send my passenger an email confirmation.. it sends them the job details, card details and payment details..

this is fine for single transfers.. but if they book more then one transfer i have to send them multiple emails outlining each job individually.. and if they wish to use the same card i have to find their card from the original job and copy those details.

if someone books 4 jobs and pays for them all with one card and one transaction i really have difficulties, i would have to send them 4 emails (with 4 job details respectively) and i would have to include all the payment details in each job to verify it has been paid.. this means i would have to enter their card details 4 times.. and even though each job might be £20 each.. i would have to write £80 in the total charged field because it is covering all the jobs..

anyways.. if you can spare me a thought i would be grateful.. thank you

 

[spasticus]

this might seem random and pointless but the answer might help:

how do you work out how much a job costs?

 

[antonyx]

ahh.. well there is no one way.. for long distance jobs it would be based on mileage.. for London areas we use set fares..

there are also other issues that may be charged such as additional drops, driver waiting time if the flight is delayed..baby seats cost extra.. etc

basically this picture will explain how the pricing works..

http://www.londonheathrowcars.com/charges.jpg

one transaction by credit card might pay for one or more jobs
one job might have one or more pickups and one or more destinations

its quite messed up

 

[antonyx]

here is another issue..

if a "job" involves two vehicles (and therefore 2 drivers)

at the moment i am entering the same record twice (job details - eg date, time, passenger, pickup, destination), the only difference being the driver..

 

[antonyx]

i think i need to do some serious normalisation here..

 

[antonyx]

here is something else to consider..

jobs
people
places
drivers
credit cards


a job may have one or more pickups
a job may have one or more destinations

a pickup will be one place
a destination will be one place

a job will have one booker
a job will have one or more passengers

a booker will be one person
a passenger will be one person

a job may have one or more drivers

a payment will involve one or more jobs
a payment will involve one credit card

 

[spasticus]

normalisation would be a good idea. off the top of my head i cant figure out what you going to need exactly but i think if you did normalisation you'd get something similar to this (i changed a couple field names so i could get my head around it better):

Customer table (Card table)
CustomerID (pk) (CardID)
CustomerName
CardType
Last4Digits
SecNumber
ExpiryDate
StartDate
IssueNumber

Booking table (Job table)
BookingID (pk) (JobID)
CustomerID
TransactionFee
Total

this would be the composite key tabel - although i cant think what other field would be part of the key

BookingID (JobID)
??
DriverID
Date
Time
PickUpLoc
Destination
Cost

Driver table
DriverID
DriverName

 

[antonyx]

the thing is not all jobs will be card jobs.. some will be cash..cheque etc

also sometimes the same customer will use different card details.. and we will need to store the customer contact details also..

i think i will do an entity relationship.. then list the attributes then normalise and see what i come up with..

 

[gemma-the-husky]

antony

i think in order to solve your original example - ie a passenger pays up front for a journey in the future - you effectively want to take a payment for a job that hasnt been done yet. this is in accounts terms like an "on account" payment - that needs to be treated as temporarily unallocated, and sorted later - ie matched against the job when it is finally arranged

in general, the whole system seems quite complex, so it probably is worth considering it carefully again