I have created a new application. For this project, I built a login page. In the table storing user's information, I'm looking for one column that will keep user ID. This ID should be unique for each user in the system. While looking around on the internet, I found a few suggestions but each of them had different problems.
In my project, I am using ColdFusion 2016 and Microsoft SQL 2008. Originally I was going to use NEWID() in SQL to generate the key for each user, but there are a few things that I do not like about this solution.
First, the ID is way too long and I wouldn't want my users to have to enter such a long value when they search.
Second, I read that this can affect the performance of the query and slow down my database.
Then I was thinking about using auto-increment id. This solution is too simple for system ID in my opinion and maybe will cause some conflicts in the future.
For example, if the table has to be recreated I'm not sure that the ID's will remain the same. So I'm wondering what is the best practice to create unique ids for the system? Should I use ColdFusion CreateUUID()? Again this is way too long in my opinion.
If anyone knows the best practice or have any examples of how this can be achieved please let me know.
As with most other things IT DEPENDS.
Something that seems like it should be such an easy issue can get quite complex pretty quickly. And INT or even BIGINT is significantly smaller than a GUID, but with only 300k or so records, it's not going to have a huge impact on size, but a GUID as your PRIMARY KEY WILL cause significant fragmentation. That will definitely affect your index and could cause issues with lookups if you need max performance.
If high security is a priority, one of the bad things about using sequential ids is that it makes it incredibly easy to numerate through your users when (not if) a bad actor discovers your id scheme. GUIDs fix this, and the chance of two people having the same GUID, even across multiple systems, is very small.
SQL 2008 does have newsequentialid(), which kinda helps some of the issues with newid(). It will significantly reduce fragmentation, but it will still require a lot more storage than in INT. https://learn.microsoft.com/en-us/sql/t-sql/functions/newsequentialid-transact-sql
Again, this is an issue that will require you to put some thought into, especially how it will impact your system. This is ultimately something you'll have to weigh within your expected system.
https://news.ycombinator.com/item?id=14523523
https://blogs.msdn.microsoft.com/sqlserverfaq/2010/05/27/guid-vs-int-debate/
And you should always pay attention to Ms. Tripp's advice. https://www.sqlskills.com/blogs/kimberly/guids-as-primary-keys-andor-the-clustering-key/
Though Jeff Atwood has a different opinion. As does Joe Celko. https://blog.codinghorror.com/primary-keys-ids-versus-guids/
There are about a bazillion links on Google that will give you multiple sides of this discussion. It really begins to approach a religious debate. Ultimately, I don't think anyone here can tell you what the "Best Practice" is. You'll have to try different methods in your system and see what works best for what you have.