Real Estate Contract Database Design

Discussion in 'Access Table Design' started by j.t.w, May 11, 2011.

  1. j.t.w

    j.t.w Guest

    Hi,

    I'd like to revive a thread from July 2005 that is basically what I'm
    needing to accomplish (please see below for previous thread post). I
    plan on implementing the table design like suggested below but, I also
    need to store commission data as well.

    For a real estate contract, the total commission can range from 3 to 7
    percent (of the sales price) or a specific dollar amount. The total
    commission is split between the sellers and buyers brokers (typcially
    50/50, but not always). Each real estate broker will get a percentage
    or a specific dollar amount and each real estate agent will get a
    percentage or specific dollar amount of the respective commission
    amounts.

    Example:
    Sales Price: $200,000
    Total Commission: 6% (or $12,000)
    Sellers Total Commission: 50% of Total Commission (or $6,000)
    Buyers Total Commission: 50% of Total Commission (or $6,000)
    Sellers Broker Commission: 50% of Sellers Total Commission (or $3,000)
    Sellers Agent Commission: 50% of Sellers Total Commission (or $3,000)
    Buyers Broker Commission: $2,500
    Buyers Agent1 Commission: $2,250
    Buyers Agent2 Commission: $1,250

    There can be multiple agents on either side of the deal and the
    commissions can be split between each party in various ways.

    I'm thinking the commission fields should be stored in the tblContract
    table like...
    tblContract
    ContractID
    ContractDate
    ContractDescription
    PropertyID
    ContractSalesPrice
    TotalCommissionPercent
    TotalCommissionAmount
    SellersTotalCommissionPercent
    SellersTotalCommissionAmount
    BuyersTotalCommissionPercent
    BuyersTotalCommissionAmount
    SellersBrokerCommissionPercent
    SellersBrokerCommissionAmount
    SellersAgent1CommissionPercent
    SellersAgent1CommissionAmount
    SellersAgent2CommissionPercent
    SellersAgent2CommissionAmount
    BuyersBrokerCommissionPercent
    BuyersBrokerCommissionAmount
    BuyersAgent1CommissionPercent
    BuyersAgent1CommissionAmount
    BuyersAgent2CommissionPercent
    BuyersAgent2CommissionAmount

    As you can see, this looks really ugly and doesn't seem right to me.
    Because of all the different agreements between brokers and agents,
    I'm not sure of a good way of implementing commissions into the
    database design. Any ideas on how should I store the commission data?

    Thanks in advance for any suggestions you have.
    j.t.w


    ----------------------------------------------------------------------------------------------------------------------------------------------------------------
    Original Thread Link...
    http://groups.google.com/group/micr.../9a76e2a836f30e79?hl=en&q=wschlichtman&pli=1#


    I would agree with some of what you've posted....

    I would change tblContract table to this:


    tblContract
    ContractID
    ContractDate
    ContractDescription
    PropertyID
    (other single-item descriptors for a contract)


    I would create a table called tblParties:


    tblParties
    PartyID
    PartyName
    PartyAddress
    (etc.)


    Then I would delete the tblBuyers, tblSellers, tblBuyerAgents,
    tblSellersAgents, and tblServiceAgents, as the above tblParties will
    take
    the place of all these tables.


    Then I would create a table (call it tblContractDetails):


    tblContractDetails (all three fields are a composite PK)
    ContractID
    PartyTypeID
    PartyID


    In the above table, PartyTypeID would be a value in a tblPartyTypes
    that
    identifies if the entry is a buyer, seller, agent, etc. This table
    will let
    you have an unlimited number of buyers, sellers, agents, etc. for a
    single
    contract.


    --


    Ken Snell
    <MS ACCESS MVP>



    message




    - Hide quoted text -
    - Show quoted text -
     
    j.t.w, May 11, 2011
    #1
    1. Advertisements

  2. j.t.w

    Tony Toews Guest

    The above is all reasonable enough.
    The above may not be needed if you create a separate agent table as
    per my suggestion below.

    Below is not reasonable.
    What happens if there are more than 2 agents? And they will tell you
    it almost never, ever happen, right?

    I did a bunch of work for a municipal government system 20 years ago.
    They all told me that 3 or 4 owners of a property was enough. Well, I
    was going to put each of those owners as separate records in a table
    anyhow.

    Then the one county showed me an interesting case. A member of the
    German nobility had purchased some farmland near the city as an
    ivestment. He subsequently died. Now his eight heirs owned the land
    jointly and each had to be notified of taxes due etc on an annual
    basis.

    So you should be putting the sellers and buyers agents and commission
    percenages in two separate tables. That is the names of the sellers
    and buyers agents in one "master/lookup" table. Then a
    seller/buyer/both flag along with percentage and amount. in a child
    table.

    Tony
    --
    Tony Toews, Microsoft Access MVP
    Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
    Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
    For a convenient utility to keep your users FEs and other files
    updated see http://www.autofeupdater.com/
     
    Tony Toews, May 13, 2011
    #2
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.