I have two tables in a database: A users_table and a items_table. I need a way to be able to define which items each user gets access to. I thought to create a third table containing: ‘user_id’, ‘item_id’, and ‘has_access’ columns.
The problem I am having is that the users_table as well as the items_table have ‘active’ columns that can change. Users can only get access to items that are active. Items can only be assigned to users that are active. Thus when a user/item is set to inactive all of the records in the third table that refer to that user/item should be set to inactive. Same goes for when a user/item gets deleted in their respective tables.
When a new user gets added they should automatically get assigned all of the items (has_access = false). When a new item gets added to the items_table, all users should automatically get assigned the new item (again, with column has_access set to false).
The reason for this is I want to simply be able to make a SQl query such as SELECT * FROM users-items_table WHERE user_id=’someId’, and use the information returned to build up a profile for the user containing the items they have access to.
How would I set up the relationship between the tables to achieve this?