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 onecompany
- one
company
have multipleperson
person
andcompany
can have multipleaddress
- one
company
can be in multipleindustry
- one
industry
can have multiplecompany
- one
company
can have multiplemembertype
- one
membertype
can have multiplecompany
- one
user
can play multiplerole
- one
role
can be assigned to mutlipleuser
- one
user
can have multipletask
- one
task
can be worked on by multipleuser
- one
task
can target multipleperson
- one
person
can be targeted by multipletask
- one
person
can only be added by oneuser
- one
user
can add multipleperson
- one
company
can have 0 or 1parent_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 oneemail
but can have multipletel
, should I make an extra table just fortel
whileemail
is still in theperson
table? Would it considered to be "unclean"? - For the table
membertype
, shouldcompany_id
andtypename
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 ^~^