Design help please.....

Hello,

I am a newbie and I need to know if I am creating this database properly.

Here is the situation. I have three main components to the database: A user, a report and a profile. Basically it is a security database deciding the access rights to the report defined by the updatable profile.

Here is how I have created the database:

USERS
----
UID [FK]
PWD
First_Name
Last_Name
Profile_Id

PROFILES
----
Profile_Id [PK]
Profile_Desc

REPORTS
----
Report_Id
Report_Desc

PROFILE_REPORTS_LINK
----------
Index [PK]
Profile_Id [PK]
Report_Id [PK]

REPORT_ACCESS
-------
Index
Access_Selections

If I start off with a UID, my app. would need to retrieve the access_selections based upon the current report selected and the profile_id previously defined by which user is logged in. I hope this makes sense... I would like to know if there is a better way of doing this and if I am to choose this way, how do I update the PROFILE_REPORTS_LINK table every time I add a new profile or a new report?

Thanks in advance.
MORI0043Is it 1-to-1 relationship between USERS and PROFILES? Is UID supposed to be a PK, not FK, and Profile_Id in USERS to be FK? And in PROFILE_REPORTS_LINK Index to stay PK while all other the fields listed are supposed to be FK, not PK, right? And REPORT_ACCESS.Index to be FK? Lastly, can a profile belong to more than one user, which means it's not 1-to-1, but rather 0/1-to-many relationship between PROFILES and USERS? If the answer is YES to all the above but the first one, - then I have one more questions, - what is the purpose of PROFILES table? Unless some additional info is present, - you can easily remove PROFILES and put UID in place of Profile_Id into PROFILE_REPORTS_LINK. Of course, this is only if I am not completely off target :)|||-Sorry... Mistype. UID is a PK.
-Yes it is a 1 to 1 relationship between USERS and PROFILES
-Sorry again.. I was going too fast. Yes in the PROFILE_LINK table both IDs are [FK]s
-You can have a profile for 0-many users... Basically it is a group profile where there would be administrators group and general group and so forth
-I don't think that you remove PROFILES because this table defines the profile which can exist for 0-many users.

Am I off on this? Am I doing this right? When I go to create a report or how do I make sure that the PROFILE_REPORT_LINK table is updated for every existing PROFILE and the same with when I add a PROFILE?

Thanks again...|||I think your design looks good. A query like this:

select Report_Access.*
from Report_Access
inner join Profile_Reports....
inner join Profiles...
inner join Users...
where Profile_Reports.Report_ID = @.Active_Report_ID
and Users.UID = @.Active_User_UID

easily gets the info you need.

You could make your schema simpler, but only at the cost of flexibility and ongoing user and report administration.

To update the profile_reports table, create a form that shows all the assigned profiles for a given report, and allows you to add or delete profiles. (I assume not all profiles have access to all reports?)

I doubt that you will find a way to automatically add profile_reports records whenever a new report or profile is added, unless you have pre-established report_access values by default.

blindman|||Thanks,

But what about using a trigger to update the PROFILE_REPORT_LINK table with the updated record id value in a loop adding for each opposite table id. For example: If I were to add a new PROFILE I would add a new take the new Profile_id and add a new record to PROFILE_REPORT_LINK for every existing Report_id in the REPORT table?

I am not quite sure how to do this though? What do you think?

Thanks.|||Sure, but what would the defaults be for the report _access table values?

You are setting up a security system here, so I would advise against automating new report or profile set up on principle unless you have rigidly defined business rules.

If all your profiles have access to all the reports with the same Access_Selections, then you could go with a simlper design.

blindman|||Blindman,

All USERS have access to all of the REPORTS but all with different SELECTIONS. The SELECTIONS are different depending on the Profile_ID. The Adminstrator will have access to add a new PROFILE with new SELECTIONS. The Reports will only be added by the developer in the future. There are three records in REPORTS now, but this will surely be added upon in the future.

Maybe I should force the user to define a SELECTION for each existing REPORT when the user creates a new profile from within my Front End code.

Do you think that this is a better option?

Thanks.|||Sorry, by SELECTIONS I meant to say REPORT_ACCESS|||rdj - it looks like profiles has 1-to-0/many with users table.

I think having a set of sps to handle the entire structure is the way to go:

usp_AddNewProfile
usp_AddNewUser
usp_AddNewReport
...
etc.|||Here is what I am doing now and it seems to work....

CREATE TRIGGER updated_profile on Profiles
FOR INSERT
AS

DECLARE @.Temp_Profile_Id as int
DECLARE @.Temp_Report_Id as int
DECLARE C1 CURSOR FOR
SELECT Report_Id FROM Reports

SET @.Temp_Profile_Id = (Select Profile_Id from Inserted)

OPEN C1

FETCH NEXT FROM C1
INTO @.Temp_Report_Id

WHILE @.@.FETCH_STATUS = 0
BEGIN
INSERT INTO Profile_Report_Link(Profile_Id, Report_Id)
VALUES(@.Temp_Profile_Id, @.Temp_Report_Id)
FETCH NEXT FROM C1
INTO @.Temp_Report_Id
END

CLOSE C1
DEALLOCATE C1]

Now I just have to create Triggers for when I add a Report.|||I like the SPs better than the triggers, again just on the principle of not automating a lot of security routines, but if it works for you then go for it.

blindman|||Thanks for all of the help.