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)
ItemGroups Table: (groups for items)
UserGroups Table: (Groups for the users)
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?
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?
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.
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.
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:
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?