Database design grouping contacts by lists and companies

Question

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

Answer 1

What you have along with the update looks about right.

So, as I see it, you have users, and each user has a master list of contacts. A user also has further lists for organizing contacts within the master list. Each contact has their name and contact information along with several companies they have worked for, plus they must keep track of their current company.

Keeping the companies in a separate table was a good idea. Normally a text field would work, but because you plan to use companies more as individual entities, a separate table works best.

I feel like I'm repeating what you have, but I'll put what seems the best setup. I'm just writing the following with my conventions (an underscore means one to many):

*user*
id [int PK], 
... 

*user_contact*
id [int PK], 
user [int FK (user)], 
currentCompany [int FK (company)] 
... 

*user_contact_company*
id [int PK], 
contact [int FK (user_contact)], 
company [int FK (company)], 
startDate [date],
endDate [date]
...

*user_contactList*
id [int PK],
user [int FK (user)]
... 

*user_contactList_contact*
id [int PK], 
contactList [int FK (user_contactList)], 
contact [int FK (contact)] 
...

*company*
id [int PK] 
... 

Then, for a basic grouping:

SELECT * FROM `user_contact` WHERE `user` = <USER_ID> GROUP BY `currentCompany`

But I don't think that would work the way you'd like, so you could have two queries:

SELECT DISTINCT `currentCompany` FROM `user_contact` WHERE `user` = <USER_ID>

Then for every company:

SELECT * FROM `user_contact` WHERE `company` = <COMPANY>

There are many other ways to do this, depending on how you're planning to implement it. For example, you could just do an ORDER BY, so all the companies are grouped together, and then your code that's displaying the companies can see if the current company is different than the previous company, and make the right distinction.

As for company positions, you could consider either doing text or referencing another table, depending on how you're using it. If you're going to do sorts, such that "Project Manager" will be grouped with other "Project Manager"s, then it should be in another table, otherwise someone might pick a different name than Project Manager, or make a name lowercase, even though they're semantically the same.