I am working on an Online Grocery Store type of website in which I have one order table to store the order details. Here I am having confusion that whether I should have an OrderId (primary key) and separate user-facing OrderNumber or I can only have an OrderNumber as my primary key as the OrderNumber is going to be unique?
Also, I was considering OrderId to be a UUID type. So can I do the reverse of having only OrderId and considering it as User-Facing Id as well?
Best Answer
You can do either or both, it doesn't really matter much, other than for some minor performance tuning things as Akina points out.
But yes you're allowed to have two fields that are unique, but only one primary key can exist, so you'd have to choose if that will be your OrderId or your OrderNumber, or a composite key that includes both fields.
Typically if the OrderNumber is a numeric data type field and unique then most people would just use this as the primary key, there would be no need for an OrderId (as this would be a column with a redundant purpose). Otherwise if the OrderNumber was something more complex in format, e.g. "124-72-2101-yum" then having a separate field for OrderId as your primary key would probably be a better choice.
The only other thing to consider, is I see you mentioned using UUID for OrderId. If you were to make a UUID field as your primary key (and keep the clustered index on it) there would be performance degradation when trying to JOIN your other tables to it (depending on how big your tables were). You theoretically could make it the primary key without making it the clustered index, and again if your OrderNumber field is of a numeric type then you can make that the clustered index for performance reasons, but at that point you might as well just use OrderNumber as your primary key too and get rid of the OrderId field.
An INT or BIGINT column provide just as much benefits as UUID in regards to being a primary key, but with much better performance. The only benefit UUID carries over them is somewhat global uniqueness which can be useful in certain contexts (like a mobile app that needs to function offline and be consolidated with the main database later on). Though there are ways to achieve this only using INT and BIGINT data types as your primary keys too, it just requires a little more implementation work. But even then my personal preference would be to use INT or BIGINT and put in the little bit of extra work upfront, to not worry about continually trying to optimize performance of my queries later on. And even UUID has a small chance for collisions / producing the same ID twice too (across systems), so it's not perfect.