5 ms·

I disagree with this bit: A User can be considered unique in one context by its email address, or by its social security number Personally, I'm a fan of g

I disagree with this bit: A User can be considered unique in one context by its email address, or by its social security number Personally, I'm a fan of giving everything a random UUID, because it's more flexible. It's random and impossible to guess, it scales well because there's no central bottleneck like with an autoincrement, and it's future proof and flexible. What happens when the user changes the email address? What if the social security number changes, because it was wrong or because it actually changes? What if the original unique identifier wasn't a good choice? What if we decide that the user can have multiple email addresses? Then you may end up having to restructure the entire database, which will be a very annoying thing to do. What if you implement additional rules for what an email is allowed to look like and now the constraint fails for existing users, and this correction needs to be propagated to millions of already existing rows? Real-life personal data is weird and fuzzy. They can violate seemingly sensible rules like being unique, unchanging, or conforming to any rule whatsoever. Best not to let them spread all over the DB and cause trouble later. Instead, you could just have a random ID that doesn't mean anything and therefore can stay fixed forever, and any user-related metadata stays in the user table, where it can be modified as needed. Plus an UUID is a fixed 16 bytes, which is easy and efficient to deal with.

jacques_chester 6y ago
> What if the social security number changes, because it was wrong or because it actually changes? What if the user doesn't have an SSN? What happens if they have one but lawfully refuse to provide it? What happens when you ask for and SSN from a US citizen who is also a European citizen? What happens when your database leaks? In general, relying only on natural keys is a nightmare. Double nightmare if it's PII. Natural keys only work if you are flawlessly omniscient about the domain. And you aren't.
- echelon 6y ago
  I want to print this comment and frame it.
- haspok 6y ago
  How do you look for a person, if not based on his/her SSN? SSN alone is not sufficient, of course. But it _is_ definitely part of the natural key that you use _implicitly_ ANYWAY, whether recognizing it or not. > Natural keys only work if you are flawlessly omniscient about the domain I would call that BS. Nobody is "flawlessly omniscient" about anything, not even in mathematics, yet we design and build systems that work. On the other hand, yes, it is a very good requirement to have someone on the team during database modeling who understands the domain model thoroughly. No UUID columns will save you from that.
  - earthboundkid 6y ago
    > How do you look for a person, if not based on his/her SSN? These are different concepts. "Looking for a person" means search. You can look for people lots of ways. In medicine for example, they often look for first name + last name + birthday. Is that a unique ID? No, but it's close enough for search usually. On the other hand, for any kind of indexing or foreign keys, you want an actually unique, immutable ID, which means you don't want a natural key, you want an artificial ID created just for your database.
  - jacques_chester 6y ago
    > I would call that BS. Nobody is "flawlessly omniscient" about anything, not even in mathematics, yet we design and build systems that work. And such systems typically use synthetic keys to completely dodge the kind of problems I outlined. The problems with natural keys are that you, the programmer, don't know as much as you think you know. You muddle the problem domain with the solution domain and when something comes along in the problem domain you didn't think of, it's now much harder to fix. > On the other hand, yes, it is a very good requirement to have someone on the team during database modeling who understands the domain model thoroughly. No UUID columns will save you from that. They save you from having to work out how to store a record when you chose SSN as primary key and discover that, uh, no you can't do that. The same goes for purchase order numbers, waybill numbers, student IDs, payroll IDs, bank account numbers, license plates ... anything whatsoever that is visible in the problem domain will or will have exceptions you didn't know about, didn't foresee and for which legislation or policy allows no exception for not using a UUID column.
- akra 6y ago
  In my experience from what I've seen there are ways to use natural keys and handle domain changes - I've seen some systems like this work quite successfully. The cost to using synthetic IDs (auto-increment, UUIDs) is a lack of reproducibility and slower importing of data especially across multiple tables/entities limiting scalability. This can be very problematic for certain classes of applications I've seen, but not most. While I agree with your comment for many classes of apps as always there is no general "silver bullet" answer - it depends on your problem space. Some cases I've seen in previous roles where some natural key is required include reconciling third party data sources, or processing events from a topic or stream and being able to replay the event log, etc knowing that a different ID may break other third parties you don't control since they've already imported the ID. Being able to replay your data sets from scratch and get exactly the same data can have some real advantages for some apps. Of course you need to be aware of the domain and assume that the key can change over time and have strategies to deal with that (e.g. entity version tables bound by time, data migration to add key attributes, etc etc) and the data structures/processes needs to be designed for this. There's more work in it for sure to get right - it shouldn't be the default. But in some cases I've seen it work really well which frankly surprised me at the time.
