A property management database schema has to hold four things at once: a portfolio of buildings, the people renting them, the agreements between the two, and the money and repairs that follow. This one models all four on PostgreSQL in 13 tables with 13 foreign keys, 2 link tables and 5 context views. Below: the decision the design turns on, what each table is for, one unit followed from lease to repair in four queries, and six problems to fix before you use it. The live diagram is above the article; open it to see every column.

A lease rents a unit to people

Three choices about the lease shape everything else. It points at a unit, not a property: the property is one hop away through units.property_id, and storing it on the lease too would give two paths to one fact that can disagree. It reaches tenants through a link table, lease_tenants, because co-tenants are ordinary and one tenant signs several leases over the years, so a tenant_id column on leases would be wrong in both directions. And money hangs off the lease: invoices.lease_id bills the agreement, and payments.invoice_id records what settled each bill.

The cost is that “who lives in 4B” is a three-table join rather than a column. The walkthrough below shows that join is short.

What tables does a property management database need?

Thirteen, in five groups. The columns worth knowing are listed here; the full reference is at the end.

All 13 relationships are one-to-many and none of the foreign key columns is nullable. The two many-to-many pairs, properties to amenities and leases to tenants, are resolved through link tables, and they are deliberately built differently (see below).

The five context views are properties (the portfolio and its amenities), tenants (people and their documents), leases (the leases table alone), payments (invoices and payments) and maintenance (requests, work orders and vendors). lease_tenants belongs to none of them on purpose: its two parents live in different contexts, and filing it under either would give one context ownership of a fact that belongs to both. Counting the foreign keys that cross between views is the reading the Context Map applies to a whole schema.

Following one unit from lease to repair

Four questions a property manager asks every week, answered by the schema as it is. Each query below was run on PostgreSQL 18 against the diagram’s own SQL, with one property, two units, two tenants and one vendor.

Maya and Jon sign a lease on unit 4B: one row in leases and two in lease_tenants. Who lives in 4B?

SELECT t.first_name, t.last_name, l.end_date
FROM units u
JOIN leases l ON l.unit_id = u.id AND l.status = 'active'
JOIN lease_tenants lt ON lt.lease_id = l.id
JOIN tenants t ON t.id = lt.tenant_id
WHERE u.property_id = 1 AND u.unit_number = '4B';

The month’s invoice is 2,100, and Maya pays her half by bank transfer. What is still owed? Work it out from the payments rather than trusting invoices.status:

SELECT i.id, i.due_date, i.total_amount,
       i.total_amount - COALESCE(SUM(p.amount) FILTER (WHERE p.status = 'completed'), 0) AS balance
FROM invoices i
LEFT JOIN payments p ON p.invoice_id = i.id
GROUP BY i.id
HAVING i.total_amount - COALESCE(SUM(p.amount) FILTER (WHERE p.status = 'completed'), 0) > 0;

It returns 1,050 owed. Which units can be let? Again, ask the leases, not units.status:

SELECT u.unit_number, u.rent_amount
FROM units u
WHERE u.property_id = 1
  AND NOT EXISTS (
    SELECT 1 FROM leases l
    WHERE l.unit_id = u.id AND l.status = 'active'
  );

Jon reports a leaking tap: a row in maintenance_requests naming him and the unit, then a work order assigned to a plumber before anyone agrees a date. Which jobs are still waiting to be scheduled?

SELECT u.unit_number, mr.title, mr.priority, v.company_name
FROM work_orders wo
JOIN maintenance_requests mr ON mr.id = wo.maintenance_request_id
JOIN units u ON u.id = mr.unit_id
JOIN vendors v ON v.id = wo.vendor_id
WHERE wo.scheduled_date IS NULL
  AND wo.status NOT IN ('completed', 'cancelled');

That IS NULL is why scheduled_date is the one nullable column: a job assigned but not yet booked is a real state, not missing data.

Why is it built this way?

The two link tables have different keys, and their names say so.

A property has an amenity or it does not, so the pair is the key. A tenant on a lease is a fact two contexts share, so it gets an id to be referenced as a row, and the pair is protected by lease_tenants_lease_id_tenant_id_key. That protection is total because both columns are NOT NULL: a nullable column would exempt rows from it.

Payments hang off invoices, not leases. invoices.status includes partially_paid, which only means something if several payments can settle one bill. Keyed to the lease, a partial payment could not say which charge it was against.

Empty text is '', not NULL. amenities.description and tenants.emergency_contact default to an empty string, so the schema’s only NULL carries meaning.

Statuses are check constraints. Ten check constraints fix the value lists, from four property types to five work order statuses. As check constraints rather than an enum type or a lookup table, the values sit in the table definition, where Schemity shows them on the entity. The price is a migration for every new status.

Six problems to fix before you use it

The model is complete; these are what a deployed system needs on top.

1. One unit can have two active leases. In the test, a second active lease on 4B for overlapping dates was accepted. A unique constraint cannot express “no overlapping dates”, but an exclusion constraint can:

CREATE EXTENSION btree_gist;
ALTER TABLE leases ADD CONSTRAINT leases_no_overlap
  EXCLUDE USING gist (unit_id WITH =, daterange(start_date, end_date, '[]') WITH &&)
  WHERE (status = 'active');

With it, the second lease fails with conflicting key value violates exclusion constraint "leases_no_overlap".

2. One property can have two units called 4B. unit_number has no uniqueness at all, and a second 4B was accepted. Unique per property, not globally:

ALTER TABLE units ADD CONSTRAINT units_property_id_unit_number_key
  UNIQUE (property_id, unit_number);

3. Three stored values will drift. After a third unit was added, properties.total_units still said 2. Unit 4B read vacant while it was leased, and the half-paid invoice still read pending. Each can be derived, as the queries above do; either derive them or keep them current with triggers.

4. Deleting a tenant quietly edits a lease. Most foreign keys here fall back to NO ACTION: a tenant with a maintenance request cannot be deleted. But lease_tenants cascades, so a tenant with no requests or documents deletes cleanly, and in the test the lease silently went from two tenants to one while its invoices stayed. Decide per relationship what deleting means; there is no deleted_at anywhere for a soft delete.

5. No foreign key is indexed. PostgreSQL indexes primary keys and unique constraints, not foreign key columns, and the diagram defines no other index. Every join above scans until you add them, starting with the hottest:

CREATE INDEX ON units (property_id);
CREATE INDEX ON leases (unit_id);
CREATE INDEX ON lease_tenants (tenant_id);
CREATE INDEX ON invoices (lease_id);
CREATE INDEX ON payments (invoice_id);

The lease_tenants index covers lookups by tenant; lookups by lease already use the unique constraint, which leads with lease_id.

6. Money has no scale. rent_amount, monthly_rent, security_deposit, total_amount, amount, estimated_cost and actual_cost are plain NUMERIC, which stores whatever scale it is handed. Pin them to NUMERIC(12,2) before real currency reaches them.

Open this schema in Schemity Lite

The diagram above the article is the same schema, live: open it to drag tables, read every column and export SQL, Mermaid or an image. To edit your own copy, open the property management schema in Schemity Lite. To start from a real database instead, the desktop app reverse engineers a live PostgreSQL schema into the same kind of diagram.

Full column reference