I've to create a DB structure which involves users, contacts, organizations and vendors. To break it down:
We have a system with users (employees who use the the system). then these users add contacts, organizations and vendors. Now, contacts and organizations are both go as one type: CONTACTS.. vendors, on the other hand is a provider.
Now, all this guys share same information to one degree (First name, last name, address, phone number, email, website). So my question is should I use:
1) one table for all of them with specific column specifying that its a user, contact or organization or a vendor. And according to that just leave some fields empty. and on user end just hide the according rows.
Thoughts: pros: One table which will be ease to integrate with other stuff;
cons: to much columns.
2) separate all of them to respectful tables. Now, this data will be also used with other tables (orders, services etc). so cons: too much tables and too much duplicate connections.
So, what would be a suggestion here? I read that most systems, aka CRMs use one table for all contacts and just separate the data by certain columns and hide the rows accordingly..
Thanks in advance..
Best Answer
To answet your question in a very short manner, it depends.
In the long version....
The way a database is designed should depend on many factors such as but not limited to the operational/informational needs and what problems to solve. Using DFDs and ER diagrams, user interviews to underatand those should reveal many necessary aspects to your database design.
The question you should be asking is "with this design, can the goal of this project be met? How well the operational and informational needs among all other things fit around it? Is there a better way to achieve it within the allowed time and budget? how will this design impact the application design or hence operational flow? and etc...."
With the application/UI design, and how users will do their work, you should be able to design the table structure to hit the right balance.
That said, you can have a contact_type column. With the use of appropriate column data type, leaving them null is not a big issue.
Yet again, whether that will really work or not is only revealed when it's validated against the reality in prototype.....