Leaderboard

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.