A property management database schema has to hold four different 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 in 13 tables with 13 foreign key relationships, 2 link tables and 5 context views, on PostgreSQL with snake_case naming. Every table below is real: the columns, types, keys, unique constraints and the 10 check constraints are read straight out of the diagram, which you can open and edit at the end.

What tables does a property management database need?

properties

Real estate properties managed by the system.

units

Individual rental units within a property.

amenities

Available amenities for properties.

properties_amenities

Junction table mapping properties to amenities.

tenants

Registered tenants.

tenant_documents

Documents submitted by tenants.

leases

Lease agreements for rental units.

lease_tenants

Links a lease to the tenants named on it. Singular lease in the name is deliberate: this table crosses a context boundary, so it is not shaped like the plain many-to-many table above.

Unique on (lease_id, tenant_id) as lease_tenants_lease_id_tenant_id_key.

invoices

Invoices generated for rental payments.

payments

Transactions completed against invoices.

maintenance_requests

Issues reported by tenants for units.

work_orders

Work orders created to fulfill maintenance requests.

vendors

Contractors performing maintenance work.

How the tables relate

Every relationship in this schema is one-to-many with a cardinality of zero or many on the child side, which is to say a property may have no units yet, an invoice may have no payments against it, and a maintenance request may not have produced a work order. None of the 13 foreign key columns is nullable, so a child row cannot exist without its parent.

The two many-to-many pairs in the schema are properties to amenities and leases to tenants. Neither is a direct relationship: each is resolved through a link table that appears in the list above as two separate one-to-many rows. The two link tables are not built the same way, and the difference is the subject of the design decisions below.

The five context views

A context view is a focused subset of the main diagram, showing one part of the model while the main view remains the single source of truth. This schema is divided into five of them, and the same five groups are drawn as coloured boxes on the diagram itself. Seven of the 13 relationships stay inside a single view and four cross from one view into another. The remaining two belong to lease_tenants, which is assigned to no view at all.

properties

Contains properties, units, amenities and properties_amenities. All three of its relationships stay inside the view: a property has many units, and a property is linked to many amenities through the junction table. Nothing in this view points outward. It is the only view of the five that is closed in that sense, which makes sense for a portfolio: buildings exist before anyone rents them.

tenants

Contains tenants and tenant_documents, joined by a single relationship. Everything else that concerns a tenant lives in another view and reaches back into this one, so tenants is the most referenced table in the schema: lease_tenants and maintenance_requests both hold a tenant_id.

leases

Contains leases and nothing else. Not one of its relationships stays inside the view: leases.unit_id reaches into the properties view, invoices.lease_id reaches in from the payments view, and the link to tenants goes through a table that sits in no view. A lease is the most connected row in this model and the least self-contained, which is what a single-table view says out loud.

The table in no view

lease_tenants is not assigned to any of the five views, and that is the point of it. It holds a lease_id and a tenant_id, and its two parents live in different contexts, so filing it inside either the leases view or the tenants view would give one context ownership of a fact that belongs to both. Its name records the same thing: the first word is singular where a plain many-to-many table would be plural. On the diagram above it is drawn inside the orange leases box, which is a grouping on the canvas and not the same thing as view membership - the leases context view itself lists one entity.

payments

Contains invoices and payments, with one relationship between them and one crossing out through invoices.lease_id into the leases view. Money attaches to the agreement, not to the unit and not to the tenant, so an invoice is always reachable back to exactly one lease.

maintenance

Contains maintenance_requests, work_orders and vendors. Two relationships stay inside: a request produces work orders, and a vendor is assigned to work orders. Two cross out, both from maintenance_requests, one into the properties view via unit_id and one into the tenants view via tenant_id. This is the only view that reaches into two other views from a single table, which is what you would expect of a repair: it happens to a unit and it is reported by a person.

Counting foreign keys as they cross between groups like this is the same reading the Context Map applies to a whole schema, one level up from individual tables.

Design decisions

Why do the two link tables have different keys?

Both connect two parents and neither carries a payload, but they are built differently on purpose, and the names say which is which.

properties_amenities is a plain many-to-many table. Its two columns are its key, a property is linked to an amenity once or not at all, and there is nothing more to say about the pairing.

