Design database, table [Resolved]

Posted by Nerdanalysis under Sql Server on 8/26/2013 | Points: 10 | Views : 1468 | Status : [Member] | Replies : 10
Hi,

I have a project where admin can create new studies and, he can give access to the existing users and later on to new users. How would that work ? Thanks in advace!

Kind regards
Rakesh




Responses

Posted by: Bandi on: 8/27/2013 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
Hi Rakesh,
Check the below script for the above requirement

CREATE TABLE StudiesMaster  ( StudiesId int PRIMARY KEY, Name varchar(10) )

insert into StudiesMaster values(1, 'MS SQL') , (2, 'JAVA'), (3, 'ASP .NET')

CREATE TABLE UsersMaster ( UserId int PRIMARY KEY, Name varchar(10) )
insert into UsersMaster values(1, 'chandu') , (2, 'manu'), (3, 'sailu')

CREATE TABLE StudiesAccess (StudiesAccessId int IDENTITY , UserId int REFERENCES UsersMaster(UserId), StudiesId int REFERENCES StudiesMaster(StudiesId))
insert into StudiesAccess values(1, 1), ( 1, 3), ( 2, 1)

SELECT CJ.StudyName, CJ.UserName, CASE WHEN SA.StudiesAccessId IS NULL THEN 'InActive' ELSE 'Active' END [Status]
FROM (SELECT SM.StudiesId, SM.Name StudyName, UM.UserId, UM.Name UserName FROM StudiesMaster SM , UsersMaster UM) CJ
LEFT JOIN StudiesAccess SA ON CJ.StudiesId = SA.StudiesId AND CJ.UserId = SA.UserId

DROP TABLE StudiesAccess
GO
DROP TABLE UsersMaster
GO
DROP TABLE StudiesMaster
GO


-- OUTPUT as follows for the above query..
StudyName	UserName	Status

MS SQL chandu Active
MS SQL manu Active
MS SQL sailu InActive
JAVA chandu InActive
JAVA manu InActive
JAVA sailu InActive
ASP .NET chandu Active
ASP .NET manu InActive
ASP .NET sailu InActive


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Nerdanalysis, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Allemahesh on: 8/26/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
Dear Rakesh, I am little confused with your question.
Well see the below tables so that it will help you.

1. Studies Master
-> StudiesId int,
-> Name varchar(10),
-> so on.....

2. Users Master
-> UserId int,
-> Name varchar(10)
-> so on.....

3. StudiesAccess
-> StudiesAccessId int,
-> UserId int,
-> StudiesId int,
-> So on....

Happy coding.



Nerdanalysis, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Nerdanalysis on: 8/26/2013 [Member] Starter | Points: 25

Up
0
Down
Thanks Mahesh for quick reply, That seems nice and i was thinking in same direction as well.

What would be the query to see which study the user have access and which study the user havent access? in short query to see user and study matrix.
How to assign access to the study if the user dont have access to that study?

Kind regards
Rakesh


Nerdanalysis, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Allemahesh on: 8/26/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
Dear Rakesk,
Good Question.
Please see the below Query.

1. What would be the query to see which study the user have access?
SELECT
U.Name as 'User name',
SM.Name AS 'Study Name'
FROM StudiesAccess SA INNER JOIN UsersMaster U
ON SA.UserId = U.UserId INNER JOIN StudiesMaster SM
ON SM.StudiesId = SA.StudiesId

2. Which study the user have not access?
SELECT
SM.Name AS 'Study Name'
FROM StudiesMaster SM
WHERE SM.StudiesId NOT IN (SELECT StudiesId FROM StudiesAccess)

3. How to assign access to the study if the user do not have access to that study?
Insert the user id in to StudiesAccess table to allow access to access to the study to user.

I have not testing the query. But you will get required output.

Happy Coding.

Nerdanalysis, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Nerdanalysis on: 8/26/2013 [Member] Starter | Points: 25

