Saturday, 25 April 2020

Guid or Int for database keys?

Should I use Guid's or Integers as primary keys in my database?
Well, it depends. Here's some simple thumb rules.

Use Guid when...

  • You have a system with a lot of data spanning multiple servers and databases. (Guids make it easier to merge data. You can partition data across servers).
  • You need to synchronise data across different systems (both ways).
  • You need a high level of security. (Less chance of someone getting access to something they shouldn't have by tweaking key parameters, doing brute force attacks etc.)
  • You are using some rally high velocity multi source threaded non-transactional database. (With Guids you can be sure entries are saved as unique)
  • You have high requirements around system quality (e.g. reliability and stability). With integers there's a greater chance of bugs. For example: "Ops, that customer Id was actually a product Id". Some developer using our API got the data mixed up and created data inconsistencies.   
  • You need to create an Id but not persist it until later.  

Use Int when...

  • You have small system and you want to be able to easily change keys during development. 
  • Your database server has resource limitations etc. (Guids takes up 4 times more space). 
  • You are just hacking together some hobby project and you don't want to spend time on making sure Guids are used in an optimal way.
  • You want to spend less time on developing a user-friendly system. You can display the key directly to users. (With Guids you can't expect them to remember or copy/paste Guids. With integers you get shorter Urls etc). 
  • You have extreme performance requirements (and you don't really need Guids). 

No comments:

Post a comment

Spammers will be hunted down and punished.