lease_tenants records who is named on a lease, which is a fact belonging to two contexts at once. It takes a surrogate id so the pairing can be referenced as a row in its own right, and the pair is protected by lease_tenants_lease_id_tenant_id_key instead of by the primary key. That uniqueness is total here because both columns are NOT NULL: a nullable column inside a unique constraint would exempt those rows from it, and neither of these is nullable.

Why does a lease reach tenants through a link table?

A tenant_id column on leases would permit exactly one tenant per lease. Co-tenants are ordinary in residential renting, and a tenant signing a second lease later is equally ordinary, so the relationship is many-to-many in both directions and lease_tenants is what expresses it. The cost is that “who lives here” is a join rather than a column.

Why is scheduled_date the only nullable column?

Across roughly a hundred columns in 13 tables, work_orders.scheduled_date is the single one that permits NULL. That is a deliberate statement about the life of a work order: it is created and assigned to a vendor before anyone agrees on a date, so the absence of a date is a real state rather than missing data. Everywhere else the schema takes the opposite position, and the two TEXT columns that could plausibly be empty - amenities.description and tenants.emergency_contact - default to an empty string instead of NULL. Being deliberate about which columns admit NULL is what keeps a nullable column from quietly weakening the constraints around it.

Why are the status columns check constraints rather than enums or lookup tables?

There are 10 check constraints in this schema and every one of them fixes an allowed value set: four property types, four unit statuses, five document types, five lease statuses, five invoice statuses, five payment methods, four payment statuses, four maintenance priorities, five maintenance statuses and five work order statuses. Written as check constraints rather than as a Postgres enum type or a lookup table, the permitted values stay visible in the table definition and in the ERD, and Schemity renders them on the entity instead of hiding them behind a type name. The trade is that adding a sixth lease status needs a migration, and that these values cannot carry extra attributes the way a lookup table row can.

Why does leases have no property_id?

A lease points at a unit; the unit points at the property. The property is one hop away, so storing it on the lease as well would create a second path to the same fact, and two paths can disagree after an update. The same reasoning keeps payments attached to invoices rather than directly to leases.

Why do payments hang off invoices instead of leases?

Because one invoice can be settled by several transactions. invoices.status includes partially_paid, which only means something if more than one payment row can reference the same invoice, and the one-to-many from invoices to payments is what allows it. A payment table keyed to the lease could not express which charge a partial payment was against.

Why is total_units stored on properties?

This one is a deliberate denormalization rather than a constraint, and it is worth naming as such: properties.total_units holds a count that could be derived by counting units rows for that property. It is cheap to read and it will drift the first time a unit is inserted without the counter being updated. If you adopt this schema, either maintain it in a trigger or drop the column and count.

What changes in production

The schema above is complete as a model, and there are five things it does not yet carry that a deployed property management system will need.

No indexes are defined. PostgreSQL creates an index for a primary key and for a unique constraint, but it does not index foreign key columns automatically. Every one of the 13 foreign key columns here - units.property_id, leases.unit_id, invoices.lease_id, payments.invoice_id and the rest - will be scanned rather than looked up until you add one, and the joins in this schema are almost entirely parent-to-child.

No deletion behaviour is recorded. The diagram carries no ON DELETE rule on any relationship, so every foreign key falls back to NO ACTION. In practice that means a tenant with documents or lease rows cannot be deleted at all. Decide per relationship whether that is the intent, whether children should cascade, or whether the answer is a soft delete - there is no deleted_at column anywhere in this schema.

Money is NUMERIC with no precision or scale. rent_amount, monthly_rent, security_deposit, total_amount, amount, estimated_cost and actual_cost are all unconstrained NUMERIC, which in PostgreSQL stores whatever scale it is handed. Pin them to NUMERIC(12,2) before real currency reaches them.

There is no tenancy column. This schema models one operator’s portfolio. If a single deployment serves several management companies, an owning organisation column has to reach nearly every table, and that is a decision to make before data exists rather than after.

There is no audit trail of who did what. Every table carries created_at and updated_at as TIMESTAMPTZ defaulting to NOW(), which answers when a row changed, but no column answers by whom. Lease status and payment status are exactly the fields a dispute will ask that question about.

Open this schema in Schemity Lite

The diagram is public and openable: open the property management schema in Schemity Lite. Nothing is read-only about it - drag the entities into an arrangement that suits you, add the columns your version needs, and export the result as SQL, Mermaid, DBML or an image. If you would rather work against your own database rather than a browser copy, the desktop app reverse engineers a live PostgreSQL database into the same kind of diagram.