- cratermoon 6y ago
  a Social Security number is far from unique. https://www.computerworld.com/article/2552992/not-so-unique.html https://www.computerworld.com/article/2552992/not-so-unique.... Identity crisis: how Social Security numbers became our insecure national ID https://www.theverge.com/2012/9/26/3384416/social-security-numbers-national-ID-identity-theft-nstic https://www.theverge.com/2012/9/26/3384416/social-security-n... Back in the 1990s I was trying to convince my colleagues not to use SSNs as unique IDs. I've since noted that quite a few organizations that had gravitated to SSNs as IDs had to go through expensive and chaotic migrations to real unique IDs.
santiagobasulto 6y ago
I'm from Argentina and we have something similar to a SSN, we call it DNI (Documento Nacional de Identidad). You wouldn't believe how many duplicate DNIs we have, it's crazy. So yes, I agree with you, I always use a random UUID as ID.
yukinon 6y ago
> Personally, I'm a fan of giving everything a random UUID, because it's more flexible Unless of course, you're using a relational database like OP and incur a performance hit from using a UUID as your primary key. Additionally, they're not sortable like autoinc id's. I've always wanted to try out Twitter's Snowflake ID [1] algorithm to get around this, but it requires requires using something like Zookeeper. I've seen some people on the net talk about UUIDv6 being sortable by time, but there's still the potential performance hit of index size. While I'm bringing this up I've never actually tested how slow PK UUIDv1's are and at what magnitude their performance hit becomes noticeable. [1] https://blog.twitter.com/engineering/en_us/a/2010/announcing-snowflake.html https://blog.twitter.com/engineering/en_us/a/2010/announcing...
- dale_glass 6y ago
  Things like Twitter are special. I'm talking more about a generally sensible way of doing things, which one may need to deviate from in special circumstances. Why would you want to sort by ID? Sort by something sensible, like the signup date instead. An autoincrement may stop corresponding to time if for instance at some point a database has an external dataset imported into it. IMO, using an ID for anything other than an opaque identifier is asking for trouble.
  - simonw 6y ago
    I like IDs I can read out over a call, or recognize when I spot them in a log file. The few times I've used UUIDs for IDs I've later regretted it.
    - mosdl 6y ago
      Add a prefix to the if then.
    - orf 6y ago
      > or recognize when I spot them in a log file “14632” in a log file could be anything, whereas a UUID is way more explicit and searchable.
haspok 6y ago
If you don't use natural keys, how do you know if you have duplicate records in your database? How do you UPSERT? A UUID won't help you here (unless created as a hash of natural data). > Then you may end up having to restructure the entire database, which will be a very annoying thing to do. Well, you might call it annoying, I may call it a good upfront design requirement. Yes, you have sometimes spend time on stuff that doesn't immediately pay off, but it will pay in the longer run. If you have a problem with your natural keys, this is mostly due to missing or misunderstanding your requirements, or an incomplete domain design. You can of course just sweep stuff under the rug, and pretend it's not there, but it's not a maintainable strategy. (Unless you are a consultant of course, with a fixed term assignment on a project - in which case it is an excellent strategy for yourself...)
jillesvangurp 6y ago
It's a common mistake indeed. People change company, which means their email will change. Or they are affiliated with multiple companies. Or they simply sign up with their personal email. If you have more user objects than people in your system, you are doing something wrong. This kind of digital schizophrenia is unfortunately quite common (looking at you Slack). Having a notion of multiple verified ways to contact the user is one step up over this. Not lot of websites actually do this. Linkedin is one of them; because they recognized early that they needed to track their users as they changed their professional affiliation. I tend to use list fields for things like phone numbers and email addresses. This makes it clear that I understand that users might legitimately have several of those and that they change over time. I always liked what Keybase did with encouraging its users to have multiple verified third party identities (the more the better). Using a social security number is a mistake for a different reason: it's a sensitive bit of information that can be abused if it falls in the wrong hands. You should protect it like you would protect a credit card number and generally not store it unless you absolutely have a valid business reason to. Also, it's country specific which sort of makes it an obstacle if you need to serve an international market (or have the ambition to do so later).
- imtringued 6y ago
  >Or they simply sign up with their personal email. If you have more user objects than people in your system, you are doing something wrong. I have three discord accounts and there is no way to avoid that.
  - jillesvangurp 6y ago
    Github manages fine. I've used my account on numerous projects. Github simply recognizes that you are you and that your account and your company's data are two things.

