Relationship Problems - Help!

[aldipalo]

I have a Condominium Management application that keeps giving me problems with relationships.

Basically I have 5 tables (In a separation Model).

Units - There are 241 units and 2 buildings.

Owners - There can be many owners to one or many units.

Renters - There can be one renter record to one unit.

Vehicles - there can be 2 vehicles per unit

Special Entry - there can be many SP's per unit

I have the 2 vehicles showing in a portal on the Owner layout as well as the renter layout. These units, for the most part, are rented short term (2 weeks - 3 months) therefore we want to keep the owners vehicles active at all times. So I set the relationship to:

OwnersLayout::OwnersID = OwnersLayout_Vehicles::fk_OwnersID

RentersLayout::RentersID = RentersLayout_Vehicles::fk_RentersersID

So here's the problem. If I have 2 vehicles for the owner(s) and I enter them in Owner1 they don't show in Owner2. If I set the relationship to UnitID then I have the renters Vehicles as well as the owners Vehicles. I have a calculation that checks to see if there are 2 vehicles registered and exits the script if that is the case. So, I must have them separate. If the vehicles are separated between owners then they could register more than 2 vehicles.

My primary fields are:

OwnersID

RentersID

VehiclesID

SpecEntryID

UnitID

There must be a way to have the vehicles common to all Owners and Unique to Renters.

Of course I have effectively the same issue with the Special Entry relationships.

I’ve attached copies of the TOG.

TIA

[mr_vodka]

I would create a self join in the owners table keyed on fk_unit_ID. Then create a calc of: List ( SelfJoin::OwnersID) Link your owner vehicle TO to this and you should show all vehicles for all owners of each unit.

[aldipalo]

Hmm, but would I be able to enter the data into the portal on the Owner as well as the Renter layouts?

See layouts.

Al

Edited January 29, 2009 by Guest

[mr_vodka]

Ok perhaps I have misunderstood you. Do you have these portals with allow creation of record or is this scripted?

[aldipalo]

Hi John:

Yes. However, the vehicles portal is scripted so that it checks to see if the max number has been reached first before allowing entry.

I spoke with the client last night and they tell me that the "Entry Number" field, which they get from their computerized entry card system is issued to each owner, but, not to Renters. So, all owners of a unit are assigned the same Entry Number. I guess this will work, but, I'm uncomfortable with this because someone else is assigning the number and I'm not 100% assured that the numbers will always be unique. So, I'd still like a method that will rely on our own data. Any thoughts or suggestions is appreciated.

Al

[mr_vodka]

Hmmmm. After re-reading this thread again, it seems to me that the vehicle for the owners should relate to the unit. However, the vehicles for the renter should relate to the renter, not the unit.

[aldipalo]

That's the way I had it, but, the way the system works there is a one to many relationship between Unit and owner and Names are often different. So, you can only have 2 vehicles registered and there could be/are 2+ owners to every unit. One unit has 9 registered owners. Therefore ownerID is no good because it will give each owner 2 vehicles and the special entries will only show for each owner not combined. Btw, I just spoke to the General Manager at he told me that the Entry number can change since it is assigned to the Key Fob issued to the owners. If the Key Fob is lost they get a new number. So there goes the data consistency. :

I guess I need a separate ID for the unit that I only assign to the owners and not the renters. I thought perhaps I could concatenate 2 fields that would be unique, but, I'm at a loss to see which fields. How can I set up a unique serial number that is the same for all UnitID's? Any ideas?

[mr_vodka]

I dont quite follow this part.

"If the vehicles are separated between owners then they could register more than 2 vehicles"

Here is what I understand so far

1. Renters can only have up to 2 vehicles

2. Owners can have up to 2 vehicles even if there are renters

Anyway, if you have both of owners and renters relating to the vehicles, then you can use a join table to keep track of the active vehicles to the current unit. You can add / delete the record from Owner / Renter vehicle portals and have an indicator that it is active for the unit.

Tables

Owner

Renter

Vehicle

Unit

ActiveVehicle

P.S. I guess I should point this out even though I am sure that you already know... You can combine the Renter and Owner tables.

[aldipalo]

Hi John:

Thanks for the thoughts and advice. I resolved the issue by creating a UnitOwnerID within the Units table and then related the Owners and vehicles as well as owners and special entries by this unique field. It appears to have fixed the problem.

Al