Real Estate Contract Database Design

[j.t.w]

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 -

 

[Tony Toews]

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/