Just need critique on my database table structure

Databases
1

Hi there

I’m developing a small property management application,
to record rent payments made on leases (associated to a property)

Im just a bit uncertain on my table design as to whether it’s the right way to do things and was hoping to get some feedback.

This is what I have at the moment…

Transaction
id
lease_id (fk)
lease_party_id (fk) <----- Should this be just Person_id??
date
description
amount

Lease
id
property_id (fk)
lease_party_id (fk) <---- Should this be just Person_id? or is this good…
start
end
rent_amount

Lease_Party (stores Lease - Tenant/Landlord association)
id
lease_id (fk)
person_id (fk) ~ tenant or landlord
type_id (fk) ~ reference a type table to state whether tenant or landlord

Person (stores Tenant/Landlord details)
id
first_name
last_name
phone
email

Any advice / guidance appreciated!

2

how many people can take out a lease on a property?

also, i think you’re missing a table for the properties

finally, keep in mind that not every table should have an auto_increment column

3

re: autoincrements yeah I can understand, the reasoning for that is largely to simplify the process of deleting/updating records from the PHP Application

The property table exists I just left it out to not clutter the post as I’m really just seeking approval of the above setup/connections.

Typically there are 2-3 people on a lease. Usually a couple, also regularly 3 friends. Hence why I made lease_party.

4

this is actually a myth :slight_smile:

well, something’s not quite right

you have a foreign key from lease to lease_party, and a foreign key from lease_party to lease

that’s not going to work :slight_smile:

5

Oh no no sorry my mistake! lol

As i was writing out the original post i actually changed the table structure lol…

Here is what it should have been.

Lease
id
property_id (fk)
start
end
rent_amount

Lease_Party (stores Lease - Tenant/Landlord association)
id
lease_id (fk)
person_id (fk) ~ tenant or landlord
type_id (fk) ~ reference a type table to state whether tenant or landlord