Results 1 to 6 of 6

Thread: Design Help

  1. #1
    Join Date
    Oct 2004
    Posts
    3

    Design Help

    I'm looking to design a database for companies to view a catalog of items. Here is what I have so far.

    Item Table: (items are grouped)
    Id
    Name
    GroupID
    ItemGroups Table: (groups for items)
    ID
    Name
    Users Table:
    ID
    Name
    GroupID
    UserGroups Table: (Groups for the users)
    ID
    Name

    My problem is how to delegate permission for the user to view items. There will be many items in the database, as well as many users. I want to be able to assign the user to a group and the group access to an item (or maybe group of items - haven't decided that yet) or assign the user direct access to the item - whether or not they are in the group that has access.

    What would be the best way for doing this? Any ideas?

  2. #2
    Join Date
    Aug 2004
    Posts
    330
    In other words, you want a specific user to only be able to see certain rows on a table? And the rows that they see are determined by the value of the GroupID column?

  3. #3
    Join Date
    Oct 2004
    Posts
    3
    A user will be part of a User Group. The user group should have access to an item, so anyone in the user group will see it. However, for those people not in a user group - they should be able to be given access without being in the group. Does that make any sense?

  4. #4
    Join Date
    Aug 2004
    Posts
    330
    Most operating systems allow users to be attached to "groups". If you set it up this way and the DBMS uses OS authentication, then you can create VIEWS of your data that you want each respective group to see. Then you can grant access to the appropriate view for each user group.

  5. #5
    Join Date
    Oct 2004
    Posts
    3
    Well, I'm not using OS authentication as I'm using php to code the frontend, but I think I got an idea for access levels.

    Ok, now what I have is an item database and each item can be part of a specific group.
    Items
    -------------
    ID
    GroupID

    There is one group that is won't be entered as an id into the items table (1 - Featured Items). This has its own table:
    FeaturedItems
    --------------------
    ID
    UserGroupID
    UserID
    ItemID

    The User table has a access field that's really just a switch that allows them access to either all groups, or just the Feature Items group.

    If the User belongs to a UserGroup, then for the Featured Items area they see the Items that correspond to their UserGroupID as shown in the FeaturedItems table - however, if the user doesn't belong to a group - the see the items that correspond to their UserID in the FeaturedItems table.

    That will hopefully work. Any comments or ideas for refinement?

  6. #6
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    You might try the following:

    User(UserID, UserName)
    Group(GroupID, GroupName)
    Item(ItemID, ItemName)

    UserGroup(UserID, GroupID)
    GroupItem(GroupID, ItemID)

Posting Permissions

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