I’m working on an solution that is fairly complex and comprehensive. It’s intended -eventually- to enable management of many parts of a trucking business.
I want to keep my table structure as efficient as possible. I’d like to set up one table for Contacts, but that seems impossible, even through multiple instances of the same table.
“Contacts” for this business is somewhat tricky. Contacts could be Customers, Vendors, Employees, Prospects, Regulators, etc., each with common, but also unique defining attributes.
Worse, within each of these Contact classes, there may be several sub-classes. Customers, for instance, could be Contract Customers, Ad-Hoc Customers, Instructional Customers (students), Maintenance Customers, etc. Further, Customer Contacts might also be Employee Contacts (or vice versa).
Sub classes are complicated on their own. “Employee” Contacts is tricky as an Employee might be both a Driver and a Driving Instructor. Each case will require discrete, specific, detailed attributes to describe them. And these attributes will differ depending on the Employee Contact’s current Role. Many of these specific attributes define the legal capacity of the Employee to engage his current role, so they are critical attributes.
If a Driver is also a Manager, then he will need to be able roll up his managed subordinate Employee Contacts. If the Driver is also an Instructor he will need to be able to roll up his Student Contacts.
Obviously, there are many other complexities, but I hope this brief description gives some sense of the interrelationships and complexities of this situation.
I’m new to database architecting. What I’m asking for is not a specific solution, but any advice any of you ol’ hands may have in terms of how to structure tables and relationships to best model this kind of complexity while allowing for efficiency, data integrity, and future flexibility (expansion and/or changes).