Hi,

I'm wondering what would be the best way to group contacts by their company. Right now a user can group their contacts by custom created lists but I'd like to be able to group contacts by their company as well as store the contact's position (i.e. Project Manager of XYZ company).

Database wise this is what I have for grouping contacts into lists

contact
[id_contact] [int] PK NOT NULL,
[lastName] [varchar] (128) NULL,
[firstName] [varchar] (128) NULL,
......

contact_list
[id_contact] [int] FK,
[id_list] [int] FK,

list
[id_list] [int] PK
[id_user] [int] FK
[list_name] [varchar] (128) NOT NULL,
[description] [TEXT] NULL

Should I implement something similar for grouping contacts by company? If so how would I store the contact's position in that company and how can I prevent data corruption if a user modifies a contact's company name. For instance John Doe changed companies but the other co-workers are still in the old company.

I doubt that will happen often (might not even happen at all) but better be safe than sorry. I'm also keeping an audit trail so in a way the contact would still need to be linked to the old company as well as the new one but without confusing what company he's actually working at the moment.

I hope that made sense... Has anyone encountered such a problem?

UPDATE

Would something like this make sense

contact_company
[id_contact_company] [int] PK
[id_contact] [int] FK
[id_company] [int] FK
[contact_title] [varchar] (128)

company
[id_company] [int] PK NOT NULL,
[company_name] [varchar] (128) NULL,
[company_description] [varchar] (300) NULL,
[created_date] [datetime] NOT NULL

This way a contact can work for more than one company and contacts can be grouped by companies