Composite Keys and Unique Record ID's per User
Hey all,
creating an app that multiple users can use to track items. They sign up and begin by adding items, item-locations and item-categories. I'm using NextJS and Drizzle plus Vercel-Postgres.
I currently have tables for User, Item, Category and Location. Each of these tables has a unique primary key and all other tables except for user contained a link to User with user_id and a FK to users.
I'm trying to figure out how to have each user have thier own unique and sequential ID's for the items they create. Currently the database creates a Serial (INT) per item and it's sequential but multiple users won't see sequential IDs for items. In my app, the item ID is part of a page url.
I think a composite key with the item ID and user_id would make sense but wondering in general how people tackle this issue. I'm not really interested in using UUID's though it's not ruled out. Ideally the user has a sense of number of items by looking at the sequential increase in item ID's
Thanks!!
0 Replies