Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2003
    Posts
    10

    Unhappy Unanswered: 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.
    MORI0043

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Is 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

  3. #3
    Join Date
    Sep 2003
    Posts
    10
    -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...

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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

  5. #5
    Join Date
    Sep 2003
    Posts
    10
    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.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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

  7. #7
    Join Date
    Sep 2003
    Posts
    10
    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.

  8. #8
    Join Date
    Sep 2003
    Posts
    10

    Update on previous message

    Sorry, by SELECTIONS I meant to say REPORT_ACCESS

  9. #9
    Join Date
    Sep 2003
    Posts
    522
    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.

  10. #10
    Join Date
    Sep 2003
    Posts
    10
    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.

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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

  12. #12
    Join Date
    Sep 2003
    Posts
    10

    THANKS

    Thanks for all of the help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •