Relating Pairs/Individuals in the same database

[CHawk]

I am creating a database to track the activities of contacts, both in a calendar, and their financial records.

However, I also have the difficulty that I want to be able to track the activities of couples (pairs of contacts), and then relate those activities back to the original individuals through portals. Each individual can belong to more than one couple, and of course, each couple contains two individuals, so I believe that this is a many to many relationship.

I believe this means that I need an instance table between Contacts, and Couples.

I have created various initial tables:

Contacts - to have an ID for one of the pair

Contacts 2 - second instance of Contacts to have an ID for the second

Couple Instance - to have an intermediary table

Couples - to have a unique ID for each couple

Could you please tell me if I am on the right track with this, or am I misunderstanding the relationship situation?? Also, what exactly am I relating to what in the Relationships table??

Thanks in advance for any help you can offer.

Edited July 29, 2010 by Guest

[comment]

each couple contains two individuals, so I believe that this is a many to many relationship.

Not necessarily: if a couple always contains two individuals (or at least no more than two), it might be more expedient to have two ContactID values in the Couples table (provided a contact/couple instance has no attributes of its own).

If you do decide to go with a join table (what you call instances), you don't need another occurrence of the Contacts table (at least not for the initial setup). Just connect them as:

Contacts -< join table >- Couples

using ContactID and CoupleID fields in the join table.

[CHawk]

Does that mean that if my set up is, for example:

Table: Contacts

Fields:

pk_ID_Contacts

FirstName

LastName

FullName (calculated field)

Email

Table: Couples

Fields:

pk_ID_Couples

CoupleName

ContactName1

ContactName2

Table: CoupleInstances

Fields:

pk_ID_CoupleInstances

fk_ID_Couple

fk_ID_Contact

Is that the setup that you are suggesting? Or, am I missing something?

[comment]

Almost: there would be no contact names in the Couples table. You can display the names in a portal to one of the other tables.

See if this example helps:

http://www.fmforums.com/forum/showpost.php?post/246136/

[CHawk]

The information and the sample database file was a great help, and I have successfully managed to integrate this system into my own database design.

I now have an additional question. I am trying to design a calendar which pulls together all of my various types of contacts as possible appointments that I could select. For example, I could select an individual contact, or a couple of contacts, or a company.

As you can see from above, each individual entity can be tracked back to a specific primary key contact ID, but I want to be able to select different types of contacts for different types of events.

For example:

Couples for training sessions

Individuals for training sessions

Companies for meetings

Individuals for meetings

My Tables are

Contacts

Couples

Companies

Couple instances

Company instances

Events

Training sessions

Appointments

I am not sure whether I need the 'training sessions' and 'appointments' tables, as these are just subsets of the 'Events' table really.

How do I start to go about relating all of these different tables?

Any advice you can offer would be gratefully received. Thank you.

[bcooney]

What's the difference btw Companies and Company instances?

[CHawk]

I haven't fully developed this part of the database yet. With the couples, I needed the couple instances as a join table between contacts and couples. Maybe I don't need that with the companies?!?

[comment]

I am trying to design a calendar which pulls together all of my various types of contacts as possible appointments that I could select. For example, I could select an individual contact, or a couple of contacts, or a company.

This is a tough question: on the one hand, individuals, couples and companies are three different entities; OTOH, they are also members of a "super-entity" - someone who can have an appointment.

There are three possible approaches here:

1. Create a super-type table of Contacts with field common to the three sub-types; this is the most proper solution - but also the most difficult to implement;

2. Put all types of contacts in one table (with some fields filled only for a specific type);

3. Use a "star-join" table for the appointments (i.e an appointment record has fields for IndividualID, CoupleID and CompanyID, with only one of them filled).

With the couples, I needed the couple instances as a join table between contacts and couples. Maybe I don't need that with the companies?!?

It depends on whether an individual can belong to more than one company.

[CHawk]

