First of all, let me apologize for the title, this predicament I'm in is quite impossible to describe in few words.
The application architecture involves a relationship between a company, clients, and its staffs.
1) A client creates an account (Account table with login and password), and creates a profile (Profile table with name, phone number, etc)
2) The client chooses a company for a service. At this point, the client becomes a client of the company, and a record is inserted in a regular many-to-many CompanyClient table.
Now, the company owner must be able to edit details of this client, and this includes profile information, such as phone number and email. However, these changes must not be reflected on the clients account when he logs in, e.g. the profile record of the client cannot be modified. It is as if the client has a private profile, and inside the a company he has another profile. A client registered as Thomas might have his name changed to Tom by a company admin that finds it easier to recognize Thomas by Tom.
Also, a company may add a client to its client list, a client that has no account or anything.
To solve this, I came up with this design:
Account -Id (FK to profile table Id, an account cannot exist without a profile) -Login -Password Profile -Id -Name -Email Person (basically a clone of the client's profile at first) -Id -Name -Email -Profile Id (nullable, a client without an account) CompanyClient -Company Id -Person Id
Keep in mind that a client may be associated with multiple companies, which means there would be several "profile clones" in the
Ok, this is half of the problem. Here's the other half:
a company staff may very well have an account or not, just like a client, and a company must also be able to change the staff profile without affecting his account, if any.
A staff has all of the
Person table columns, and more.
I thought about making a
Staff table with an FK to
Person. This would also allow for a
Staff to be granted access to the system, which would cause a record to be inserted into
Account, and the
Profile Id column of his
Person table record would be set to the newly inserted account Id.
The last part of the problem is that a company must be able to add additional information about a client, such as rating, observation, etc. Because of this, having the
Person table starts to sound wrong.
I'm not sure this is the right way to go about this problem, or if adding all of the
Person columsn to the
CompanyStaff table would be the right way. This feels like a very unusual design, any help is appreciated.
Maybe the best way is to have a Profile, Client, and Staff table, all with replicating information such as name, phone, etc?