Database structure for an agency with a client and service provider

Hi there

Any advice would be much appreciated.

I want to build a CRM with the following entities:

  • Agency
  • Client
  • Service Provider

The system can have multiple agencies, the purpose of an agency is to connect a Client and Service Provider based in certain properties.

Here is the challenge, a Service Provider and Client must be able to register at multiple Agencies, but the Agencies are in fact in competition with each other, they don’t want to share user data or see each others users.

Any advice in how the database should be structured?

Depending on how many agencies and records there are for each one, something like this:

Agency Connections

  • User
  • User Type
  • Agency
  • User information specific to Agency (contact, rate, whatever)

That builds in the privacy rules as well as different profiles per agency.

Thank you for the quick response.

There will be 40 agencies, each agency will have between 100 and 500 clients and 5000 odd service providers.

I need to build a “registration/login page” for each agency which I want to display on their own agency websites, that way their clients and service providers can register or log into the CRM via the iframe. From there on the user can contine on the CRM.

Questions:

  • Is the above possible?
  • Is there a way to link a client/service provider to the right agency if the user log in or register on the agency login/registration page which is actually an iframe of the CRM?

Sure. You can read referring domain or add a url parameter or session variable to ID which agency it is for.

Great, thank you