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.
personworks for one
companycan have multiple
companycan be in multiple
industrycan have multiple
companycan have multiple
membertypecan have multiple
usercan play multiple
rolecan be assigned to mutliple
usercan have multiple
taskcan be worked on by multiple
taskcan target multiple
personcan be targeted by multiple
personcan only be added by one
usercan add multiple
companycan have 0 or 1
parent_companycan have multiple child company
I have come up with the following design and it has undergone some changes:
- Are there better ways to display the
- For example, if a
personcan have only one
tel, should I make an extra table just for
persontable? Would it considered to be “unclean”?
- For the table
typenameboth 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 ^~^
The main issue I see is that while all primary keys are defined as Int some foreign keys or references are defined as varchar.
- company in contact table
- user_role in user table
- parent_company in company
- added_by in user table
Also role_id is length 10 while all other primary keys are 11.
Personally I would prefer capitalised table names, User, Company etc
Update for the edited version:
You might want to create a table for phone, mail,fax etc say
contact_info that could contain a string field holding the contact info and a type field (email, phone, fax,…). This way you could store several phone numbers for instance, if you want to limit email to one you could either leave it in the
person table and not allow it here or have a business rule to only allow one email row in
This table could also be useful for
company if you want to store email or phone numbers for the
company like firstname.lastname@example.org or the number to the company switchboard
For the table membertype, should company_id and typename both be PK?
About the address solution:
address table contain enough info to make each address unique, I can understand that a company can have more than one address but should it allowed for two companies to have the same address (by that I mean the same row in the database) so maybe it should be changed to a one-to-many from
company and ´address´ but a one-to-one in the other direction.
I also think it could be good to have some kind of label in the two address-link tables so one can easily identify an address like “home”, “work”, “Office”, “Warehouse”…