0

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?

  • Staff and Client are Person. In code, I use an inheritance here. Staff has a bunch of foreign keys to other tables that represent their schedule details, and an "admin access only" column. A Service is identified by an Id, and it has an FK to Company. Each service belongs to a single Company, with multiple Services. Each Staff performs one or more Service. A Service needs a Company to exist. The Client and Staff details (name, email...) are properties of the relationship between them and a Company. – victor Feb 22 '18 at 17:16
  • A person migh have a UserAccount with name John, but in the relationship between John and Company "A", he is called "Kid next door". In the relationship between John and Company "B", he is called "Little dude from across the street". The same happens for a Staff: one may have an account with name "Anderson", but in the Company he words for, he is known as "Andy". I used name here, but this could happen for any information. – victor Feb 22 '18 at 17:21
0

A staff has all of the Person table columns, and more.

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.

Profile
 -Id
 -Name
 -Email
-- ClientType int not null (1=Client,2=Staff,3=Client who do not have profile)

Staff Table
It contain info of Staff table which cannot be edited by company.but can be edited by Admin

Person (basically a clone of the client's profile at first)
 -Id
 -Name
 -Email
 -Profile Id (not nullable)
-- ClientType (Denormalization)

CompanyClient
 -Company Id
 -Person Id
 --ClientType (Denormalization)

Rating Table
--RatingID
--PersonID
--Observatiion
--Rating

This is not my final design .It all depend upon MIS report and frequency of use.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.