Many to Many relationship?

[davesmith202]

I have Contacts that can be linked to many Organisations. Each Organisation can have many Contacts. So, does that mean I need a Many to Many relationship with an intermediate table? Contacts - ContactsOrg - Org

Also, for each Organisation, I need to have a memo field, so that specific Organisation can record info about that specific Contact. Am I right in thinking this would go in the intermediate table?

Thanks,

Dave

 

[namliam]

I have Contacts that can be linked to many Organisations. Each Organisation can have many Contacts. So, does that mean I need a Many to Many relationship with an intermediate table? Contacts - ContactsOrg - Org

No, you cant have a many to many relationship... thus Yes you need the intermediate table to make a n-1 and 1-m relationship to resolve the n to m relationship

Also, for each Organisation, I need to have a memo field, so that specific Organisation can record info about that specific Contact. Am I right in thinking this would go in the intermediate table?

You will probably want more information there like date(s) but yes the info/memo field can go there (too)

good luck !

 

[jdraw]

I agree on the intermediate table --called a Junction table, but you do not need a memo field necessarily. In your contacts table place the fields you need to record info about that Contact.

Free video on junction tables/many to many relationship.

Good luck.

OOoops: I see namliam hs responded while I was typing.

 

[davesmith202]

Thanks guys. Its as I thought but was just feeling a bit rusty!

 

[davesmith202]

I'm wondering on the correct structure to do this.

Can I do this:

Contacts table, Organisation table, OrganisationContactsSub table.

The idea is, you have Organisation table that has a 1 to many relationship with OrganisationContactsSub table. Then, in that sub table, you have ContactID that links to the Contacts table. Other fields include Notes (a memo field). Then, the Orgnisation can have many Contacts and store the notes too.

Or, do I need to do the junction table?

 

[namliam]

as long as you make sure you end up with a 1 to many relationship between the tables and dont end up with a Many to Many, cause M:N relationships just break your database.

 

[gemma-the-husky]

I don't use them, but if you only want the names, I expect that a multi-value field (mvf) does this sort of thing behind the scenes for you

 

[davesmith202]

All done now. I used a junction table after all.