jacques_chester 6y ago

> What if the social security number changes, because it was wrong or because it actually changes? What if the user doesn't have an SSN? What happens if they have one but lawfully refuse to provide it? What happens when you ask for and SSN from a US citizen who is also a European citizen? What happens when your database leaks? In general, relying only on natural keys is a nightmare. Double nightmare if it's PII. Natural keys only work if you are flawlessly omniscient about the domain. And you aren't.

echelon 6y ago
I want to print this comment and frame it.
haspok 6y ago
How do you look for a person, if not based on his/her SSN? SSN alone is not sufficient, of course. But it _is_ definitely part of the natural key that you use _implicitly_ ANYWAY, whether recognizing it or not. > Natural keys only work if you are flawlessly omniscient about the domain I would call that BS. Nobody is "flawlessly omniscient" about anything, not even in mathematics, yet we design and build systems that work. On the other hand, yes, it is a very good requirement to have someone on the team during database modeling who understands the domain model thoroughly. No UUID columns will save you from that.
- earthboundkid 6y ago
  > How do you look for a person, if not based on his/her SSN? These are different concepts. "Looking for a person" means search. You can look for people lots of ways. In medicine for example, they often look for first name + last name + birthday. Is that a unique ID? No, but it's close enough for search usually. On the other hand, for any kind of indexing or foreign keys, you want an actually unique, immutable ID, which means you don't want a natural key, you want an artificial ID created just for your database.
- jacques_chester 6y ago
  > I would call that BS. Nobody is "flawlessly omniscient" about anything, not even in mathematics, yet we design and build systems that work. And such systems typically use synthetic keys to completely dodge the kind of problems I outlined. The problems with natural keys are that you, the programmer, don't know as much as you think you know. You muddle the problem domain with the solution domain and when something comes along in the problem domain you didn't think of, it's now much harder to fix. > On the other hand, yes, it is a very good requirement to have someone on the team during database modeling who understands the domain model thoroughly. No UUID columns will save you from that. They save you from having to work out how to store a record when you chose SSN as primary key and discover that, uh, no you can't do that. The same goes for purchase order numbers, waybill numbers, student IDs, payroll IDs, bank account numbers, license plates ... anything whatsoever that is visible in the problem domain will or will have exceptions you didn't know about, didn't foresee and for which legislation or policy allows no exception for not using a UUID column.
akra 6y ago
In my experience from what I've seen there are ways to use natural keys and handle domain changes - I've seen some systems like this work quite successfully. The cost to using synthetic IDs (auto-increment, UUIDs) is a lack of reproducibility and slower importing of data especially across multiple tables/entities limiting scalability. This can be very problematic for certain classes of applications I've seen, but not most. While I agree with your comment for many classes of apps as always there is no general "silver bullet" answer - it depends on your problem space. Some cases I've seen in previous roles where some natural key is required include reconciling third party data sources, or processing events from a topic or stream and being able to replay the event log, etc knowing that a different ID may break other third parties you don't control since they've already imported the ID. Being able to replay your data sets from scratch and get exactly the same data can have some real advantages for some apps. Of course you need to be aware of the domain and assume that the key can change over time and have strategies to deal with that (e.g. entity version tables bound by time, data migration to add key attributes, etc etc) and the data structures/processes needs to be designed for this. There's more work in it for sure to get right - it shouldn't be the default. But in some cases I've seen it work really well which frankly surprised me at the time.
cratermoon 6y ago
a Social Security number is far from unique. https://www.computerworld.com/article/2552992/not-so-unique.html https://www.computerworld.com/article/2552992/not-so-unique.... Identity crisis: how Social Security numbers became our insecure national ID https://www.theverge.com/2012/9/26/3384416/social-security-numbers-national-ID-identity-theft-nstic https://www.theverge.com/2012/9/26/3384416/social-security-n... Back in the 1990s I was trying to convince my colleagues not to use SSNs as unique IDs. I've since noted that quite a few organizations that had gravitated to SSNs as IDs had to go through expensive and chaotic migrations to real unique IDs.

