Mysql – Suggestion for a contact management database design

database-designMySQLmysql-workbench

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:

enter image description here

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 ^~^

Best Answer

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.

Related Question