0

Let me explain, take an inventory system. You have items and you have rooms. If I have a table for items, that means everyone's data gets inserted into the same table. Hence, the primary key (item_id) is auto_incremented by 1. So "John" signs up two weeks later and his first item's id might be 210. How can I go about giving each user their own "id?" Without resorting to creating their own table, which could get messy if I have 1,000 users.

| item_id | item_name | user_id |
|    1    |  sofa     |    1    |
|    2    |  ps3      |    1    |
|    3    |  ipad     |    1    |
|    4    |  laptop   |    2    |

Example URL:

http://domain.com/item/view/1

John would get a URL of http://domain.com/item/view/210 for his first item.

In case this helps someone else, here's what i ended up doing.

Create a mapping table. In my case, "users_items" with item_id and user_id and remove the primary key.

SELECT case when item_id IS NULL then isnull(max(item_id)+1) else max(item_id) +1 end as item_id 
    FROM users_items WHERE user_id = $user+id;
sdot257
  • 10,046
  • 26
  • 88
  • 122
  • 1
    what exactly bothers you in that case when John's first item id might be 210? John would be upset about it? You can always keep field named 'order' which will have its own sequence of numbers for different users, and every item will can be identifed by (order, user_id) pair. Do people in your system work directly with these item_id's? – Anton Sep 25 '11 at 13:03
  • It's funny that I'm being picky somewhat with this but I wanted to make the experience a unique one. Kind of hard to explain. I guess what i wanted to avoid was having the number auto_increment to some large number. Let's say I do end up with 100 users but ONLY 20 are active users. My item_id field grows to let's say 340. What sucks is, I know for sure the first 100 numbers should be cleaned out since the accounts are no longer active. – sdot257 Sep 25 '11 at 13:15
  • im not sure that re-using values that are supposed to be primary keys is somehow fitting into bounds of relational model. Solution for your particular question is simple: make (item_id, user_id) your primary key, remove auto_increment from item_id and write a function or use a subquery to obtain next item_id for specific user when inserting. But i'm still not sure that cleaning out first 100 numbers is necessary in some way, of course if your rows arent extra large and you need to gain some disk space. – Anton Sep 25 '11 at 13:23
  • Because, well, if John and Mary both have sofas, these sofas are still different? Not only by their owner, but they are two different objects. So their item_id must be different. Or John will decide by looking at the database that he and Mary have sofa with the same number, so he's free to use is as if it was his own sofa, Mary wudnt be ok with that. Just kidding, but you get my point. – Anton Sep 25 '11 at 13:25
  • The more I think about it, the more it makes since to leave it as is. I understand EACH item is unique, hence the primary key on `item_id`. This user is trying to implement the same thing. http://stackoverflow.com/questions/5900065/have-more-than-one-autoincrement-per-table – sdot257 Sep 25 '11 at 13:33

2 Answers2

1

Maybe create an additional column item_code which you can populate with something like

SELECT ISNULL(MAX(item_code) + 1) FROM item
WHERE user_id = 2

Considering item_id is a surrogate key it shouldn't matter what value it is, the user has no concern about that, but item code can be the code for the user to display.

Craig
  • 36,306
  • 34
  • 114
  • 197
1

If its only about places where item id is visible to user, why not storing first item_id for each user in some column and then just adding desired ID(from URL, for example) to this number? For example in users table user with id=2 will have 4 as value of this column, and when he opens url /item/view/%X system will add (%x-1) to 4 and get real item_id.

Anton
  • 3,006
  • 3
  • 26
  • 37