How to start an escrow script?

sprogo

Hello,

I would like to write a script for establishing an escrow system but I have no idea for how should be structure of database table. In this system, users can send money to "escrow system" through any kind of payment gateways (a user database table and payment gateway scripts are already available). There are 3 parties in transactions, a buyer, a seller and an escrow account. And system should work as folowings:
- Buyer send payment to escrow account. And when payment confirmed by escrow mods/admins emails should be sent to buyer and seller
- Seller send the item to buyer. When he/she sends the item, emails should be sent to buyer and escrow account.
- If buyer gives confirmation when he/she receives the item without any problem, money from the escrow account should be transferred to seller. If buyer does not give a confirmation in a certain period or gives negative comments system should work to backwards ..Confirmation info should be email to seller and escrow account.

Any idea, clue or thought will be very helpfull for me. It is
really a pain in my neck for a week.

Thanks guys
Sprogo

Andrew_W_Peace

I looked around but I couldn't find anything that looked promising 🙁
I think the best solution is to make it all yourself or to hire somebody to do it. This forum and ozzu.com are good places to look for freelance coders.
I did try this search, however. Maybe you can find something here:
http://www.google.com/search?hl=en&q=php+media+manager&btnG=Google+Search

Good luck!

justsomeone

Technically, it sounds straightforward enough. Legally it could be a nightmare for you, if you plan on carrying all the risk.

I would suggest that a couple of tables can give you what you need here for a basic system

USERS

userid (integer, autoincrement)
username (text)
password (text)
allowed_to_purchase (Y/N)
allowed_to_sell (Y/N)
allowed_to_adimn (Y/N)
...various other fields such as contact info etc

TRANSACTIONS

transactionid (integer, autoincrement)
sellerid (Foreign Key to user table)
buyerid (Foreign Key to user table)
description (text)
value (real)
status (FK to transaction_status table)

TRANSACTION_STATUS

statusid (int)
meaning (enum "Awaiting Payment", "Payment Received by Escrow", "Order despatched", "Order received", "Seller Paid")

This is a very basic data set but sould get your prototype up and running.

Form 1 could allow people to register.

Form 2 could allow an admin to assign buy/sell/admin rights to users

Form 3 could allow a buyer to create an escrow payment

Form 4 could allow an admin to acknowledge receipt of payment

Form 5 could allow a seller to signal dispatch of goods

Form 6 could allow a buyr to acknowledge receipt

Form 7 could allow an admin to release payment for the goods

How's that?

sprogo

Thanks justsomeone,

I have to confess that I am not good at neither php nor mysql but I have a programming background in c++ and some knowledge on msaccess although it was a really past time like 10-12 years ago.

It will be a starting point for me. Actually this escrow system is gonna be integrated to an auction script so there are plenty of tables already running. Thus, as you suggested, a new escrow_transaction_status table should be created and "escrow_status" field should be added to auction_transaction_table. It sounds logical.

For legal risks, all buyers and sellers must accept a user agreement (will be prepared by a lawyer) during registration and credit card payments will be held on a secured system and through credit card gateways provided by my bank, other money transactions should be done on users own online/offline bank accounts by users and they will only inform us about payment such as user_id, auction_id, transferred amount, from which bank account, etc with a form on our auction pages. Do you think that we should take any other risks on above roadmap?

Thanks again, I am gonna keep you informed if it works.

Sprogo

sprogo

What do you think about the speed of the database? Is it better to use tinyint instead of ENUM data type and create another table with the values such as ("Awaiting Payment", "Payment Received by Escrow", "Order despatched", "Order received", "Seller Paid") ?

justsomeone wrote:
TRANSACTION_STATUS

statusid (int)
meaning (enum "Awaiting Payment", "Payment Received by Escrow", "Order despatched", "Order received", "Seller Paid")

liquorvicar

This is already a lookup table

Originally Posted by justsomeone

TRANSACTION_STATUS

statusid (int)
meaning (enum "Awaiting Payment", "Payment Received by Escrow", "Order despatched", "Order received", "Seller Paid")

referenced by

TRANSACTIONS

transactionid (integer, autoincrement)
...
status (FK to transaction_status table)

So, I guess the answer to your question is "Yes, better to use TINYINT and reference to another table" but this is what is already being proposed.