Got a many to many type question....and more

[dmj]

My boss owns 9 companies. He wants me to create a mailing list database that will hold all the contacts in the same place, but seperate them by the business they are associated with at will. Any given Contact can be a customer of more than one business. So, it appears I have a many to many relationship here, right? I have tried to set it up a couple different ways, mainly trying to use a template, so far, to no avail. I'm a total newbie to Filemaker and also a pretty beginner database designer. I'm teaching myself.....

Any ideas/pointers on how to set this up?

Edited August 29, 2006 by Guest

[bcooney]

You'll need three tables (Companies, Contacts, CompanyContact).

In Companies:

__kP_CompanyID (primary key, number, auto-enter serial, prohibit modification)

and any other fields for the company such as name, address, etc.

In Contacts:

__kP_ContactID (primary key, number, auto-enter serial, prohibit modification)

and any other fields for the contact

In CompanyContacts:

__kF_CompanyContactID (primary key, number, auto-enter serial, prohibit modification)

__kF_CompanyID

__kF_ContactID

This is the join table. It holds the combinations of companies and contacts.

Then make a portal on the Company form layout that shows records from the join table.

Also, make a portal on the Contact form layout that shows records from the join table.

I've attached a sample.

MyDatabase.fp7.zip

[dmj]

So how I've got it set up now (in my head) is to have a contact table for the customers/contacts...(contact_id, name_last, name_first, street, city, state, all the usual)....PLUS, in the contact table I would also include fields for Company_ID_1, Company_ID_2, etc UP TO 9. Then, have a seperate table for each company, assigning one of the companies as Company_ID_1, another as Company_ID_2, etc, etc to each table (one table for each business).....

Am I on the right path here? I havent began to put it together for testing. I want to avoid redundant data.....blah. Help? :)

[dmj]

Sweet....now I just gotta figure out how to get it all together.....thank you very much. i'm going to look over this while i'm taking my non-existant lunch break......I would love to have this thing working before Friday so I can move onto more usual tasks

[bruceR]

"Company_ID_1, Company_ID_2, etc UP TO 9. "

Ack no. Any setup like that means you're not normalizing the data. There should be a related company/contact join table.

[dmj]

Yah Im figuring it out. Normalization is obviously something I have to spend alot more time learning and thinking about. I'm getting into this enough now to hope I get to do more of it. We'll see.