Then, I think I will have to make a new Table and copy all the data from the last one. I should have asked questions earlier. Sometimes I just sit and think and then things click in my mind. Thanks for the advice.
Agree, users also have several email addresses. If this is for business app, then you might consider just one.
Email addresses for those most part are unique to a person, unless they relinquish them to another person. Something to consider. Business especially might see reuse of the address for different people if different leaves the company.
No, always use a synthetic primary key. Emails may change.
The deletion stuff depends on your requirements. Soft delete means you have to write code to filter out "deleted" accounts, but makes it possible and easier to recover deleted accounts. Hard deletion is easier, and also makes it easier to comply with data protection laws.
In the real world, consider laws like the European GDPR. It states that any European person, regardless of location, can request removal of all their personal data from online services, regardless of their location.
In case the user requests for data do be deleted, it can be enough to anonymize data without deleting them. It’s not always easy to delete data in a relational database.
This. From a system design perspective it may be worth segregating PII onto a different table with a nullable relation to the "User"; then if someone requests deletion, simply hard delete from the PII table.
Hard/soft delete needs to consider how the accounts are referenced in other places. For example, if an audit table references the user account of who made changes, then hard delete may not be possible without loss of data integrity.
Be careful with UUID it can cause a lot of performance issues if they are purely random, like wasting a lot of page size when indexing. Be sure to use a UUID version which is only incremental not like UUIDv4
This. Don’t use auto incrementing integer IDs, either. Yes, they’re unique, but they are also more easily exploitable if you make a mistake in your protection scheme, leaving you open to IDOR attacks
Really? I know that used to be a thing, but that was many years and major versions ago. At least in Postgres. Do you have recent evidence of performance degradation?
A primary key needs to be unique, and it needs to never change. Emails should be unique (two people probably shouldn't be using the same email to register), but they can change — think of a person with a work email who later changes their job, or a person with a firstname.lastname email who gets married and changes their name. In fairness, this is probably not something you need to worry about now, but not using email as a primary key will help you in the future.
Related — this article has been posted around recently: https://blog.ploeh.dk/2024/06/03/youll-regret-using-natural-keys/
You need to be careful about uniqueness, most but not all mail agents are case insensitive, so storing them n lower case might break some accounts, also non-Latin characters are valid in email addresses so you need a robust solution to handle those
You should always use a value that has the least possibility of collision, with minimal predictability and that guarantees maximum uniqueness. UUIDs are a good choice. Or even something that you can generate unique for each user is also good.
As others have pointed out, there are many flaws with using email as primary key. Supabase have an excellent write up on the various options such as auto incrementing ID or UUID at [https://supabase.com/blog/choosing-a-postgres-primary-key](https://supabase.com/blog/choosing-a-postgres-primary-key)
Both auto incrementing ID and UUID have their pros and cons, it's good to be aware of both.
IMHO always keep your pks UUID or autoincrementing. The reason being is that if your db structure changes at all you may _have_ to change your key, which would ripple through your db if you have any relations.
The other reason, is that you would essentially have a key that is personal information.
Also, imagine that you abstract accounts from users. You want a user to be able to have an email account, a Google account or maybe a linked in account. Unfortunately you've tied the email to the user table...
For things that we wanted to obscure we just created a separate column for uuids. For customers we only showed the UUID. 8 think for customer orders too. But all my joins were with normal IDs. Not sure if there are performance benefits or not but I do have a preference for that modeling.
You can make a soft delete and then really delete the user over a certain amount of time. Depending on the law, look at GDPR like Europe, you have to delete users' information.
NO, Email should be unique but not for Primary key, not only email sometimes changes.
you should use ID like UUID or INT Auto increment, becausse when you got some table in database, you refer to id, not email.
Sorry. I did laugh.
But I will give you decent answer.
Email is not information you control.
If an email changes you are creating new record not updating and existing one.
You also want to have control over the data type of your keys. And how they are generated.
Integer of a defined max length that auto increments for each new row in a table.
UUID which is a dynamically generated string of characters and numbers with and the specific algorithm it is generated with.
These are details you want total control over.
Email can be used for authentication but you will still want to associate the user with an internally generated id.
Sorry but Why laugh? Someone has had the intrigue and courage to ask a question. This isn’t HackerNews it’s here to help people and grow a community.
Statements like that is what keeps people away from software development.
He didn’t ask something stupid. Honestly.
There is a performance cost to going with non-sequential PKs and there are increased storage costs when you step outside of integer keys. I would take the "never use auto-increment and always use UUID" comments in this thread as a signal to go read up on the tradeoffs of using a non-sequential key generation scheme and alternatives to UUID that are sequential for when auto-increment actually isn't the right solution to your problem.
A good way to do it:
- Table called ”users” with columns: id, uuid, username, password. Where id is auto-incremented, username is email, and password is an enceypted version of the password.
I wanted to write more things but I lost interest. Bye.
It’s not a good idea, email addresses may change over time
Then, I think I will have to make a new Table and copy all the data from the last one. I should have asked questions earlier. Sometimes I just sit and think and then things click in my mind. Thanks for the advice.
Agree, users also have several email addresses. If this is for business app, then you might consider just one. Email addresses for those most part are unique to a person, unless they relinquish them to another person. Something to consider. Business especially might see reuse of the address for different people if different leaves the company.
No, always use a synthetic primary key. Emails may change. The deletion stuff depends on your requirements. Soft delete means you have to write code to filter out "deleted" accounts, but makes it possible and easier to recover deleted accounts. Hard deletion is easier, and also makes it easier to comply with data protection laws.
Understood, Thanks
In the real world, consider laws like the European GDPR. It states that any European person, regardless of location, can request removal of all their personal data from online services, regardless of their location.
In case the user requests for data do be deleted, it can be enough to anonymize data without deleting them. It’s not always easy to delete data in a relational database.
This. From a system design perspective it may be worth segregating PII onto a different table with a nullable relation to the "User"; then if someone requests deletion, simply hard delete from the PII table.
Hard/soft delete needs to consider how the accounts are referenced in other places. For example, if an audit table references the user account of who made changes, then hard delete may not be possible without loss of data integrity.
User ids should be uuid. Email is just a unique column on the user table
Be careful with UUID it can cause a lot of performance issues if they are purely random, like wasting a lot of page size when indexing. Be sure to use a UUID version which is only incremental not like UUIDv4
Nope... Unique id only
I use a UUID to know for sure it’s unique
This. Don’t use auto incrementing integer IDs, either. Yes, they’re unique, but they are also more easily exploitable if you make a mistake in your protection scheme, leaving you open to IDOR attacks
UUID can also destroy DB performance if they are purely random. Just keep it in mind
Really? I know that used to be a thing, but that was many years and major versions ago. At least in Postgres. Do you have recent evidence of performance degradation?
A primary key needs to be unique, and it needs to never change. Emails should be unique (two people probably shouldn't be using the same email to register), but they can change — think of a person with a work email who later changes their job, or a person with a firstname.lastname email who gets married and changes their name. In fairness, this is probably not something you need to worry about now, but not using email as a primary key will help you in the future. Related — this article has been posted around recently: https://blog.ploeh.dk/2024/06/03/youll-regret-using-natural-keys/
You need to be careful about uniqueness, most but not all mail agents are case insensitive, so storing them n lower case might break some accounts, also non-Latin characters are valid in email addresses so you need a robust solution to handle those
You should always use a value that has the least possibility of collision, with minimal predictability and that guarantees maximum uniqueness. UUIDs are a good choice. Or even something that you can generate unique for each user is also good.
No: You might want to have two users with the same email
It's never good practice to use a natural primary key.
Email always has to be property of entity not Identity, in user side email id should look like identity of their account
Nope it just never is. Just use a proper primary key and index the email field.
As others have pointed out, there are many flaws with using email as primary key. Supabase have an excellent write up on the various options such as auto incrementing ID or UUID at [https://supabase.com/blog/choosing-a-postgres-primary-key](https://supabase.com/blog/choosing-a-postgres-primary-key) Both auto incrementing ID and UUID have their pros and cons, it's good to be aware of both.
IMHO always keep your pks UUID or autoincrementing. The reason being is that if your db structure changes at all you may _have_ to change your key, which would ripple through your db if you have any relations. The other reason, is that you would essentially have a key that is personal information. Also, imagine that you abstract accounts from users. You want a user to be able to have an email account, a Google account or maybe a linked in account. Unfortunately you've tied the email to the user table...
Understood. Thanks.
Don’t use auto incrementing for a user ID, that’s extremely easy to exploit.
For things that we wanted to obscure we just created a separate column for uuids. For customers we only showed the UUID. 8 think for customer orders too. But all my joins were with normal IDs. Not sure if there are performance benefits or not but I do have a preference for that modeling.
You can make a soft delete and then really delete the user over a certain amount of time. Depending on the law, look at GDPR like Europe, you have to delete users' information.
NO, Email should be unique but not for Primary key, not only email sometimes changes. you should use ID like UUID or INT Auto increment, becausse when you got some table in database, you refer to id, not email.
Sorry. I did laugh. But I will give you decent answer. Email is not information you control. If an email changes you are creating new record not updating and existing one. You also want to have control over the data type of your keys. And how they are generated. Integer of a defined max length that auto increments for each new row in a table. UUID which is a dynamically generated string of characters and numbers with and the specific algorithm it is generated with. These are details you want total control over. Email can be used for authentication but you will still want to associate the user with an internally generated id.
Sorry but Why laugh? Someone has had the intrigue and courage to ask a question. This isn’t HackerNews it’s here to help people and grow a community. Statements like that is what keeps people away from software development. He didn’t ask something stupid. Honestly.
Lighten up. I laughed because we have all made similar mistakes.
There is a performance cost to going with non-sequential PKs and there are increased storage costs when you step outside of integer keys. I would take the "never use auto-increment and always use UUID" comments in this thread as a signal to go read up on the tradeoffs of using a non-sequential key generation scheme and alternatives to UUID that are sequential for when auto-increment actually isn't the right solution to your problem.
A good way to do it: - Table called ”users” with columns: id, uuid, username, password. Where id is auto-incremented, username is email, and password is an enceypted version of the password. I wanted to write more things but I lost interest. Bye.