Contract database design

[wschlichtman]

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?

 

[Ken Snell [MVP]]

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.

 

[Jack MacDonald]

Ken

I agree with your basic approach, but I wonder how you handle the
requirement of the four original parties to the contract? In other
words, how do you ensure there is at least one each of Buyer, Seller,
BuyerAgent, and SellerAgent.

BTW, I am not sure whether this is a requirement of the OP, but I've
run into the same situation in the past and wondered about the
recommended solution.

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.

**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security

 

[wschlichtman]

This does cut down greatly on the number of tables and the complexity of the
database. Thanks.

I am a little suprised that the PropertyTypeID is placed in
tblContractDetails. How is the real estate agent able to select clients from
the Parties table? If no contract is yet in place, then no connections can be
made from the agent to the client.

 

[wschlichtman]

You are correct in your assessment that 4 parties are required to be
associated with the contract. Although Ken's solution provides the ability to
add the 4 parties, you can create the contract record with less than 4
parties involved. The good thing about Ken's solution is that you can more
than 1 Buyer and/or 1 Seller, which does take place often in real property
contracts.

 

[Ken Snell [MVP]]

Requiring that there be four original parties as a minimum, as you note, is
something that I would do via the form that is used to enter and manipulate
the data. The form can be programmed to have a command button to "save" the
data or to "complete" the contract -- and clicking the button would run
validation code to verify that the requirement has been met. I would not
attempt to use table-level validation to do this.

 

[Ken Snell [MVP]]

You can add the field PartyTypeID to the tblParties table if you wish, which
would allow you to assign a status to each "party"... if a party can have
more than one status (i.e., a buyer could also be a sellet), I would add two
records for that party -- one with the Buyer type ID, and one with the
Seller type ID. Then you can use a query to select just the buyers or just
the agents, etc. In this situation, the value of PartyID in
tblContractDetails will then identify the "type" of party (you wouldn't need
a PartyTypeID in that table then).

An alternative would be to have multiple fields in the tblParties to hold
boolean values for BuyerType, SellerType, etc. and then you would have just
one record for each party, and you'd assign that party to all of the types
that are appropriate... the concern with this setup would be that you must
continually modify the table structure if you want to have more PartyTypes.
If you are sure you'll have a stable number of PartyTypes, you could use
this less normalized approach with success.

Note that there is not a "one and only, perfect" way to design a database.
One should try to normalize the structure as much as possible... and use a
denormalized structure in some situations/tables where it's appropriate for
your needs.
--

Ken Snell
<MS ACCESS MVP>

 

[Jack MacDonald]

Yes, I understand and agree with your assessment. My question was more
about *process*

For example, this database design would typically lead to a
form-subform arrangement, where the main form is associated with the
contract and the subform with the Parties. If you were to add, say,
one new contract per day, simply adding the four Parties into the
subform would be no big deal. Strictly a manual approach, and entirely
appropiate in that situation.

But if you were distributing the application to a group of
unsophisticated data clerks, and they were entering many new contracts
per day, then the manual approach would become tedious and (more
importantly) subject to error if a clerk forgot to enter one of those
Parties. Perhaps the application should include some end-of-record
data checking to count the number of parties associated with the
contract.

Alternatively, an automated approach could be used, perhaps by
generating the records via VBA. But that brings its own set of
problems: how best to generate the four new records in the Parties
table and ensure that they are properly linked to the correct
contract?

And if you have enforced referential integrity, how can you create the
four new records automatically, and have them be linked to valid
parties *before* you know who the party is?

That's what I was trying to get Ken's opinion on.

You are correct in your assessment that 4 parties are required to be
associated with the contract. Although Ken's solution provides the ability to
add the 4 parties, you can create the contract record with less than 4
parties involved. The good thing about Ken's solution is that you can more
than 1 Buyer and/or 1 Seller, which does take place often in real property
contracts.

**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security

 

[Ken Snell [MVP]]

Comments inline...
--

Ken Snell
<MS ACCESS MVP>

For example, this database design would typically lead to a
form-subform arrangement, where the main form is associated with the
contract and the subform with the Parties. If you were to add, say,
one new contract per day, simply adding the four Parties into the
subform would be no big deal. Strictly a manual approach, and entirely
appropiate in that situation.

You actually might want to use multiple subforms if you have a "fixed"
number of party types... one subform to enter buyers, one for sellers, etc.
This simplifies the form-level validation because the main form would just
need to verify that each subform has at least one record in it. But this
slightly defeats the flexibility of the table structure that I suggested.

Instead, I would probably use just one subform:
ComboBoxToSelectPartyName
ComboBoxToSelectPartyType

The main form then would scan the subform's RecordsetClone to verify that
there is at least one record for each required party type. This is fairly
easy to do via VBA programming and can be programmed to be independent of
how many party types you have in the database:

