2

My users will login into a website using a login of their choosing, but which I will check against the DB to ensure unicity. Given this, should I also have a unique userID in the DB? This could be an autoincrement number and would be completely invisible to the users.

Thanks,

JDelage

| improve this question | |
2

The unique number is what we'd call a surrogate key (as opposed to a natural key, which would be some part of the actual data).

It is almost always advisable to use a surrogate key, the cases where it isn't are vanishingly small.

Also, I would recommend against using an auto-increment column. It would be much better to use a GUID. The main problem with an auto-increment is that you must insert the row into the database before you can know what the surrogate id is. With GUID's, you can safely generate the surrogate id without consulting the database. It will be difficult to change later if you go with an auto-increment column now.

| improve this answer | |
  • If I understood properly what you are saying I think you are missing SQL functions like LAST_INSERT_ID that let you easily retrieve the last inserted id for an auto-increment field. – Marco Demaio Mar 30 '11 at 16:01
  • Nope, I don't think you are understanding exactly. Needing to call LAST_INSERT_ID to get the id from the database is exactly the problem. It unnecessarily complicates the code, introduces extra round-trips to the DB, and it introduces serious complications scaling the application. It's best to avoid auto-increment altogether and go with GUID's from day 1. This is as close to universally true as you can get. – quentin-starin Mar 30 '11 at 16:04
  • 1
    Here's one article I picked because it's Spolsky, there's more info in the comments and linked-to pages than his post: codinghorror.com/blog/2007/03/… – quentin-starin Mar 30 '11 at 16:34
  • 1
    Use whatever facility exists in your language to generate a new GUID, store them in a GUID column if the storage engine has one, otherwise char is good. – quentin-starin Mar 31 '11 at 5:20
  • 1
    int vs. guid comparison is a micro optimization. If you have enough traffic to warrant such optimizations, you would in an extremely high percentage of scenarios gain far more performance by scaling and distributing the workload, something that will then require large scale re-architecting if you used auto incrementing integer primary keys. Been there, done that. Have you? – quentin-starin Mar 31 '11 at 14:46
2

Its always good to have an ID which autoincrements as you put new users in your DB. In the future you might need it. And it will ensure that there are no replicates in your DB and your program doesn't fail while logging in. You can make this the primary one also.

| improve this answer | |
  • Thanks. Could you clarify "In the future you might need it."? – JDelage Mar 29 '11 at 14:34
  • the answer by Ammar Alakkad is great. thats what you might need sometime when building an application. and also once you make your framework for the app, it will be very hard for you to change it in the future. specially if its going to be open source or you need to make big changes to your program in the future. you wouldn't be able to tell all your users to again sign up on your site. rather keep some places where you can improve in the future. – Arjun Bajaj Mar 30 '11 at 12:38
  • ??? – Marco Demaio Mar 30 '11 at 15:54
2

It's recommended to do that, you may want to link it with foreign keys from other tables or to do queries that contains something like:

"SELECT * FROM users WHERE user_id > 1000"

When dealing with primary keys it's very handy if it were numbers.

| improve this answer | |
  • +1 Excellent example of why numerical keys are good to have – John Conde♦ Mar 29 '11 at 18:44
  • 2
    @John: I wouldn't consider that a good example at all. What logical purpose would such a query serve? Any purpose I can conceive would be better served with a different piece of data (e.g., CreatedOn). Surrogate keys are there only to serve as an immutable unique key. There are also very real and significant limitations with an auto-increment column. GUID's don't suffer those issues, and if one really wanted ordering could be at least partially provided with a comb strategy for GUID generation. – quentin-starin Mar 29 '11 at 22:02
  • @qes: totally agree, I've never seen a query more useless than the one provided as an example in this answer. – Marco Demaio Mar 30 '11 at 15:52
1

If your user is not allowed to ever change the user name he chooses the 1st time, and you are absolutly sure that you will never give the user the possibilty to change such user name for the rest of his life, you could also just use this user name as a unique primary key.

This is not the case when user name is the email itself, it would be nosense to not allow the user to change it ever.

You can find best answers here: https://stackoverflow.com/questions/4466553/use-of-an-id-in-databases

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.