Help with relationship design - three tables Landlord, Property, Tenant

[Moss100]

Hello I am designing a database to keep track of Landlords Properties and Tenants.

I am struggling to think how best to set up the relationships between the tables.

The basics are that ONE Landlord may have MANY properties and ONE Property may have many Tenants over time - but only one active Tenant at any time.

I have set the following ID's in each table.

Landlords - LAN_ID
Properties - PROP_ID
Tenants - TEN_ID

At first I thought the method below was the way to go:

LAN_ID > (one to many) > PROP_ID > (one to many) > TEN_ID

The problem with this approach is that I want to ensure that there is no risk of old tenants showing in forms and potentially the user applying data to the wrong tenant.


I then considered having a field in the Properties table called Ten_ID_Link, into which I could input the Ten_ID. I can make this field indexed so that a ten_ID would only appear once in the table and thus ensure that only one tenant is ever bound to a property. ie.

LAN_ID > (one to many) > PROP_ID
PROP_TENANT_ID_LINK > (one to one) > TEN_ID


I would be very grateful for any thoughts or advise as this is a crucial area of my database design to get right.

Regards and many thanks - Mark

 

[MajP]

If Landlords and Tenants have the same type of information, you can actually use on table for both. You would just need a single field for identifying what type of Person either "landlord" or "tenant"
something like:

TblPersons
PersonID (PK)
PersonType (Landlord or Tenant)
FirstName
LastName
.....
other fields unique to a person


But for now lets assume Landlords and Tenants have certain fields that are unique to them and use two tables. Then you would need

TblLandlords
LandlordID (PK)
LL_FirstName
LL_LastName
....
fields that are unique to landlords

TblTenants
TenantID (PK)
T_FirstName
T_LastName
....
fields that are unique to landlords

TblProperties
PropertyID (PK)

Now you need a junction table to allow a 1 to many

JTblLandlord_Properties_Tenants
LandLordID_FK
PropertyID_FK
TenantID_FK
OccupancyStartDate (date moved in)
OccupancyEndDate (date moved out)

So assume you have this data in the table

1 12 A
1 22 B
2 44
3 55 G

That used in a query to show all the details, but it shows that Landlord 1 owns two properties,12 and 22. In property 12 is tenant A and in 22 is B. Landlord 2 own property 44 and has no tenant.

Now if you maintain historical information you would also need OccupancyStartDate and OccupancyEndDate in the junction table

There are ways at the form level to ensure your buisness rules. For example if a Tenant can only be in one property at a point in tiem, then in your pull down you would only make a choices available of records in the Tenant table "not in" the junction table where todays date is greater than occupancyEndDate. That is done in Sql. In other words my pull down will show all tenants in the tenant table not currently in a property.

Now working with junction tables in forms takes a little practice, but once you get it the process is the same.

 

[MajP]

Sorry about the formatting. But that is supposed to be table name and under it the possible fields.

TblPersons
   PersonID (PK)
   PersonType (Landlord or Tenant)
   FirstName
   LastName
    .....
   other fields unique to a person

 

[Moss100]

Thank you very much for taking the time to help me out - very much appreciated.

Kind regards Mark

 

[mjcmkrsr]

Hi,

I guess the relations are a little more complicated. Since properties may be sold/acquired, and tenants may rent one property and after that the next one you'll have over the time

Landlord (a many to many relation) Properties (a many to many relation) Tenant(s)

Hence you'll need at least five tables
- Landlord table
- relation table Landlord-Property
- Properties table
- relation table Properties-Tenants
- Tenants table

hth

MK

 

[MajP]

Since properties may be sold/acquired, and tenants may rent one property and after that the next one you'll have over the time

The original structure I posted with a single junction would support that.

JTblLandlord_Properties_Tenants

  LandLordID_FK
  PropertyID_FK
  TenantID_FK
  OccupancyStartDate (date moved in)
  OccupancyEndDate (date moved out)

So assume you have this data in the table

1 12 A 1/1/2014 6/1/2014]
1 22 B
2 12 A 7/1/2014 7/1/2015 
3 55 G
4 66 A 8/1/2015 8/1/2016

So as can be seen. Landlord 1 rented property 12 to Tenant 1 from 1/1 to 6/1
Then Landlord 2 owned property 12 and rented A on 7/1/2014 - 7/1/2015
Then Tenant A rented from Landlord 4 from 8/1/2015

