Jump to content

Need a new relationship

rick altman

This topic is 2954 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Hello, for our annual conference, our database is people-oriented: Our main table, PATRONS, tracks the people who attend and all of the relationships are established around the events that people attend and the things that people do.We create relationships to various other tables, such as ACTIVITIES, SEMINARS, and DEMOGRAPHICS.


As you would expect, one of the fields in PATRONS is Company, and when we sign on a sponsor, there might be several people attending from that organization. That presents us with a challenge: for these purposes,Company becomes the central organizing unit (instead of Last Name), and we would like to be able to view and manipulate our records that way. I would like to create a relationship (and a layout) whereby the other fields could revolve around Company: where we could add a record in which first we add the Company, define its role in the conference, and then add the people who will be attending.


The attached image shows two of our primary views: on the left shows the PATRONS table and on the right is the ACTIVITIES table. As you can see, Company is just one of the garden-variety fields in PATRONS.


I'm good with scripts and calculations; less so with relationships, as I am unable to see them in my head. I would be grateful for some help in visualizing and creating the relationship(s) necessary to make this happen.



Rick A.

Pleasanton CA


Link to comment
Share on other sites

From what you have described, you need a Company table that would sit above Patrons. I'd structure it something like the attached.

Then at the Patron level, you can create a Company Value List from Companies, to make the field a drop down.

Then Companies can have a portal to Patrons, and Patrons can have a portal to Activities.

Screen Shot 2016-01-21 at 8.19.06 am.png

Link to comment
Share on other sites

Thank you, webko. A few questions:

  1. How is this different/better than creating a self-join? My needs are simple enough that that route might get the job done.
  2. I would need to migrate the data from the Company field to the new table, yes? In the past, I have done that with a big ol' export/import procedure.
  3. What other fields would reside in the Company table?
Link to comment
Share on other sites

1. It's a *lot* easier to get the lists of Patrons that are associated with a Company with a separate Company table. My rough rule of thumb is that if a piece of information could appear on more than a couple of records in a table, it's time to think about having another table to hold it... And in this case, the same Company could potentially be used a lot of times

2. Yes, and then put the Company ID back into the Patrons::_kf_ID_Company field to establish the relationship

3. Anything that is specific to the Company -

Note that my personal preference is to have any/all Contact information in another table that has multiple FK to relate to Company / Patron etc, which then means that they can have as many email addresses and phone numbers as they want - might not be really required in this instance, but worth thinking about

Link to comment
Share on other sites

This topic is 2954 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.