santiagobasulto 6y ago

I'm from Argentina and we have something similar to a SSN, we call it DNI (Documento Nacional de Identidad). You wouldn't believe how many duplicate DNIs we have, it's crazy. So yes, I agree with you, I always use a random UUID as ID.

yukinon 6y ago

> Personally, I'm a fan of giving everything a random UUID, because it's more flexible Unless of course, you're using a relational database like OP and incur a performance hit from using a UUID as your primary key. Additionally, they're not sortable like autoinc id's. I've always wanted to try out Twitter's Snowflake ID [1] algorithm to get around this, but it requires requires using something like Zookeeper. I've seen some people on the net talk about UUIDv6 being sortable by time, but there's still the potential performance hit of index size. While I'm bringing this up I've never actually tested how slow PK UUIDv1's are and at what magnitude their performance hit becomes noticeable. [1] https://blog.twitter.com/engineering/en_us/a/2010/announcing-snowflake.html https://blog.twitter.com/engineering/en_us/a/2010/announcing...

dale_glass 6y ago
Things like Twitter are special. I'm talking more about a generally sensible way of doing things, which one may need to deviate from in special circumstances. Why would you want to sort by ID? Sort by something sensible, like the signup date instead. An autoincrement may stop corresponding to time if for instance at some point a database has an external dataset imported into it. IMO, using an ID for anything other than an opaque identifier is asking for trouble.
- simonw 6y ago
  I like IDs I can read out over a call, or recognize when I spot them in a log file. The few times I've used UUIDs for IDs I've later regretted it.
  - mosdl 6y ago
    Add a prefix to the if then.
  - orf 6y ago
    > or recognize when I spot them in a log file “14632” in a log file could be anything, whereas a UUID is way more explicit and searchable.

haspok 6y ago

If you don't use natural keys, how do you know if you have duplicate records in your database? How do you UPSERT? A UUID won't help you here (unless created as a hash of natural data). > Then you may end up having to restructure the entire database, which will be a very annoying thing to do. Well, you might call it annoying, I may call it a good upfront design requirement. Yes, you have sometimes spend time on stuff that doesn't immediately pay off, but it will pay in the longer run. If you have a problem with your natural keys, this is mostly due to missing or misunderstanding your requirements, or an incomplete domain design. You can of course just sweep stuff under the rug, and pretend it's not there, but it's not a maintainable strategy. (Unless you are a consultant of course, with a fixed term assignment on a project - in which case it is an excellent strategy for yourself...)

jillesvangurp 6y ago

It's a common mistake indeed. People change company, which means their email will change. Or they are affiliated with multiple companies. Or they simply sign up with their personal email. If you have more user objects than people in your system, you are doing something wrong. This kind of digital schizophrenia is unfortunately quite common (looking at you Slack). Having a notion of multiple verified ways to contact the user is one step up over this. Not lot of websites actually do this. Linkedin is one of them; because they recognized early that they needed to track their users as they changed their professional affiliation. I tend to use list fields for things like phone numbers and email addresses. This makes it clear that I understand that users might legitimately have several of those and that they change over time. I always liked what Keybase did with encouraging its users to have multiple verified third party identities (the more the better). Using a social security number is a mistake for a different reason: it's a sensitive bit of information that can be abused if it falls in the wrong hands. You should protect it like you would protect a credit card number and generally not store it unless you absolutely have a valid business reason to. Also, it's country specific which sort of makes it an obstacle if you need to serve an international market (or have the ambition to do so later).

imtringued 6y ago
>Or they simply sign up with their personal email. If you have more user objects than people in your system, you are doing something wrong. I have three discord accounts and there is no way to avoid that.
- jillesvangurp 6y ago
  Github manages fine. I've used my account on numerous projects. Github simply recognizes that you are you and that your account and your company's data are two things.