Again you would have to enforce some of the business rules at the form level
But the structure allow multiple Landlords for the same property over time (implicit), and tenants renting multiple properties over time from same or different tenants.

However, there is a good point here. If there is a lot of unique characteristics of the relationship of the Landlord and the Property then you may consider another table as proposed.
Example if you need to track a property bought and sold dates.

 

[willir]

I don't know if you put this problem to bed or not, but when I look at this problem, I think about transactions.
Something akin to an invoice header and invoice detail with a twist.
Think about the invoice date, due date, address on the header and the transactions listed in the detail section
(Been a while so I maybe rusty)

In terms of "reports", I suspect you want:
- who is currently renting which property
- who is currently the landlord of the property
- who were the previous tenants, from period to period
- who were the previous landloards, from period to period
- what properties do I have
- what properties did I have
- Purchase price, Sold price, Rent acquired, Damages acquired, Utilities
- How much money this propery make for me / cost me

Assumptions:
- A landlord can be the landlord of multiple properties
- A tenant will usually be the tenant of one property, but can theoretically be the tenant of multiple properties
- A tenant may sublet, but the tenant is still responsable for rent and damages
- At any given time, a property can only have one tenant and landlord

tblProperty
PropID - primary key
LandLordID - foreign key
TenantID - foreign key
CurrentStatus - Active, Sold, Renovation
PurchasePrice
PurchaseDate
SoldPrice
SoldDate
CurrentRent
- Lists all properties, past and present
- Could list current tenant and current landlord, but these are caculated fields

tblTenant
TenantID - primary key
LastName
FirstName
CurrentStatus
PreviousAddress
ForwardingAddress
References
Comments
- Lists all tenants, past and present
- No property reference in this table

tblLandLord
LandLordID - primary key
LastName
FirstName
CurrentStatus
Comments
- Lists all landloards, past and present
- No property reference in this table

tblTransactions
TransactionID - primary key
PropID - foreign key
TenantID - foreign key
TransactionType - eg: RentDue, RentPaid, Adjustment, NSF, Renovations, Repairs, DamagesCharged, TaxDue, TaxPaid
TransactionStartDate
TransactionEndDate
TransactionAmount
ReferenceID - Invoice No for rent, Cheque No for rent, Invoice No for renovations,
Comments
- Reference of all transactions
- TransactionType is important - Rent Due reconciled with Rent Paid
- Track renovation costs to a property
- Track charges for damages
- May be a little tricky to generate various views, and such
- If you dont have a transaction table, it may be difficult to calcualte

tblTenantHistory
PropID - Foreign key - Primary key = Prop ID + Tenant ID + Period
TenantID - Foreign key
Period - year + month
Status
MoveInDate
MoveOutDate
- This is a transaction table for Tenants
- Current tenant would be the one without a move out date
- You would sort on move in date, newest to oldest
- I did not track rent here - see the lease agreement. Since a tenant may rent for years and years, they could have numerous leases

tblLandLordHistory
PropID - Foreign key - Primary key = Prop ID + Land Lord ID + Period
LandLordID - Foreign key
Period - year + month
StartDate
EndDate
- Simialr to Tenant Hsitry table

tblLeaseAgreement
LeaseID - primary key
TenanatID - foreign key
Status - Active, InActive, InArrears, InCourt
MonthlyRent
CurrentBalance
StartDate
EndDate
StateOfProperty-OnMoveIn
StateOfProperty-OnMoveOut
Comments
- Current balance is a calculated field from transaction table, but very useful in this situation

Back to the Invoice header / detail comment at the beginning.
If I were working on this project, I would use a tabbed form
frmProperty
- tab displays general information about property and maybe summary information total costs and total earnings
- tab displays current tenant along with lease agreement
- tab displays current landloard
- tab displays previous tenants and landlords
- tab displays renovations
- tab displays transactions, ability to filter by tenant, by time period

frmTenant
- tab displays general information - current status, current balance
- tab displays transactions
- tab displays lease agreements

frmLandLord
- simialr to frmTenant except more focus on your business interactions with them

frmTransactions
- Real good ability to apply filters for properties, tenants, transaction types
- Ability to zoom into a property or tenant

Anway, enough rambling
Hope it helped