HOA member/lot tracking database

[InformaticsNurse]

Good morning,



I am helping my HOA build a simple database for tracking HOA Members and the properties they own in the HOA for billing purposes. We have almost 300 owners and close to 800 lots to keep track of, and our bookkeeper's paper system is very limited in its functionality.

Here's what I have so far:

TMember table
- MemberID (auto number, primary key)
- LastName
- FirstName
- StreetAddress1
- StreetAddres2
- City
- State
- Zipcode
- PrimaryPhone
- SecondaryPhone
- Email
- Active
- CommonsRestriction
- Notes
- Attachments


TLot table
- LotID (short text, primary key)
- LotOwner (number)
- SitusNumber
- SitusStreet
- PPIN
- LastTransactionDate

The MemberID field from the TMember table is related to the LotOwner field in the TLot table as a one-to-many relationship. This allows me to associate multiple lots to one owner. Unfortunately, we have a few instances where we have a lot(s) with multiple, unrelated owners. Each of these owners needs to be charged HOA dues, and thus one lot may need to be associated to more than one member. My database in its current state will not allow a lot to be associated to more than one member. How do I make lots and owners a many-to-many relationship so that I can charge HOA dues based on how many lots each member has in their name?

Thank you!

[ranman256]

The lot table needs and owner field indexed. (LONG) data type
now you can pull many owners.

[NTC]

Lots are forever. Members (owners) come & go - plus can move from one lot to another.

Your Lot table should not have owner field. The lot table is static - and should have no changing data. There should not be a 'Last Transaction' field - that is a calculation, and not a stored value.

The Owner table should have a Lot ID field - with also a Start Date field and an End Date field; so that you can keep historical data correctly. If an owner moves away and returns or sells one lot and buys another - then they would have multiple entries. Your Last Transaction by Lot value can be established via query.

[InformaticsNurse]

ranman256,

So I need to set the LotOwner field as Indexed "Yes (Duplicates okay)"? I have done that and it required me to delete the PK from the LotID field on the TLot tab. I can enter the same lot number under multiple owners, but the other information associated with the lot (SitusNumber, SitusStreet, PPIN) doesn't pull in automatically. Is there a setting for this? This is not a huge deal breaker as I can sort the TLot table in Datasheet View and fill in the gaps where they exist. I was really hoping to have LotID as a PK so that since we have 800 lots we can only have 800 entries in the table but I see now that that cannot be true because if we have 800 lots and one lot is owned by two unrelated owners we would have 801 entries in that table.

Does LotOwner in the TLot field need to be a text field rather than number? I made it a number because I wanted the MemberID (which is an auto number) to be how I related the two. Is there a better way to relate the two tables from a database design perspective? I imagine I could change the "LastName' and "FirstName" fields into a "LastName, FirstName" in the TMember table and then on the TLot table alter the "LotOwner" field into short text, but all of tutorials/classes I have watched prefer to have Last and First names separate. What would be the most elegant solution here?

Thank you for your patience as I ask some very novice questions. I have been an informatics nurse for years and dealt with many medical databases but with that job it's mostly queries, forms/screens, and reports that I build...

[pbaldy]

I'm going to disagree. I think you need a many-to-many junction table. A lot can have multiple owners, but also (presumably) an owner can own multiple lots. I see a third table with fields for MemberID, LotID and perhaps a percentage field (plus it's own autonumber ID field).

[InformaticsNurse]

Lots are forever. Members (owners) come & go - plus can move from one lot to another.

Your Lot table should not have owner field. The lot table is static - and should have no changing data. There should not be a 'Last Transaction' field - that is a calculation, and not a stored value.

The Owner table should have a Lot ID field - with also a Start Date field and an End Date field; so that you can keep historical data correctly. If an owner moves away and returns or sells one lot and buys another - then they would have multiple entries. Your Last Transaction by Lot value can be established via query.

The Last Transaction query would be a date that we receive from the county clerk's office for when the lot last changed hands. In the past the HOA board calculated HOA dues based on the # of lots a person owns and when they purchased them. We have since voted away that system in favor of a system where everyone pays equally, but I think it is a good value to keep on hand. Even better is your idea of having a start and end date for each lot's ownership change (since people trade lots out here like baseball cards...)

I'm going to disagree. I think you need a many-to-many junction table. A lot can have multiple owners, but also (presumably) an owner can own multiple lots. I see a third table with fields for MemberID, LotID and perhaps a percentage field (plus it's own autonumber ID field).

Would the start and end dates make the most sense here? I can confirm that a lot can have multiple owners AND one owner can own multiple lots, the latter of which is the more frequent occurrence.

[pbaldy]

