Suggestion for a contact management database design I am currently designing a contact management database for a chamber of commerce. The goal of the database is to store all the person (except our own staff), all recorded companies (regular companies and member of the chamber), addresses of the person and companies, tasks that the staff are currently responsible for, a list of our staff (user) and the roles within the chamber.



Business Rules


one person works for one company
one company have multiple person
person and company can have multiple address
one company can be in multiple industry
one industry can have multiple company
one company can have multiple membertype
one membertype can have multiple company
one user can play multiple role
one role can be assigned to mutliple user
one user can have multiple task
one task can be worked on by multiple user
one task can target multiple person
one person can be targeted by multiple task
one person can only be added by one user
one user can add multiple person
one company can have 0 or 1 parent_company
one parent_company can have multiple child company


I have come up with the following design and it has undergone some changes:



Issue


Are there better ways to display the user-task-person relationship?
For example, if a person can have only one email but can have multiple tel, should I make an extra table just for tel while email is still in the persontable? Would it considered to be "unclean"?
For the table membertype, should company_id and typename both be PK?
How does this schema look now? Are there still some normalisations to be done?




I'm a newbee at database, there are definitely some design flaws or errors, it would be nice if you guys could give me some suggestions so that I can correct and improve this design. Thank you ^~^
I'm seeing a few misses.
First, the role table.  If one user can belong to more than one role, you're going to need another table between role and user.  1 table to store the roll(like you have already) and another to store a junction table which will store the relation between a role and a user.  That table would probably only have two columns, role_id and user_id.  The user table would not need to identify the role unless it was a primary role or default role.  That could also be accomplished in the junction table. 

Similar situation with Company - Member Type.  Since you only have a single column in the company table, you will only be able to store one member type per company.  If the requirement is to have companies be able to be assigned to more than one member type, you will need a junction table to store company|member_type relationships.  

One last thing I noticed, is there is currently no relation between Employee and Company other than the address.  That will definitely get confusing.
Since you want contacts to be associated with multiple companies, you will need another junction table to store the contact_id | company_id relationship. There might be a clever solution using your employee table.  Since that essentially is the only direct connection between employee and company, maybe just change that to the junction table, company_employee.  Then add a boolean/bit column (IsEmployee) to indicate whether the contact is actually an employee of the company.