Database design grouping contacts by lists and companies
- by Serge
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
