CODEWITHSUNDEEP
mysqlsql
sdot257
sdot257

Reputation: 10366

Giving users unique ids in the form of an auto_increment

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;

Upvotes: 0

Views: 106

Answers (2)

Anton
Anton

Reputation: 3036

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.

Upvotes: 1

Craig
Craig

Reputation: 36836

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.

Upvotes: 1

Related Questions