database design help

[CyberShot]

I want to create a database for my person use. I am the treasurer for an HOA. I collect money for 14 units monthly. I currently use a spreadsheet but I figured it would be a great learning experience to try creating a database. ( very little skill ). I want to be able to select a unit and see how much is owed, when the last payment was made and any other notes needed. I can't decide how I should make it. Should I just make a plain database with simple records like a spreadsheet or make it more complicated? by simple, I was thinking one table.

ID, name, address ( a four digit number ) , paid ( bool ), amount_paid, date, notes

I have toyed with the idea of making it more tables but I don't know if it is needed.

The idea is that I want the ability to select a unit number ( address ) and see what is owed up to the current date. If I have a unit that hasn't paid in a year, I want to be able to see that and the months owed. if they pay half of the amount, I want to be able to do the math on that and see that.

I am trying to figure out if a one table database will work just fine.

[requinix]

You're off to a surprisingly good start, actually. Most people who are like "I'll just make a database for it" just kinda go for it without really knowing what they're doing.

That said, you're a treasurer. You have a rather important job of keeping very good track of money. You need detailed records of everything that happens, not just a summary of it all. So here's the database design I'm thinking of:

units      tenancies      charges       payments
-----      ---------      -------       --------
ID         ID             ID            ID
number     unit ID        tenant ID     charge ID
notes      tenant         date          date
           start date     amount        amount
           end date?      notes         notes

There are four concepts involved here:

1. The units. You could go with just numbers but that doesn't allow you do do anything else with the concept.
2. Tenants. It's one thing to track payments against units, but units don't make payments. People do. And people move in and out of units all the time. You should be able to differentiate between payment #100 for unit #1234 made by John Smith and payment #101 for unit #1234 by the new occupant Jane Doe.
3. Charges. Because how can you record a payment of $100 when you don't even know what it's for?
4. Payments. You may only want this system to tell you who is up to date with payments, but what you need is to be able to know every little detail about how money is changing hands.

I imagine you would use the system something like this:

• A new tenant is moving into the area. You open the application and go to the page where you manage tenants. You open the details for the unit they will be moving into and see that your system correctly thinks it's unoccupied (having handled the previous tenant's previous moving out earlier). You enter their name and start date and save.
• A new month starts and new HOA dues are required from each unit. You open up the application and go to the page where you enter new charges. You tell it that you want to enter a new charge for all currently-occupied units and how much the dues are. The system tells you that it created new charges for some number of units.
• You receive a check for from some unit. You open up the application and go to the page where you enter new payments being made, and it asks you for the unit. On the next page, it presents you with the current state of that unit: who lives there, what charges they've owed, and what payments have been made. The page tells you that they have not yet paid (fully) for a particular charge. You look at the check, confirm the name and address on it, confirm that the charge they wrote as a note is indeed what they still need to pay, and confirm that they aren't overpaying. You select that charge, enter the amount paid, and save. The system tells you that they've fully paid for that charge (or not), and perhaps also lists any other charges still unaccounted for.
• The HOA comes to you and asks for a report of everybody who hasn't paid their dues yet. You go to a reports page, then to the page that can tell you about outstanding charges. You select the charge corresponding to the month's dues and the system tells you each occupant who hasn't paid (fully) yet.
• An IRS agent comes knocking to conduct an audit. You go to a reports page that tells you all activity over a date period. You select the past year and the system tells you about every charge and every payment, and even summarizes it. The IRS agent compares your printout to their notes, frowns, and tells you that everything is in order. You then wake up from your dream and wish audits were that simple.

 

[Barand]

I would go for a slightly different model where the monthly charge is set for the duration of the lease. Charges can then be generated rather than having to enter them yourself.

+--------------+ 
|  unit        |   
+--------------+   
| unit_id      |----+
| number       |    |                             +--------------+
| notes        |    |   +--------------+          |   payment    |
+--------------+    |   |   tenancy    |          +--------------+
                    |   +--------------+          |  ID          |
                    |   | tenancy_ID   |---------<|  tenancy_ID  |
                    +--<| unit ID      |          |  date        |
                   +---<| tenant_id    |          |  amount      |
                   |    | charge_p_m   |          |  pay_type    |  (rental, deposit, contra, refund)
                   |    | start date   |          |  notes       | 
                   |    | end date     |          +--------------+
                   |    +--------------+     
                   |                         
                   |                         
+--------------+   |                         
|  tenant      |   |                         
+--------------+   |                         
| tenant_id    |---+                         
| name         |                                    
| prev_address |                                    
| notes        |                                    
+--------------+