Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
I guess you know where exactly you went wrong. Now where do you need a suggestion. Could you be more specefic about the problem you are facing in the design. –  azzaxp Jul 31 '13 at 5:15
    
Tracking the same items a particular user has. I believe using groups as suggested below is one option. I'm going to run with it for the time being. –  luckytaxi Jul 31 '13 at 14:15
add comment

1 Answer 1

up vote 3 down vote accepted

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 |
share|improve this answer
    
Well darn, I think I'll go this route. Makes sense. –  luckytaxi Jul 31 '13 at 14:09
    
I think you meant for map_group_id to be 1 in the last table, no? –  luckytaxi Jul 31 '13 at 14:17
    
Yes, I only changed the column name and omitted to changed the data. It should be a foreign key to the group table so value should be 1 (changed from being a foreign key to user table) –  sa555 Jul 31 '13 at 23:43
    
Np, thanks. Just wanted to make sure. –  luckytaxi Aug 1 '13 at 13:23
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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