Specifications.sql

/*
I own a shopping center in a busy part of town and want to organize my records.
I'd like to keep track of the lease terms in summary form so that I don’t have to keep referring back the 50+ page lease whenever I need to recall details.
There are 20 units. 1-10 on the ground floor are storefronts and 11-20 on the 2nd floor are offices.
I want old tenants, whose lease has expired/moved out to have an inactive status, but don’t delete, as I may need to reference historic records. 
However, I don't need/want to keep track of tenants that left before I took over the property back in 2012.

I want the following data summarized:
Business/Tenant name 
Name of contact person/business owner
Email address/Phone number of contact
Unit number
Square footage of unit (rentable space)
Lease effective date
Rent effective date, if different than lease
Lease end date
Yearly increase rate- can be a percentage or set dollar amount, can also be n/a
Month to apply increase- usually the month after the “lease end” (eg, if lease ends may 31, month of increase is June), but can be different
Current Basic rent amount
Additional Rent/CAM (common area maintenance)
Total monthly rent (basic + additional rent. Please make this calculate automatically)
Date late fee applies
Late fee rate
Options to renew (if any)
Security Deposit amount
Additional notes


Please see attached for Sample data

Reports: for starters, I would like to be able to see a list of current tenants. 
Also, those whose leases are due to expire within the next 6 months -I just need the name, contact info and lease end date- sorted by end date (closer end dates on top)
I'd also like a list of those tenants whose rent needs to be increased next month
It would also be great to be able to analyze the average current rent for offices vs. Store fronts

Q: What is the earliest date a lease could have begun? 
A: I don't know, I bought the property back in 2012, but some of the units were already occupied. 

Q: Can a tenant leave before the lease ends?
A: Ideally not, but it does happen when a tenant is extremely unhappy- or doesn't pay the rent and gets evicted, then I try to lease the unit to a new tenant as oon as possible

Q: Can a tenant renew or extend their lease?
A: But of course! I always hope that they do. If they reamin in the same unit, even terms of the lease change, I don't need to keep a separate record of the old terms, 
just the old least start date, so that I know when they originally moved in. However, if they want to move into a different vacant unit, then I would want it to be a completely new record.

Q: Does a lease ever cover two or more units?
A: Interesting question. I have never had that so far. I suppose that if a tenant would want 2 adjacent units, it can be done.

Q: following up on the previous question, if a tenant would want 2 units, would they sign 2 seperate leases?
A: No, it would be combined in one lease.

Q: Do you ever have 2 tenants occpuying a single unit?
A: No. There are some very large units and I sometimes allow my tenants to sublet their space if it's too big for their own needs, (provided that it's specified in the lease)
but I only rent out each unit to one person/business and I don't keep track of which tenants sublet
*/