Results 1 to 4 of 4

Really simple database

  1. #1
    aveit is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Mar 2011
    Posts
    3

    Really simple database

    Hi



    Sorry people this is probably really simple but i want to set up an Access database to keep on track of peoples permissions to systems.

    Basically one user will have access to a number of different systems (up to 4) and would then have different levels of access to these systems (levels themselves could change).

    So User A could have access to 4 systems. System 1 he has 3 different permissions, system 2 he has 4, system 3 he has 1 and system 4 he has 6.

    My question is how would it be best to build this in MS Access. Im ok at running queries etc but i am struggling finding the best way to setup tables.

    Many thanks

  2. #2
    ketbdnetbp is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Midwest
    Posts
    254

    Just a quick thought...

    Aveit - You may want to consider something like this...

    tblUser 'List of all users
    UserID (PK)
    UName

    tblSytems 'List of all systems
    SystemID (PK)
    Description

    tblPermissions 'List of all permission types
    PermissionID (PK)
    Type

    tblUserAccessPermissionDetails
    UAPDID (PK)
    UserID (FK)
    SystemID (FK)
    PermissionID (FK)

    Relationships -
    tblUser (One) to tblUserAccessPermissionDetails (Many)
    tblSystems (One) to tblUserAccessPermissionDetails (Many)
    tblPermissions (One) to tblUserAccessPermissionDetails (Many)

    Hope this helps!

  3. #3
    aveit is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Mar 2011
    Posts
    3
    Hi

    Many thanks for your help.

    The permissions for each of the systems are completely different to each other-would this still be the best?

    The outcome of this database is a report will be generated based on user. The report will then need to display 'system 1' then give the permissions, then 'system 2' the give the permissions etc.

    Many thanks

  4. #4
    ketbdnetbp is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Midwest
    Posts
    254

    Question???

    Aveit - In your last post -

    Quote: The permissions for each of the systems are completely different to each other.

    Can you elaborate a bit further or provide some examples, etc.

    If the permissions are specific to each system, you may need to add a (FK) SystemID to the tblPermissions and then remove the (FK) SystemID from tblUserAccessPermissionDetails. Then add a one to many relationship between the tblSystems and tblPermissions.

    Quote: The outcome of this database is a report will be generated based on user. The report will then need to display 'system 1' then give the permissions, then 'system 2' the give the permissions etc.

    If you base the report on a query of the tblUserAccessPermissionDetails, I think you'll have all the needed data. You would just have to group the data accordingly, ie group by UserID first, then by SystemID.

    However, if I am missing something, let me know.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Simple Holiday bookings database?
    By darkan99el in forum Access
    Replies: 2
    Last Post: 03-11-2011, 11:25 AM
  2. Simple database problems
    By CHowell2005 in forum Access
    Replies: 6
    Last Post: 07-22-2010, 04:20 PM
  3. Simple Database (atleast I *think* it should be)
    By CloudFuel in forum Database Design
    Replies: 6
    Last Post: 06-28-2010, 10:40 AM
  4. Simple database required
    By Steve in forum Database Design
    Replies: 2
    Last Post: 03-09-2010, 06:34 PM
  5. Looking for quick opinion on simple database
    By Zoran in forum Database Design
    Replies: 6
    Last Post: 01-31-2010, 05:04 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums