Need advice for table design for multi-user access

Question

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.

Answer 1

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.

+------------------+------------------+
| primary_group_id | group_name       |
+------------------+------------------+
|             1    | Edit Stereos     |
+------------------+------------------+

+---------------+---------------+---------------+
| user_group_id | user_id       | group_id      |
+---------------+---------------+---------------+
|          1    | 1             | 1             |
|          2    | 4             | 1             |
+---------------+---------------+---------------+

+--------------+-------------+----------------+------------+-----------+
| map_group_id | map_item_id | unique_item_id | item_owner | privilege |
+--------------+-------------+----------------+------------+-----------+
|            1 |       100   |              1 |          1 |      NULL |