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.
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?
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
-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?
I think your design looks good. A query like this:
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.
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?
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.