Property and Tenant tables - best approach for relationship
Property and Tenant tables - best approach for relationship
(OP)
Hello - I am writing a database to record Properties and Tenants.
The property will, probably over several years, have several tenants.
I can either set the relationship as:
1. One (property) to Many (tenants)
or
2. Insert the Tenant ID as a link into the property table.
As i see it using a one to many will have the benefit of seeing which past tenants a property has etc.
However...
Inserting the Tenant ID into the table to form a link will ensure that only one tenant (hopefully the correct one) is linked and available to the user at any time (thus preventing the user from updating the wrong tenant record).
Which appraoch is likley to be the best?
Many thanks Mark
The property will, probably over several years, have several tenants.
I can either set the relationship as:
1. One (property) to Many (tenants)
or
2. Insert the Tenant ID as a link into the property table.
As i see it using a one to many will have the benefit of seeing which past tenants a property has etc.
However...
Inserting the Tenant ID into the table to form a link will ensure that only one tenant (hopefully the correct one) is linked and available to the user at any time (thus preventing the user from updating the wrong tenant record).
Which appraoch is likley to be the best?
Many thanks Mark
RE: Property and Tenant tables - best approach for relationship
CODE -->
RE: Property and Tenant tables - best approach for relationship
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: Property and Tenant tables - best approach for relationship
RE: Property and Tenant tables - best approach for relationship
Property (PropertyID, Postcode, Address ...)
Tenant (TenantID, Name, DOB ...)
Lease (LeaseID, PropertyID, TenantID, StartDate, EndDate)
I've added some generic columns just for example.
This way you have correct normal form, and you can implement check constraints to facilitate rules such as if a tenant can rent more than one property at the same time etc..
Lease would be active if enddate is <= now(), the existence of a lease record (being it's a weak entity) implies a property was rented and the period.
More or less what MajP has ;)
"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."
"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music
RE: Property and Tenant tables - best approach for relationship
A tenant can also have many leases so my thinking was
to have Property > Tenant > Lease
There is always another angle on these things -
You have me rethinking things now ;)
Many thanks Mark
RE: Property and Tenant tables - best approach for relationship
Duane
Hook'D on Access
MS Access MVP