I have an inventory application that needs to support multi-user. As of right now, only one user can access and manage their items. I've gotten a lot of requests to support multiple users so they can all manage the same inventory.
I have a table called user_items that stores item information. To keep it simple, I'll include just the relevant column names.
mysql> select primary_item_id, user_id, item_name from users_item limit 1;
+-----------------+---------+-----------------+
| primary_item_id | user_id | item_name |
+-----------------+---------+-----------------+
| 100 | 4 | Stereo Receiver |
| 101 | 5 | Couch |
+-----------------+---------+-----------------+
I've created a mapping table to map the items to users.
+-------------+-------------+----------------+------------+-----------+
| map_user_id | map_item_id | unique_item_id | item_owner | privilege |
+-------------+-------------+----------------+------------+-----------+
| 4 | 100 | 1 | 1 | NULL |
| 13 | 100 | 1 | 1 | NULL |
| 5 | 101 | 1 | 5 | NULL |
+-------------+-------------+----------------+------------+-----------+
The unique_item_id column is the item_id that's displayed to the users. So item #1 for user #4 is "Stereo Receiver." Item #1 for user #5 would be a couch. The item_owner field doesn't mean much for the time being. I'm not sure if I need it but it's there for now as I play with the schema and code.
Anyways, this works fine, except I need multiple users to track the same item(s). Instead of offering the opportunity to share items AND track their own items, my version of "multi-user" means they have to track the same exact number of items. If user #13 adds a new item, user #4 also has access to said item.
Any suggestions? I think I shot myself in the foot by offering unique ids for each item. It is what it is so now I have to work with what I have.
Best Answer
I'd try creating a group and user_group table. Then change map_user_id to map_group_id and your group all have access to manage that item.
Eg.