Many many-to-many relationships

[jeremychance]

Hello, 

 

I am in the planning stages of building a Contact / Marketing Outreach solution, and it has become clear that all my tables need to interrelate in a many-to-many way.  It will have:

 

Individuals

Entities (i.e. companies, organizations)

Lists

Notes

 

All these things interrelate in a many-to-many way, so they require a join table (though Notes might be an exception, as any given note would only relate to a single Individual, Entity or List).    

 

What I'm wondering is if it makes sense to use only one join table between all 4 of these things and use complex relationship criteria, scripts, calculations, and filters to build out all the requisite layouts - OR - do I use a different join table for each relationship.  

 

Thanks!

Jeremy

[Steve Martino]

If you could describe with a little more detail your perceived work flow, you will get more suggestions.

For example, what will 'Lists' do?

Do notes pertain to Lists, Individuals, or Entities?

Could Individuals & Entities be on the same table? For example, if it were a Customers table, you could have customers who are individuals, and customers who are companies.

How do you 'contact' these individuals/entities-phone, letter, email and/or combination of both?

The join table would be needed for a task you are performing that needs data from different tables.

An example would be when you have customers and are creating invoices from line items.

[jeremychance]

I work for a multidisciplinary design/fabrication/research practice, so our contact lists covers a wide array of industries and uses, and our targets are often multidisciplinary as well.  

 

We want to be able to track Individual contacts, the multiple Entities they might belong to, and to organize both Individuals and Entities in Lists for specific outreach campaigns (phone, individual emails, as well as email blasts). Ideally, Individuals and Entities can interrelate.  On a layout showing one Individual record, I want to be able to see all the Entities the Individual is a part of.  On an Entity record, I want to see all the Individuals that comprise the Entity.  And I want to be able to have multiple, time/user tagged Notes for every Individual, every Entity, and every List.

 

Examples:

1) we often want to contact a museum curator in one context, but contact the same person through their academic position at a university for a completely different reason; however, we want to be able to keep an overview of all communications with this person.

 

2) we fabricate sculptures for artists, sometimes we try to reach them as individuals, but other times we campaign the multiple galleries that represent these artists.  artist and/or gallery could become our client, so again, we need to see an overview of the interrelationships

 

does this help clarify?

 

Thanks!