Hi Bob,

I spent hours last night (again as I have done this before) trying to figure this situation out. There appears to be a flaw in the way the organization set this up. The rules are:
Every lot owner is automatically a member in the POA organization.
Every member only gets one vote regardless of how many lots are owned
Members are based on Member Households, so a husband and wife owning a lot or two is one membership and one vote.


There are lots owned by husband and wife as well as companies
There is a husband and wife (lets say Mike and Linda) who own a lot (say lot 128) and therefore only one membership and one vote.
Then there is another husband and wife (say Brian and Carol) who own lot 139 and therefore they are one membership and one vote.
Now, Mike and Brian have gone together and purchased another lot (lot 123) together (assume speculation maybe) and since they are both already members, they cannot become a member again nor can they have another vote for this lot.
So Membership is based on ownership but not tied to any given lot. Membership is a one per household and not per person, or lot.

This makes this a bit difficult as in your scenario, I would have a member_id in the lot table and would only be able to tie a single membership to this lot where in the case of lot 123 above, I couldn't really include the memberships of both Brian & Mike. This was why I added the LotOwnership joining table.

Now my thinking is I need to not link the lot owners through the membership table as I originally proposed but rather just have a join from the lot to the LotOwnership table and then from LotOwnership to Contact to represent who the persons are that own the lot. I originally tried to link it to a membership thinking that if you owned a lot you were also a member.

Thanks for your thoughts, I see I should have posted this in the DataBase design category.
Tim

>Hi Tim,
>
>The MemberContacts table and LotOwned tables are not needed based on your description below therefore you only need the 3 tables: This is because you do not require a many-to-many relationship on them.
>
>Contact (PK contactID, FK membershipID, field contactName)
>Membership (PK membershipID, field membershipName)
>Lot (PK lotID, FK membershipID, field lotName)
>
>The contact and lot tables will have a foreign key for the Membership table. The foreign key on the contact table will allow null so that a contact does not have to belong to a Membership. The foreign key on the Lot table will not allow null as it appears that a Lot must belong to one and only one Membership.
>
>To list contact names, do the following:
>
>

>SELECT     Contact.ContactName, Membership.MembershipName, Lot.LotName
>FROM         Contact INNER JOIN
>                      Membership ON Contact.MembershipID = Membership.MembershipID INNER JOIN
>                      Lot ON Membership.MembershipID = Lot.MembershipID
>