Property and Tenant tables - best approach for relationship 4

[Moss100]

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

 

[MajP]

Depends on your business rules. I would think you would want to have historical records. Also do tenants commonly move into another one of your properties? If so I think I would actually do a many to many to keep historical information and allow you to reassign a tenant.

tableProperties
  property_ID
  other property fields

tableTenants
  tenant_ID
  other Tenant fields

table_Properties_Tenants
  Property_ID_FK
  Tenant_ID_FK
  Tenancy_Start_Date
  Tenancy_End_Date

Assuming that no one can occupy more than one property at once, you can use a little vba to check that you cannot assign a person to property if they are already in an "active" lease. In other words when you enter a record in table "table_Properties_Tenants" if there is a record for that person with a start date less than today and an end date greater than today, they would have an active lease.

 

[SkipVought]

Well might it not also be possible for a well to do tenant to lease multiple properties in the same period of time? You might also have overlapping periods where a tenant goes from one property to another.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[MajP]

Well might it not also be possible for a well to do tenant to lease multiple properties in the same period of time? You might also have overlapping periods where a tenant goes from one property to another.

The many to many table structure would then support these cases as well. You may still want to alert the user that an "active" lease exist to avoid inadvertent mistakes, but still allow them to create these conditions.

 

[1DMF]

Personally I would have 3 entities (tables)...

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

 

[Moss100]

Thank you very much for your help.

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

 

[dhookom]

Both 1DMF and MajP made the same suggestion. Other fields need to be added but the structure is sound.

Duane
Hook'D on Access
MS Access MVP