Relationship to and from question.
I have three tables I want to "link" together. One table is Campground, the other two are Restaurants and Bait Stores. I want to show Restaurants and Bait Stores that are close to records in the Campground table. I've tried setting up both relationship to and relationship from fields. The problem is once I link a Restaurants or Bait Stores record to a Campground record, then those selections are no longer available to link to other Campground records. That's no good since Restaurants and Bait Stores can be close to more than one Campground. What is the correct way to set this up so it does what I need?
13 replies
-
One option is to create new table called NearBy. You can make this a child of Campgrounds because without a campground there is nothing near by. :) What we are doing is creating a Many to Many (M:M) table.
You will also make reference fields to Restaurants and Bait Stores tables.
The one issue here is that you have to check both fields for data to get what is near by. So you can add a text field and a choice field. The text field could be called placeName and the choice could be Type. Then have a trigger on update for the reference fields to copy over the name of restaurants or bait store and update the Type. Then you can quickly list all places with:
NearBy.placeName -
to make things easier, you can merge restaurants and bait stores into one table (they share similar data like address, open and close times, ratings, etc.) and use a choice field to differentiate. They really are the same thing, a place to go. Now you can add other places like lakes, or movie theaters, or pee wee golf.
That will make the M:M work easier.
I would not recommend storing data with dynamic choice fields. They are great for UI work.
I'll see if I can mock up a sample tomorrow.
-
Ok found a bit time. Here is what it could look like.
-
Thanks. I will take a look.
-
I'm looking at your example DB. Thanks. Please explain how I make the entries? I think I make a campground record and then add the NearBy places?
-
That is correct. You have to have a campground record before you can add a NearBy place, since NearBy is a child of Campgrounds.
Or you can make a Page that does the work for you. Let me know if you want a sample Page.
-
Yes, please do show me a Page example. Thanks.
-
-
Yes, please do show me a Page example. Thanks
Ok, here it is. It is very basic. It does check to make sure you don't add similar places to the same campground.
-
That's cool. Thanks. I assume I can hide the NearBy table since it is not used for data entry?
-
Sure. If you build out the Page you can hide all of the tables.
-
Thanks very much for showing me these many-to-many options. I started modifying my DB and ran into problems, so I decide not to battle it. I believe the solution I have will work for me. It may not be the best, but it does work.
-
If it works then that it all you need.
If you change you mind and want some help troubleshooting, we are ready to help.
-
Content aside
- 2 days agoLast active
- 13Replies
- 58Views
-
2
Following