Multi-User Multi-Tenant cross-vendor relationships
Currently working on a database for use by multiple companies with multiple employees. As a single-tenant multiuser database, there is already a lot going on, and I understand the major challenges involved with getting this running the way it needs to be.
Essentially what needs to happen is this:
- Companies are set up with default data - billind address, default terms, default discounts etc..
- Each company has multiple contacts (employees) and items.
- Companies can create "relationships" with each other. Relationships allow the two companies to set their default terms and discounts.
- Not all companies actually use the system. Users will also need to be able to create a relationship with a company that doesn't have an existing record, and isn't a user on the system.
- "Companies" contain vendors, clients and entitites that assume both roles. Clients will not be users on the system, and as mentioned above, must be entered manually
- If a relationship is being set up with a vendor on the system, the user can search for them using their ID or other targets, and run the "create relationship" script.
The challenges that I am trying to quantify involve the two-way relationship of vendors. If CompA begins a relationship with CompB (both system users), I am currently assuming CompA fills in all the relationship-level details like AR_Terms and AP_Terms, which a user from the other company then approves, and marks the relationship "active". From here all the items, invoices etc.. etc.. will look up their data from the relationship record and store it in the LineItem record on transactions.
At this point in time, I have settled on a self-join model with two TO's of "Company" (CompA & CompB). The join table (RelationshipJoin) holds the data about the relationship of the two companies. CompA_RecordID and CompB_RecordID join the two TO's. From there, the Accounts Payable terms information is stored in RelationshipJoin::CompA_AP_terms and RelationshipJoin::CompB_AP_terms from which, it is looked up in future transactions.
I am fine with scripting etc.. I just want to make sure this all makes sense. It is going to take a long time to get to the point of figuring that out, and a second opinion would be excellent before getting too invested in this strategy.