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>



    "wschlichtman" <> wrote in
    message


    news:...


    - Hide quoted text -
    - Show quoted text -

    >I am attempting to develop a database that tracks real estate contract
    > processes. This db also tracks clients and properties. I'm having a bit of
    > a
    > brain cramp trying to get my thoughts wrapped around relationships.


    > I have identified the objects I believe I need to make a functional
    > application but am having trouble linking them properly.



    > I have the following tables (this is not a complete list, but only the
    > ones
    > that are pertintent to my question)



    > tblContract
    > ContractID --> PK
    > Associated BuyerID -->FK
    > Associated SellerID -->FK
    > Associated BuyerAgentID -->FK
    > Associated SellerAgentID -->FK
    > PropertyID -->FK
    > ServiceAgentID -->FK



    > tblBuyers
    > BuyerID -->PK
    > Contact info -->fields



    > tblSellers
    > SellerID -->PK
    > Contact info



    > tblBuyerAgents
    > BuyerAgentID -->PK
    > Contact info



    > tblSellersAgents
    > SellerAgentID -->PK
    > Contact info



    > tblProperties
    > PropertyID -->PK
    > Property description



    > tblServiceAgents (there are actually many of these, depending on the
    > service)
    > ServiceAgentID -->PK
    > Contact info



    > In a real estate deal, the client info is created and tracked until a
    > contract is negotiated. At that point, I want to create a contract linking
    > all associated parties and properties, as listed above. The contract is
    > monitored and updated thoroughout its life until closing. At that point,
    > the
    > contract is marked complete and can no longer be updated.



    > Note that there can be more than one buyer and/or seller associated with a
    > contract so I believe my solution needs an additional table to create a
    > party. Is this correct?



    > In any case, am I on the right track here?
     
    j.t.w, May 11, 2011
    #1
    1. Advertisements

  2. j.t.w

    Tony Toews Guest

    On Wed, 11 May 2011 10:37:24 -0700 (PDT), "j.t.w" <>
    wrote:

    >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


    The above is all reasonable enough.

    > SellersTotalCommissionPercent
    > SellersTotalCommissionAmount
    > BuyersTotalCommissionPercent
    > BuyersTotalCommissionAmount
    > SellersBrokerCommissionPercent
    > SellersBrokerCommissionAmount


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

    Below is not reasonable.

    > SellersAgent1CommissionPercent
    > SellersAgent1CommissionAmount
    > SellersAgent2CommissionPercent
    > SellersAgent2CommissionAmount
    > BuyersBrokerCommissionPercent
    > BuyersBrokerCommissionAmount
    > BuyersAgent1CommissionPercent
    > BuyersAgent1CommissionAmount
    > BuyersAgent2CommissionPercent
    > BuyersAgent2CommissionAmount


    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

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.
Similar Threads
  1. Christopher Glaeser

    Companies, Title Companies, Real Estate Offices, Suppliers

    Christopher Glaeser, Feb 25, 2004, in forum: Access Table Design
    Replies:
    4
    Views:
    208
    Christopher Glaeser
    Feb 26, 2004
  2. Dean
    Replies:
    5
    Views:
    359
  3. Kenard

    designing real estate database using Access 2000

    Kenard, Feb 10, 2005, in forum: Access Table Design
    Replies:
    7
    Views:
    183
    bcbhatt
    Feb 22, 2005
  4. KGB003

    ANYBODY HAVE A REAL ESTATE RENTAL DATABASE?

    KGB003, Oct 13, 2005, in forum: Access Table Design
    Replies:
    13
    Views:
    395
    roccogrand
    Oct 21, 2005
  5. Erick Solms

    Database design for a real estate office

    Erick Solms, Mar 31, 2006, in forum: Access Table Design
    Replies:
    1
    Views:
    193
    Vincent Johns
    Mar 31, 2006
  6. Alain

    Real Estate Database

    Alain, Jan 31, 2007, in forum: Access Table Design
    Replies:
    3
    Views:
    107
    mikeinohio
    Feb 1, 2007
  7. UrLocalRealtor

    Does a real estate database already exsist?

    UrLocalRealtor, Jan 20, 2009, in forum: Access Table Design
    Replies:
    7
    Views:
    150
    John W. Vinson
    Jan 21, 2009
  8. deb

    real estate database design

    deb, Nov 2, 2009, in forum: Access Table Design
    Replies:
    7
    Views:
    228
Loading...