Up
0
Down
Thanks Mahesh, this is top class... in second query if the the study is assigned to even one user, then it will not come up for rest of the users, any thoughts? thanks a million.

Nerdanalysis, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 8/26/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Hi Rakesh,
This is the script for above DB Design...
CREATE TABLE StudiesMaster  ( StudiesId int PRIMARY KEY, Name varchar(10) )

insert into StudiesMaster values(1, 'MS SQL') , (2, 'JAVA'), (3, 'ASP .NET')

CREATE TABLE UsersMaster ( UserId int PRIMARY KEY, Name varchar(10) )
insert into UsersMaster values(1, 'chandu') , (2, 'manu'), (3, 'sailu')

CREATE TABLE StudiesAccess (StudiesAccessId int IDENTITY , UserId int REFERENCES UsersMaster(UserId), StudiesId int REFERENCES StudiesMaster(StudiesId))
insert into StudiesAccess values(1, 1), ( 1, 3), ( 2, 1)


--1. What would be the query to see which study the user have access?
SELECT
U.Name as 'User name',
SM.Name AS 'Study Name'
FROM StudiesAccess SA INNER JOIN UsersMaster U
ON SA.UserId = U.UserId INNER JOIN StudiesMaster SM
ON SM.StudiesId = SA.StudiesId

--2. Which study the user have no access?
--this is top class... In second query if the the study is assigned to even one user, then it will not come up for rest of the users, any thoughts
SELECT
SM.Name AS 'Study Name'
FROM StudiesMaster SM
WHERE SM.StudiesId NOT IN (SELECT StudiesId FROM StudiesAccess)

--3. How to assign access to the study if the user do not have access to that study?
DECLARE @study int = 1, @user int = 2
IF NOT EXISTS (SELECT 1 FROM StudiesAccess WHERE StudiesId = @study AND UserId = @User)
INSERT StudiesAccess (StudiesId, UserId) VALUES(@study, @User)

SELECT * FROM StudiesAccess

DROP TABLE StudiesAccess
DROP TABLE UsersMaster
DROP TABLE StudiesMaster


In the above script [b]ASP .NET[/b] is assigned to only one user... That why the study name "ASP .NET" is not in the result ( As per Mahesh's solution)
>> [b]In second query if the study is assigned to even one user, then it will not come up for rest of the users[/b]
SELECT 

SM.Name AS 'Study Name'
FROM StudiesMaster SM
WHERE SM.StudiesId NOT IN (SELECT StudiesId FROM StudiesAccess)

OUTPUT:
JAVA


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Nerdanalysis, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Allemahesh on: 8/27/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
Dear Rakesh,
As you said that, "in second query if the the study is assigned to even one user, then it will not come up for rest of the users ". Yes, You are right.
I thing it will satisfy your query that says "Which study the user have not access ".
Let me know if you have any other issue.
Happy Coding.

Nerdanalysis, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Nerdanalysis on: 8/27/2013 [Member] Starter | Points: 25

Up
0
Down
Hi Mahesh,

I want to see for each user which study they have access and havent. I want to provide the access to the users for different studies and deny the access to studies which are not of their area.

I did cross join to match all the Usermaster(userid, userName) and StudyName(studyid, studyName)....this gave me all the studies with the usernames. But the problem starts when i join the bridge table eg studyUserMaster(userid, studyid, active) to see is that active or not.

select FullName, StudyName.Studyid, StudyName.StudyName,sums.Active from UserMaster cross join StudyName -- to get all users with all study
inner join studyUserMaster as sums on sums.StudyId = StudyName.StudyId -- to get active = ture or false infront of that row(for that record)
order by FullName

Thanks once again for taking time to help me out.

Kind regards
Rakesh

PS: Thanks Bandi for your time.


Nerdanalysis, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Nerdanalysis on: 8/27/2013 [Member] Starter | Points: 25

Up
0
Down
Chandu you are the star.....Thanks Mahesh as well...

Nerdanalysis, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 8/27/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
You are welcome
However Mahesh too had good design knowledge

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Nerdanalysis, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response