Normalisation

  • Thread starter Thread starter Joe Bloggs via AccessMonster.com
  • Start date Start date
J

Joe Bloggs via AccessMonster.com

Can anyone normalise this from UNF up to 3NF???
Seller Number[PK]
Seller Name
Seller Address
Property Ref Number
Property Valuation
Property Asking Price
Lawyer Number
Lawyer Name
Lawyer Address
Contract Number
Date Contract Signed
Viewing Date (Repeating)
Viewing Comments (Repeating)

Any help would be much appreciated Thanks
 
Can a seller sell more than one property?
Can there be more than one lawyer per sale?
Will the same Lawyer be involved in more that one sale?
Can the property be sold more that once?
 
Can anyone normalise this from UNF up to 3NF???
Seller Number[PK]
Seller Name
Seller Address
Property Ref Number
Property Valuation
Property Asking Price
Lawyer Number
Lawyer Name
Lawyer Address
Contract Number
Date Contract Signed
Viewing Date (Repeating)
Viewing Comments (Repeating)

Any help would be much appreciated Thanks
Click to expand...

Not without knowing more about your business, though here's a first
pass: * indicates Primary Key

Sellers
*SellerNumber
SellerName
SellerAddress

Properties
*PropertyRefNumber
SellerNumber <who's selling this place>
Valuation
AskingPrice

Lawyers
*LawyerNumber
CompanyName
LastName
FirstName
Address
... other contact info

(this might be two tables, LawFirms and Lawyers, in a one to many
relationship)

Contracts
*ContractNumber
PropertyRefNumber
LawyerNumber
SigningDate

<unless there are many to many relationships, such as one contract for
selling several properties as a bundle>

Viewings
*ViewingID
PropertyRefNumber
ViewingDateTime
Comments


John W. Vinson[MVP]
 
Joe said:
Can anyone normalise this from UNF up to 3NF???
Seller Number[PK]
Seller Name
Seller Address
Property Ref Number
Property Valuation
Property Asking Price
Lawyer Number
Lawyer Name
Lawyer Address
Contract Number
Date Contract Signed
Viewing Date (Repeating)
Viewing Comments (Repeating)

Any help would be much appreciated Thanks
Click to expand...

Seller Record - one record for each Seller
--------------
Seller Number[PK]
Seller Name
Seller Address
Rest of seller information

Property Record - one record for each Contract
---------------
Property Ref Number
Property Valuation
Property Asking Price
Property Address
Rest of property information

Lawyer Record - one record for each Lawyer
-------------
Lawyer Number
Lawyer Name
Lawyer Address
Rest of Lawyer information

Contract Record - one record for each Contract or Contract/Property
---------------
Contract Number
Seller Number
Property Number
AutoNumber
Rest of the contract infromation

Contract Signing Record - Many records for signing
of a contract. Contract record to Contract Signing Record
is a one to many
-----------------------
Contract Number
Property Number
AutoNumber
Date Contract Signed
Viewing Date
Viewing Comments

Lawyer Assigned to Contract Record - Many Records for assigning
a lawyer(s) to a contract. Contract record to Lawyer Assigned
Record is one to many.
----------------------------------
Lawyer Number
Contract Number
Property Number
AutoNumber
Date Lawyer Assigned

I hope this helps, you didn't say if a contract can have many lawyers
assigned to a contract
and if there are many signing of a contract. If you have 5 date fields
for signing and a contract
ends-up with 6 signings, the database won't be able to handle it. Also,
the seller many not be
selling their home, they may be selling another property. Or you can
sell several properties under
one contract and have different lawyers assigned to a property. You may
need to change the PK's
of the tables and add more tables.

Many properties to each contract.
Many lawyers to each contract / property combination

My first pass assumed there was one contract for each different
property, so I hope I didn't miss
anything.

Ron
 
Back
Top