Best way to identify a contact unique to an account for specific roles

[datasponge]

I would like to define one contact uniquely for an account for each of several roles. Specifically I want to identify one contact uniquely for each of the following roles:

Primary Contact
Technical Decision Maker
Financial Decision Maker

and possibly others e.g. who to send product updates to.

I'd like them to be enforced as unique so that I can easily define tools to package update emails, inform of new features, etc and control who to send it to based on a standard query that guarantees a single contact per account without arbitrarily choosing one based on some criteria.

I could add custom fields to the contacts module such as a checkbox for each role, but I don't know how to force it to be unique.

I could add a select field to accounts for each role, but I don't know if this will create issues by bypassing the accounts_contacts relationship table and possibly leaving garbage around.

Is there a way to extend the accounts_contacts table by adding custom fields there to identify roles?

Any recommendations of the most Sugar-friendly way to do this would be greatly appreciated.

Thanks

Phil

[datasponge]

Ok, no response, but I did some research and here is my impression at this point. Please comment if you think this is a good, bad or avarage solution.

The best solution I can see is to add a custom field to Accounts and define a relationship so that it shows the Select button to edit and in detail screen allows you jump to the Contact record. This can be done for any contact I want including Technical Support Contact, Final Decision Maker, Technical Decision Maker or whatever.

This way it will save the contact_id into this field, so I can use this to join tables in external queries. Therefore, I can also populate this field from my current data joined to Sugar tables to get the contact_id.

I can use a similar strategy for any similar Unique relationship to other tables.

I think I'm getting the hang of how SugarCRM is put together.

Comments?

Thanks

Phil