SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Require help in creating table


Require help in creating table

Author
Message
Kaushal Parik
Kaushal Parik
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 34
I am having a main table - Client (ClientID Primary Key)

Then 2 tables below:

1. Employee (EmployeeID Primary Key, with ClientID as Foreign Key)
2. User (UserID Primary Key)

Now, it is possible that one employee might be working for more than one client. So, the EmployeeID/EmpCode/Paycode (many other data) will be different for a single person per client. Question is: User should only be able to use ONE user to access both client information. In other words, there should only be ONE user created in the system to access clients for whom employee is working.

Example: "ABC" is a taxi driver (Employee). He is working for "XYZ" and "PQR" (Clients).

1. Client Table:
ClientName: XYZ, ClientID: 1, ...
ClientName: PQR, ClientID: 2, ...
2. Employee Table:
EmpName: ABC, EmpCode: 679, ClientID: 1, Shift: Day, ...
EmpName: ABC, EmpCode: 579, ClientID: 2, Shift: Night, ...
3. User Table:
UserID: 100, UserName: ABC_User

Somehow, I need to normalize the "User" "Employee" table in a way that with only "ABC_User" user login, "ABC" should be able to access data for Client "XYZ" and "PQR". I thought of creating a table "UserAccess" with ClientID, EmployeeID and UserID.

Let me know your thoughts.
SrcName
SrcName
SSC-Enthusiastic
SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)

Group: General Forum Members
Points: 174 Visits: 394
first question is
do employee for example BBB should have access to "XYZ" and "PQR"?
Kaushal Parik
Kaushal Parik
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 34
Yes it is possible. Thinking of it in this way:
"BBB" is actually employee (project manager) of client "XYZ", but he is also working as employee (as consultant) for client "PQR".
TomThomson
TomThomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14306 Visits: 12197
The schema definition seems odd. You might find that things become easier if you change the definition of the Employee table to be something like

Employee (user_id not null references User(UserID), ClientID not null references Client(ClientID), ...other attributes..., primary key (UserID,ClientID))

so that the Employee table (which is a table where each row shows the relationship between a client and a user, not a table describing an entity) has the compound primary key required to indicate the client and user for which the row with that primary key describes the relationship. That will eliminate the dummy entity identifier EmployeeID, which you can't use as a primary key despite the statement in your first post that it is the primary key because it's not unique: the data in your post has two rows with the same value for that column, and which is in any case clearly being used to say which user is involved, not which Employee relationship.

Tom

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search