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!!
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!!
