this post was submitted on 28 Jul 2024
20 points (100.0% liked)

Learn Programming

1622 readers
1 users here now

Posting Etiquette

  1. Ask the main part of your question in the title. This should be concise but informative.

  2. Provide everything up front. Don't make people fish for more details in the comments. Provide background information and examples.

  3. Be present for follow up questions. Don't ask for help and run away. Stick around to answer questions and provide more details.

  4. Ask about the problem you're trying to solve. Don't focus too much on debugging your exact solution, as you may be going down the wrong path. Include as much information as you can about what you ultimately are trying to achieve. See more on this here: https://xyproblem.info/

Icon base by Delapouite under CC BY 3.0 with modifications to add a gradient

founded 1 year ago
MODERATORS
Ategon@programming.dev
JaumeI@programming.dev
bugsmith@programming.dev
20
Making a database identifier unique per user? (programming.dev)
submitted 1 month ago by Cyno@programming.dev to c/learn_programming@programming.dev
18 comments fedilink hide all child comments
 

Let's say I am making an app that has table Category and table User. Each user has their own set of categories they created for themselves. Category has its own Id identity that is auto-incremented in an sqlite db.

Now I was thinking, since this is the ID that users will be seeing in their url when editing a category for example, shouldn't it be an ID specific only to them? If the user makes 5 categories they should see IDs from 1 to 5, not start with 14223 or whichever was the next internal ID in the database. After all when querying the data I will only be showing them their own categories so I will always be filtering on UserId anyway.

So let's say I add a new column called "UserSpecificCategoryId" or something like that - how do I make sure it is autogenerated in a safe way and stays unique per user? Do I have to do it manually in the code (which sounds annoying), use some sort of db trigger (we hate triggers, right?) or is this something I shouldn't even be bothering with in the first place?

you are viewing a single comment's thread
view the rest of the comments
[โ€“] Hansie211@lemmy.world 5 points 1 month ago (1 children)

Your description of the problem makes sense. If you think improving the user experience in this way is worthwhile, then go for it. Just remember, most users don't pay attention to the address bar.

For implementation, I suggest creating a new table, something like 'tb_user_category_index,' with userId as a foreign key and an integer that increments each time it's used. Automating this process in the database would be ideal: set it up once and let it run on its own, keeping things simple.

However, don't confuse this cosmetic index with a primary key. The primary key should only serve as a unique identifier for a record and hold no other information.

[โ€“] Cyno@programming.dev 1 points 1 month ago

By automating it you mean something a store procedure that returns the ID and increments the count at the same time or is there a more sophisticated way of doing it?