I think that this is the way that I might need to go as I am going to also be relating the 'Events' to 'Financial Transactions' as well. However, I will also wish to have these various 'Contacts' or their 'Sub-Types' related to Financial Transactions with no 'Events' involved. Am I right that this would be the way to go. In which case, can you point me in the right direction somehow?!?!?

This is not really appropriate as I am having to use different User Layouts to enter different sub-types of Contact

This might be the way to go, but I believe that it involves a lot of extra unused Database real estate, and also is presumably also going to be more complicated by the existence of the 'Financial Activities'???

Thank you for all of your advice. You are a great help.

[comment]

If you need a relationship between transactions and contacts (of all sub-types) then the third option is probably not the best choice.

You can find more discussion and some demo files if you search the forums for "supertype" or "graham". However, keep in mind that this is not an entry-level task.

I am having to use different User Layouts to enter different sub-types of Contact

That's not really a problem, since you can have a layout (of the same table) for each sub-type. And in version 11 you can use script triggers to show only records of matching sub-type when you switch layouts.

[CHawk]

Firstly, thank you so much for your input. It is invaluable to me, and very kind of you to advise me in this way.

I do not mind the structure of the database being complicated, but clearly would prefer it not to be so - if it is not necessary. Therefore, having studied the various different threads and topics about Graham's technique, and Super/Sub-Type Databases, it seems like this is probably the way to go, but I wish to be absolutely certain before I proceed with the complicated process ahead. I have decided to summarise my needs for the database to make sure that you agree on the right way forward.

-------------------------------------------

The database is based around the activities of people within a company. Let us call them 'Employees'. There are not many, but I would like to be able to vary this model for greater numbers!!!

Table of EMPLOYEES

--------------------------------------------

I need a contact database for all of our individual contacts, for all of our groups of contacts (usually couples, but could be groups), and all of the companies with which we do business.

CONTACTS or COMPANIES (: Supertype)

(:?Sub-Types)

COUPLES

GROUPS

ORGANISERS

Specific Individuals can belong to more than one of these sub-type categories, and I would need to track their activities in different ways for their various forms of existence.

I need to record appointments with each of these different kinds of CONTACTS, both the Supertype, and the Sub-Types.

I need to track payments for these various appointments, if there is a payment involved.

These payments occur in different countries with different currencies and different accounts.

I need to be able to do financial reports based on the interaction between different 'Employees' and the various 'Contacts', 'Companies' or sub-groups of 'Contacts'.

I also need to be able to keep a calendar of all of the appointments between all 'Employees' and any type of 'Contact' or group.

--------------------------------------------

Layouts

I will create different Layouts for the Individual Contacts, the Companies, the Couples, the Groups, and the Organisers from which, I will need an overall Contacts database, as well as specific Contact Type lists as well.

The Contacts Layout will include portals referring to the activities of the individual Contacts in their roles as Couples, Groups, or Organisers.

The Calendar Layout will provide different views (day, week, month, year) selectable by different Employees. I will need an easy entry system to quickly pull up the 'Employee', and the Contact, or Company, or Couple, etc involved, and any financial transaction connected to that.

The Financial Records will be partly consisting of Event payments, and partly related expenses. So, for example, one related expense could be hotel expenses - (the hotel would be recorded as a company, and the expense could be separate to the event, but would need to be related to that particular event.

Finally, I would need to produce reports of Events and their specific expenses, and overall reports for individual Employees, or individual Contacts or Couples, etc.

--------------------------------------------

I hope that this is all clear, and easy to digest. If it helps to point me in the right direction, then it has been worthwhile writing all of this down!!

Thanks in advance.....

[comment]

I have decided to summarise my needs for the database to make sure that you agree on the right way forward.

It's not for me to agree or disagree. A lot here depends on your workflow and other details that are difficult to describe in a short forum post.

My tendency is not to go into super-type/sub-type model unless it's absolutely unavoidable. The dividing line is difficult to define.

Basically, the super-type/sub-type model is nothing more than a large table with some of the fields pushed into smaller satellite sub-tables. However, implementing this in Filemaker has additional implications; for example, calculations may be forced to unstored even though they are within the same (virtual) record.