Need a new relationship

[rick altman]

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

[webko]

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.

[rick altman]

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?

[webko]

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

[rick altman]

Many thanks, Webko. I will give it a try.