Best way to handle unique member ID's in the db

[TechBoyJK]

Hi Guys,

I've been working on an app, and each member gets a unique ID.

In the primary table, users have a column called 'member_id'. It's the first column, and the primary key. It's generated by auto-incrementing.

Basically, first member that signs up, gets unique member ID of 1.

Is this ok? seems pretty simple and basic.

Or, should I create the unique ID through an external process?

 

[EagleKeeper]

Why duplicate the effort?

 

[degibson]

Auto-increment.

Start at member number 25478 so member #1 doesn't feel too special.

 

[WannaFly]

Shouldn't be a problem as long as you minimize the exposure of the member_id to the users.
If you use it in a querystring it makes it much easier to brute force, but i guess the same could be said for usernames also.
i.e. showprofile.php?memid=1, showprofile.php?memid=2, you could very quickly scrape out your entire member database.

I also agree with degibson, just start the seed on a 5 digit number, so there is no #1.

Make sure to put a constraint on the username field if you have one to ensure it is unique.

 

[SJP0tato]

Auto-increment.

Start at member number 25478 so member #1 doesn't feel too special.

Marge: Homer a man who called himself “you know who” just invited you to a secret “wink wink”, at the “you know what”. You’re certainly popular now that you’re a Stonecutter.
Homer: O yeah. Beer busts, beer blasts, keggers, steinhoist, AA meetings, beer night. It’s wonderful Marge. I’ve never felt so accepted in all of my life. These people look deep within my soul and assign me a number based on the order in which I joined.

That being said... auto-increment is always how I've seen it done.

 

[Ka0t1x]

Yep, start it out at some arbitrary number.

And any where that you'd be tempted to publicly show the ID (like the showprofile.php example above).. just use the username.

 

[Markbnj]

We usually start at 1, but by the time we're done with development and all the testing rounds it's well up in the hundreds, anyway.

 

[Krioni]

You could also use GUIDs for the ID column. That would keep people from guessing the next user ID as was suggested in another post. But, they are a larger data type, so I wouldn't use that if you are expecting large row counts.

Another idea would be to change the increment to something other than 1... 7 or something like that... then it's a little harder for someone to guess (until they figure out the patter).

 

[degibson]

There's nothing wrong with knowing "member numbers." Database keys aren't secrets -- if you need a secret, add a password.

 

[BigDH01]

How would the user ever know their own auto-increment primary key? They (hopefully) know their username/password and they can locate their session ID if they try, but there's really no need to be passing unique keys back and forth.... either as url parameters or POST data. If you're displaying private information that shouldn't be public, and using a primary key as a URL parameter, then I'd recommend you stop it. Put it up past your password barrier and only let users see information based on their own session ID.

 

[Cogman]

auto increment. There is also nothing wrong with starting from 1 and moving up.

If you have a place that shows personal information of members, don't use the members ID. Rather, create a random session ID and hand that out. Tie the session ID to user IDs and whatever other information you feel it needs to be tied to.

Auto incrementing from 1 or 0 is the best, though, because it is common. There is a good chance that the DB does some special magic sauce with an auto incrementing key number to make it faster than a random key number.

 

[KentState]

If you are lazy and expose the internal id, then use a GUID, otherwise it shouldn't matter.