Skip to content

Instantly share code, notes, and snippets.

@wallstop
Created October 18, 2017 04:26
Show Gist options
  • Save wallstop/76d3ed1ca6dd899217fe1f4623b09461 to your computer and use it in GitHub Desktop.
Save wallstop/76d3ed1ca6dd899217fe1f4623b09461 to your computer and use it in GitHub Desktop.
DataModels

Data Models

id


column data type nullable? key type notes
public_id char(10) N primary server-side generated, unique hash, alphanumeric

The id table provides a way of generating unique ids for both landlords and tenants. Whenever a new landlord or tenant is created, the server generates a random ten-character string, then attempts to insert it into the db, repeating until it succeeds. This prevents leaking user numbers.

landlord


column data type nullable? key type notes
landlord_id char(10) N primary N/A
contact_name varchar N N/A N/A
contact_number varchar N N/A phone number

Likely need more info.

tenant


column data type nullable? key type notes
tenant_id char(10) N primary N/A
contact_name varchar N N/A N/A
contact_number varchar N N/A phone number

Likely need more info.

tenant_notes


column data type nullable? key type notes
tenant_notes_id bigint N primary db-provided, auto-inc
notes varchar N N/A An info dump on the tenant, provided by landlord

tenant_notes_mapping

column data type nullable? key type notes
landlord_id char(10) N fk(landlord.landlord_id) N/A
tenant_id char(10) N fk(tenant.tenant_id) N/A
tenant_notes_id N fk(tenant_notes.tenant_notes_id) N/A

PrimaryKey(landlord_id, tenant_id, tenant_notes_id)

These allows landlords to create custom notes for each tenant, potentially multiple

payment_due


column data type nullable? key type notes
payment_due_id bigint N primary db-provided, auto-inc
lease_id bigint N fk(lease.lease_id) N/A
amount varchar N N/A big decimal represented as text, USD
type varchar N N/A [Rent,LateFee,Manual]
due_date datetime Y N/A UTC
description varchar N N/A human-friendly description of what these charges are for (September rent, September late fee)
status varchar N N/A [Active,Cancelled,Fulfilled,Overdue]
landlord_notes varchar Y N/A Landlord-provided details, if any

payment_due is the "bill" that the tenant's see. Server validates that any payments made don't overpay the amount due. Underpayment is perfectly fine. Whenever a payment is created or updated, or whenever a payment_due is updated, the status is re-calculated based on existing payments, in combination with current status (cancelled or fulfilled payment_dues cannot be changed)

payment_method


column data type nullable? key type notes
payment_method_id bigint N primary db-provided, auto-inc
name varchar N unique friendly name of the payment method (venmo, paypal, ad-hoc)

This table is primarily for data cleanliness - we want every payment to have a method attached to it. Hardcoded strings can change in server code pretty easily. Making a table for payment methods and having payment.method point into it means that any changes to payment metohds will be a very conscious thing, and won't be done by accident.

payment


column data type nullable? key type notes
payment_id bigint N primary db-provided,auto-inc
tenant_id char(10) N fk(tenant.tenant_id) who made the payment
payment_due_id bigint N fk(payment_due.payment_due_id) what the payment was for
payment_date datetime N N/A Time payment submitted
amount varchar N N/A bigdecimal represented as text, USD
method varchar N fk(payment_method.name) how the payment was made
status varchar N N/A [Pending,Processing,Declined,Approved,Canceled]
tenant_notes varchar Y N/A Whatever kind of cute messages the tenant wants to attach, visible to anyone with access to the payment_due

lease


column data type nullable? key type notes
lease_id bigint N primary db-provided, auto-inc
landlord_id char(10) N fk(landlord.landlord_id) N/A
start_date datetime N N/A Start of the lease, should be a localdate
end_date datetime Y N/A End of the lease, should be a localdate
valid bool N N/A If the lease is currently valid (not canceled)
late_fee_policy varchar Y N/A Big jumble of text, provided by the landlord.
day_of_month_due byte Y N/A What day of the month payment is expected

lease_breakdown


column data type nullable? key type notes
lease_breakdown_id bigint N primary db-provided, auto-inc
lease_id bigint N fk(lease.lease_id) what lease this is for
amount varchar N N/A bigdecimal represented as text, USD
description varchar Y N/A what this charge is for

The lease breakdown table explains how the total amount the tenant owes every month is due. For one-off payments, like application fee, payment_dues can be manually created. This is primarily for rolling up a monthly bill for the tenants.

lease_mappings


column data type nullable? key type notes
tenant_id char(10) N fk(tenant.tenant_id) the tenant...
lease_id bigint N fk(lease.lease_id) and the lease

primary key(tenant_id, lease_id)

Maps tenants to their leases. This allows for roomates, or tenants that have multiple leases, for whatever reason.

TODO: Credit, overpayment TODO: History tables. We want to almost every table above to have an audit trail of changes

payment_history


  • payment_history_id (db-provided,bigint,auto-inc)
  • payment_id (non-null)
  • change_date (datetime) (non-null)
  • change_author (varchar) (non-null)
  • previous_status (varchar) (non-null)
  • new_status (varchar) (non-null)
  • previuos_amount (big decimal?) (non-null)
  • current_amount (big decimal?) (non-null)
  • previous_lease_id (FK) (non-null)
  • current_lease_id (FK) (non-null)
  • previous_notes_
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment