Help with relationship design - three tables Landlord, Property, Tenant
Help with relationship design - three tables Landlord, Property, Tenant
(OP)
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
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
RE: Help with relationship design - three tables Landlord, Property, 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.
RE: Help with relationship design - three tables Landlord, Property, Tenant
CODE -->
RE: Help with relationship design - three tables Landlord, Property, Tenant
Kind regards Mark
RE: Help with relationship design - three tables Landlord, Property, Tenant
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
RE: Help with relationship design - three tables Landlord, Property, Tenant
The original structure I posted with a single junction would support that.
CODE
So assume you have this data in the table
CODE
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.
RE: Help with relationship design - three tables Landlord, Property, Tenant
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