Would the start and end dates make the most sense here? I can confirm that a lot can have multiple owners AND one owner can own multiple lots, the latter of which is the more frequent occurrence.

Well, I assume the HOA fee is due on a lot. If it's $100 for a lot and the lot has 2 50% owners, they owe $50 each. If it had 4 equal owners, they'd owe $25 each. Not saying you couldn't have the dates as fields too, but I would think the percentage of ownership was key.

[InformaticsNurse]

Well, I assume the HOA fee is due on a lot. If it's $100 for a lot and the lot has 2 50% owners, they owe $50 each. If it had 4 equal owners, they'd owe $25 each. Not saying you couldn't have the dates as fields too, but I would think the percentage of ownership was key.

Our HOA does things a bit differently regarding HOA fees... Here's a bit of backstory: Being a lot owner who is current on their HOA fees grants the owner access to a 275 acre lake to fish and roughly the same mount of acres of hunting land. Our property also backs up to a local hunting club's property. Years ago a lot owner sold his lot to a man who deeded the lot to upwards of 10 of his friends who were members the adjacent hunting club. It resulted in a significant amount of traffic added to our property. Our HOA fees are ridiculously low ($15/month per lot) and the board at the time felt as though their $15/month was not adequate to cover the additional traffic and decided that having multiple unrelated owners on one lot was not in the spirit of the community's best interests because each of the 10 men had families that were now using our facilities plus each family's guests.... The board enacted upon the vote of the homeowners a policy that stated that every owner listed on the deed of a property that is not married to another owner was subject to an additional HOA fee. Even if the owners are an unmarried couple living together on the lot with two names on the deed it's counted as one fee. The bylaws and CC&Rs were re-written to prevent abuses like this from happening in the future, especially because there are many HOA lots available for sale.

Essentially the # of lots in the HOA never changes, but our income changes based on how many owners we have.

[pbaldy]

Interesting. I still root for the junction table, but without the percentage field.

[pbaldy]

If I own 2 lots, do I pay 1 fee or 2? You might be able to make do with the owners table if 1.

[InformaticsNurse]

If I own 2 lots, do I pay 1 fee or 2? You might be able to make do with the owners table if 1.

I have tried the junction table method and it seems to work best for my application. I have MemberID and LotID as both primary keys in the TOwnershipJunction table. Would there be a benefit to having a PK of OwnershipID? Does MemberID and LotID have to be PKs for the junction table to work? I'm happy with the way it works now but I'm always open for improvements that I'm not knowledgeable enough to foresee yet

If I had 3 PKs (OwnershipID (autonumber), MemberID, and LotID, would my start date and end date be more meaningful when my neighbors trade lots back and forth?

As far as fees go, we actually pay 1 fee for 1 lot, 1 fee for 2 lots, 2 fees for 3 lots, 3 fees for 4 lots, etc... Some people pay monthly, some pay quarterly or yearly, and some just send us checks every few months with random amounts (and of course some don't pay us at all until we send out nastygrams). As an aside, if you pay your HOA fees for a lot through December but you sell the lot in July the seller does not get a refund, but instead the buyer doesn't start paying until January.

[ssanfu]

PMFJI,

I have MemberID and LotID as both primary keys

There can be only 1 PK field in a table. What you have is a compound primary key.

My personal preference is to have an autonumber type field in virtually every table as the PK field.

I would design your junction table as:

TOwnershipJunction
--------------------
OwnershipID_PK (autonumber)
MemberID_FK (number Long)
LotID_FK (number Long)

The PK field is OwnershipID_PK and
a secondary index of the fields "MemberID_FK" and "LotID_FK" that is set to Unique.
(I would have to think more about this index. Might need some VBA to validate Lot/ Member relationships.)


See
Microsoft Access Tables: Primary Key Tips and Techniques
http://www.fmsinc.com/free/newtips/primarykey.asp





My $0.02.... your mileage may vary..

[NTC]

Agreed; a junction table is a more robust design. Not sure for a hobbyist HOA situation of sub 1000 records it is actually needed however; as duplicate Owner records really isn't a biggie one wouldn't think.... but it is definitely the more correct approach.

[InformaticsNurse]

PMFJI,

TOwnershipJunction
--------------------
OwnershipID_PK (autonumber)
MemberID_FK (number Long)
LotID_FK (number Long)

The PK field is OwnershipID_PK and
a secondary index of the fields "MemberID_FK" and "LotID_FK" that is set to Unique.
(I would have to think more about this index. Might need some VBA to validate Lot/ Member relationships.)

I went with this approach and as I have sifted through record after record, I find that it is working like a charm. Thank you to everyone for your help!