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

    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?

      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

        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")

          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.

            Write a Reply...