Example for many-to-many relationship

[tina2007]

I know there are one-to-many and many-to-many relationship. I find a lot of one-to-many relationship, but could anyone give me an example of many-to-many relationship?

I have built an new module named groups and I want to assign the contacts to groups, how can i do it? if it's possible that one contact could belong to more than more groups?

Need your help!
Tina.

[roblaus]

Hi,

A many to many relationship cannot be expressed directly. You always have to go through relationship tables. You'll find many of them in Sugar but few of them are actually used in the program logic. As a working example: opportunities_contacts with the main fields: id (the identifier of the record itself), contact_id, opportunity_id.

Whenever you add a contact to an opportunity a new record is inserted into this table. If you later on open the subpanel contacts under an opportunity a SQL statement is fired that in principle says "select (fields) from contacts where id in (select contact_id from opportunities_contacts where opportunity_id = (id of the current opportunity))"

There's a lot more to say but I hope that will do as a start...

rgds