Help understanding Many to Many / Join Table

[jpeirce]

I am a little confused with many to many relationship / join tables.

For example:

I have a table with pet owner names; and a table with pet types.

Each owner can have multiple pet types, and each pet type can have multiple owners.

Both are listed with value lists.

I would like to be able to choose either an owner and get all the pets they own, or choose the pet type and get a list of the owners.

I tried doing this with checkboxes, but it wasn't working, and then with portals and it wasn't working. Can someone take a look at my example and give me a hint as to where I am going wrong?

Thanks for your help!

JP

[T-Square]

I think there's something off with your structure. The structure you describe allows an Owner to be a PetType and a PetType to have an Owner. By putting the PetType field directly in the Owner table, you are in effect saying that the PetType is an attribute of the Owner ("Bob is a Dog"). But the PetType is part of a different thing altogether--the Pet itself, and you need a table to represent this ("Fido is a Dog. Bob owns Fido.").

A basic Pets table would minimally identify the Owner (by OwnerID) and the PetType (by PetTypeID). Additionally, you'd probably have things like Name, Birthdate, etc. You would add relationships between Owners::OwnerID<->Pets::OwnerID and PetTypes::PetTypeID<->Pets::PetTypeID. Set the first to allow creation of records. Next, add a *portal* for Pets on both the Owners and PetTypes layouts, and add fields to these portals.

[jpeirce]

Yes you are correct. I think I was trying to do this too simply.

I have changed it, and it works, but not really how I want it to.

Set up as you suggest, I work in the PETS (join) table, but this adds records in the OWNERS and PetTypes tables. I would like to do it in reverse, but it doesn't quite work. I basically want to have an owners table, that gets filled in with no specific pet info, and a pet-type table with no specific pet info. Then in a join table PETS, I make records with pet name, pet type, and owner. Then in the portals on the owner table, you would see the name and type of pets each owner owns, and on the PetType table portal, you would see the pet name and owner name of each type.

The way it is set up now, I get multiple records of the owner, and pet type. When I uncheck 'allow records creation' I cant enter data into the pets table. It's almost like I have this backwards?

Here is my revised file...

Thanks for your help, I do appreciate it!

JP

Pets.fp7.zip

[T-Square]

To work in the Pets table directly, change your ValueLists and remember that you're adding info to the Pet entity--not the Owner or Type entities. See the attached for a quick idea. I changed the value lists to use entries in the Owners and Types tables, and added the right fields in the Pets Layout.

Petsagain.fp7.zip

[jpeirce]

thanks, I get it now. I do appreciate all your help!

take care

JP