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 Person table.
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 CompanyClient and 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?
Best Answer
How much more ? 4-5 column extra ? I suggest try to keep both table same with more column as additional. along with ClientType Column int.
OR
Client Info table means ,those minimum info is need to become client. Same thing is true for Staff too .Isn't it ? So Staff info will be maintain in 2 tables.One which is necessary to become client will be in client Table.Rest in Staff table.
Now as per rule Company can change same fields for both Client and Staff. So may be Staff info is edited from 2 place,but both are 2 diff. thing.
So there will be Profile table for both Staff and Client.
This is not my final design .It all depend upon MIS report and frequency of use.