×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Property and Tenant tables - best approach for relationship
4

thread700-1760793

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

RE: Property and Tenant tables - best approach for relationship

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.

CODE --> 

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.

RE: Property and Tenant tables - best approach for relationship

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Property and Tenant tables - best approach for relationship

Quote:

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.

RE: Property and Tenant tables - best approach for relationship

2
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

RE: Property and Tenant tables - best approach for relationship

(OP)
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

RE: Property and Tenant tables - best approach for relationship

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close