-2

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?

1

The UserItems table doesn't need an HasAccess field. If a record exists in this table for a combination of UserId and ItemId, it means the user has access to the item. If you want to revoke access, delete the record.

So I would definately advise against this:

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).

Assigning an item to a user is as simple as adding a record in the UserItems table.

INSERT INTO UserItems (userId, itemId) VALUES (123, 456);

To query, you simply join the tables together. Don't forget to check for active items. Something like:

SELECT * FROM Item
   INNER JOIN UserItems ON Item.Id = UserItems.ItemId
   WHERE UserItems.UserId = 123
      AND Item.IsActive = true;

This will return all active items that are assigned to a user.

If you want to see, for example, all users that have a certain item assigned, turn the query around:

SELECT * FROM User
   INNER JOIN UserItems ON User.Id = UserItems.UserId
   WHERE UserItems.ItemId = 456
      AND User.IsActive = true;

As a sidenote, don't name your tables users_table or items_table. The convention is to use User and Item. For join tables, the convention is to make the second part plural: UserItems.

  • Thank you for your informative answer, this helped me a lot! – yourstruly22 May 27 at 11:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.