C#•2y ago

❔ Best way to create unique order reference numbers?

First of all, do I do it at the DB level (identity field?) or the application level? GUID? hashes? What is the most common approach to ensure that they are unique and don't give out too much information?

38 Replies

Denis•2y ago

Database level, as it is also responsible for ensuring the integrity of unique primary keys I recommend using a guid

Pobiega•2y ago

DB if possible, and most DBs can do both guids or sequences

linqisniceOP•2y ago

 public class ReservationConfiguration : IEntityTypeConfiguration<Reservation>
    {
        public void Configure(EntityTypeBuilder<Reservation> builder)
        {
            builder.Property(b => b.ReferenceNumber)
                  .IsRequired()
                  .HasDefaultValueSql("NEWID()");

            builder.HasIndex(b => b.ReferenceNumber)
                   .IsUnique();
        }
    }

 public class ReservationConfiguration : IEntityTypeConfiguration<Reservation>
    {
        public void Configure(EntityTypeBuilder<Reservation> builder)
        {
            builder.Property(b => b.ReferenceNumber)
                  .IsRequired()
                  .HasDefaultValueSql("NEWID()");

            builder.HasIndex(b => b.ReferenceNumber)
                   .IsUnique();
        }
    }

like this?

Pobiega•2y ago

depends on your database, but with for example SqlServer you don't need that configuration (assuming your reference is your primary key) you jsut set it to be a Guid type primary key and ya done

linqisniceOP•2y ago

sqlserver yes, and the primary key is a separate id in this case. but maybe it shouldnt be

Jimmacle•2y ago

there are things like this to obfuscate integer IDs, haven't tried it myself yet https://hashids.org/

Hashids

Generate short obfuscated strings from integers. Use in url shorteners or as unique ids.

linqisniceOP•2y ago

@Pobiegaso you think the PK should be the reference number?

Pobiega•2y ago

Can be useful, if you want to avoid enumerable IDs I dont know enough about your domain to say either way but its unusual to need more than one unique identifier 🙂

linqisniceOP•2y ago

you're right, with a resrevation it makes no sense for the order number not to be the PK 😛

Pobiega•2y ago

A reservation either exists or it doesnt, so yeah, seems about right. Now, it seems reasonable that you will have a url somewhere that contains this value api/reservations/{id} or similar

linqisniceOP•2y ago

yep

Pobiega•2y ago

if its just a plain int, people will try to enumerate it, 100% so a guid, hashid or snowflake might be better

linqisniceOP•2y ago

do i want to let SQL or ef core generate the GUID?

Pobiega•2y ago

doesnt really matter, but usually you let the DB handle it if you can

linqisniceOP•2y ago

@Pobiegagotcha, thx

Pobiega•2y ago

you want to avoid doing it in application code, but EF can be trusted, as can the DB

linqisniceOP•2y ago

but i've never seen a guid reference number before is that really common? or order number

Jimmacle•2y ago

guids aren't particularly user friendly imo

linqisniceOP•2y ago

exactly lol

Jimmacle•2y ago

imagine if they were on the phone with support and had to read out a GUID

linqisniceOP•2y ago

that's why i wanted to know what the most common approach is i know guids basically ensure uniqueness but they aren't user friendly like you say

Pobiega•2y ago

You could do a youtube-style id hash

linqisniceOP•2y ago

I might have to do some unique convention for my own application i guess, maybe there needs to be actual identifiers in hte order/reference number for staff to quickly identify the type of reservation instead of just random characters

Pobiega•2y ago

Sure, if you need it. You might want to look at something like snowflake ids, they do something similar but with time random googled example: https://github.com/RobThree/IdGen

Pobiega•2y ago

linqisniceOP•2y ago

@Pobiegainteresting, ill check it out

Pobiega•2y ago

and if its just an ID you want, but wiht parts of it being human readable, thats doable too volvo uses a fixed position ID system for their cars, for example 🙂 where the first 3 indicate the model, the first 12 contain engine and salesversion etc

linqisniceOP•2y ago

@Pobiegathat'll generate the id on teh applicaiton level though?

Pobiega•2y ago

yep, it would since now ID generation is non-trivial

linqisniceOP•2y ago

the idgen above that is will it ensure uniqueness?

Pobiega•2y ago

yep discord and twitter both use something similar to that

linqisniceOP•2y ago

I'll use that for now and try to create my own convention later if needed. Not sure if there needs to be actual information in the reference number just seems like a "good to have" but not necessary by any means yeah well if it's good enough for them it's good enough for me lol haha

Pobiega•2y ago

this sounds like something a BA or product owner should have decided before it ended up on your desk 🙂

linqisniceOP•2y ago

probably lol, i have to make a lot of decisions myself since im the only backend developer lol

Jimmacle•2y ago

relatable (am full stack + random devops/IT things)

Pobiega•2y ago

like, if they wanted partially human readable IDs, thats not something you randomly come up with 😄 thats decided ahead of time if its "just make some unique ids" you can do whatever you feel like I like both the snowflake and the hashid ways tbh

linqisniceOP•2y ago

yeah so I'll go with that since that saves me time and he didnt specify otherwise

Accord•2y ago

Was this issue resolved? If so, run /close - otherwise I will mark this as stale and this post will be archived until there is new activity.

Gaming

Programming

❔ Best way to create unique order reference numbers?