Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Property and Tenant tables - best approach for relationship 4

Status
Not open for further replies.
Moss100

Moss100

Technical User
Aug 10, 2004
586
GB
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
 
Sort by date Sort by votes
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.
 
Upvote 0 Downvote
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,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
 
Upvote 0 Downvote
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.
Click to expand...
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.
 
Upvote 0 Downvote
Personally I would have 3 entities (tables)...

renting_ggdpjo.png


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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

MrStohler
  • Locked
  • Question
Beginner Q: How to separate data from Excel into related tables and maintain relationship? 1
Replies
5
Views
706
SkipVought
S
JayRo71
  • Locked
  • Question
Importing only unique records from excel tables 1
Replies
10
Views
939
SkipVought
S
puforee
  • Locked
  • Question
Best way to setup tables for multible related data sets
Replies
1
Views
531
dhookom
dhookom
oxicottin
  • Locked
  • Question
Creating tables and relationships for new database
Replies
2
Views
4K
MajP
MajP
Lidias13
  • Locked
  • Question
Odata contains fields from multiple tables in single table with different field names - for same
Replies
1
Views
537
dhookom
dhookom

Part and Inventory Search

Sponsor

Back
Top