Dim intTypeCounter As Integer
Dim intRecords As Integer
Dim rst As DAO.Recordset
Dim dbs As DAO.Database
intRecords = 0
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblPartyTypes", dbOpenDynaset)
rst.MoveLast
intTypeCounter = rst.RecordCount
rst.MoveFirst
Do While rst.EOF = False
With Me.SubformName.Form.RecordsetClone
.MoveFirst
Do While .EOF = False
If !PartyTypeIDField.Value = rst.Fields("PartyTypeID") Then
intRecords = intRecords + 1
Exit Do
End If
.MoveNext
Loop
End With
rst.MoveNext
Loop
If intRecords <> intTypeCounter Then _
MsgBox "At least one party type is missing!"
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

Note that the code could be rewritten to actually track the different party
types that are found, and to report the actual party type(s) that is/are
missing.
--

Ken Snell
<MS ACCESS MVP>

 

[Ken Snell [MVP]]

Comments inline...
--

Ken Snell

You actually might want to use multiple subforms if you have a "fixed"
number of party types... one subform to enter buyers, one for sellers,
etc. This simplifies the form-level validation because the main form would
just need to verify that each subform has at least one record in it. But
this slightly defeats the flexibility of the table structure that I
suggested.

Instead, I would probably use just one subform:
ComboBoxToSelectPartyName
ComboBoxToSelectPartyType

The main form then would scan the subform's RecordsetClone to verify that
there is at least one record for each required party type. This is fairly
easy to do via VBA programming and can be programmed to be independent of
how many party types you have in the database:

Dim intTypeCounter As Integer
Dim intRecords As Integer
Dim rst As DAO.Recordset
Dim dbs As DAO.Database
intRecords = 0
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblPartyTypes", dbOpenDynaset)
rst.MoveLast
intTypeCounter = rst.RecordCount
rst.MoveFirst
Do While rst.EOF = False
With Me.SubformName.Form.RecordsetClone
.MoveFirst
Do While .EOF = False
If !PartyTypeIDField.Value = rst.Fields("PartyTypeID") Then
intRecords = intRecords + 1
Exit Do
End If
.MoveNext
Loop
End With
rst.MoveNext
Loop
If intRecords <> intTypeCounter Then _
MsgBox "At least one party type is missing!"
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

Note that the code could be rewritten to actually track the different
party types that are found, and to report the actual party type(s) that
is/are missing.

I would not recommend this approach... it gets to be pretty "messy" to
maintain "Null" records, and you would have to forgo some table-level
validation to allow these records to exist. And you still would have to add
more records in order to have more than one buyer, or more than one seller,
etc... which means your form will have to handle generation of initial
"Null" records, and then not more than one "Null" record but still add new
records as needed.

 

[wschlichtman]

Thanks for the great suggestions. I'm going to work some test cases and try
to evaluate which approach will be best for my situation. If I run into any
other issues, and if you don't mind, I'd like to revisit this topic at that
time. Again, thank you for the help.

 

[Jack MacDonald]

Thanks for the insight. I hadn't considered using the RecordsetClone
property.

Comments inline...
--

Ken Snell
<MS ACCESS MVP>



You actually might want to use multiple subforms if you have a "fixed"
number of party types... one subform to enter buyers, one for sellers, etc.
This simplifies the form-level validation because the main form would just
need to verify that each subform has at least one record in it. But this
slightly defeats the flexibility of the table structure that I suggested.

Instead, I would probably use just one subform:
ComboBoxToSelectPartyName
ComboBoxToSelectPartyType

The main form then would scan the subform's RecordsetClone to verify that
there is at least one record for each required party type. This is fairly
easy to do via VBA programming and can be programmed to be independent of
how many party types you have in the database:

Dim intTypeCounter As Integer
Dim intRecords As Integer
Dim rst As DAO.Recordset
Dim dbs As DAO.Database
intRecords = 0
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblPartyTypes", dbOpenDynaset)
rst.MoveLast
intTypeCounter = rst.RecordCount
rst.MoveFirst
Do While rst.EOF = False
With Me.SubformName.Form.RecordsetClone
.MoveFirst
Do While .EOF = False
If !PartyTypeIDField.Value = rst.Fields("PartyTypeID") Then
intRecords = intRecords + 1
Exit Do
End If
.MoveNext
Loop
End With
rst.MoveNext
Loop
If intRecords <> intTypeCounter Then _
MsgBox "At least one party type is missing!"
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

Note that the code could be rewritten to actually track the different party
types that are found, and to report the actual party type(s) that is/are
missing.

**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security

 

[Ken Snell [MVP]]

I just noted that there is a logic error in the code I posted....it'll give
a false "positive" if three buyers and a seller are selected, but no
agents....One would need to rewrite the code to "flag" just one example of
each type that is found - probably by using four boolean variables (either
separate variables or an array) and set the corresponding one to True when
the appropriate type is found. Then test for the sum of the variables to be
equal to the negative of the "total number of types".

But I trust that it shows the concept.