Jump to content

passwords as...


jimfog

Recommended Posts

Since every table must have a primary key column and since each value in it must be unique, can passwords be used a primary keys? What problems may arise by adopting passwords as primary keys-if any?

Link to comment
Share on other sites

What if two users want to use the same password? You want to allow users with the same username in, as long as they have different passwords? You wouldn't allow registrations because a password someone chose matches that of an existing user?All that sounds like something a hacker might use to figure out passwords... create a bunch of accounts with the same name, but a different password, and do that until declined because of a password conflict, in which case they can safely assume another user has that password, after which they can try every user that they can see.No, this sounds like a terrible idea. A primary key needs to be something that isn't allowed to repeat in any circumstances. Like the username or an email for example.

Link to comment
Share on other sites

it wont be such bad if it is used withproperly salted hashed password. properly salted hashed password never going to be same for two user in any circumstances even if the raw password is same. but doing so wont give you any advantage other than performance penalty. primary key are automatically indexed. which means when ever you insert something into table the whole tables is got re-indexed. as it gets larger it will take more time to doing so. unnecessary indexing can reverse the affect of indexing. you never going to query to match a certain password. do you? so it is not worthy to make it primary key (indexed).

Link to comment
Share on other sites

What if two users want to use the same password? You want to allow users with the same username in, as long as they have different passwords? You wouldn't allow registrations because a password someone chose matches that of an existing user? All that sounds like something a hacker might use to figure out passwords... create a bunch of accounts with the same name, but a different password, and do that until declined because of a password conflict, in which case they can safely assume another user has that password, after which they can try every user that they can see. No, this sounds like a terrible idea. A primary key needs to be something that isn't allowed to repeat in any circumstances. Like the username or an email for example.
Ok I got your point, I am not surprised to hear all these. Then we come down to the question what should I use as a primary key, the e-mail(for example,as you mention) OR create a separate column of the INT type ans set it to AUTO INCREMENT Edited by jimfog
Link to comment
Share on other sites

I wouldn't use an auto-increment field as the primary key for a user table. Simply because it's unnecessary. A user cannot (or should not) have the same username as another user, so why not use that as the primary key? If you're using email as the username then, yes, make that the primary key.

Link to comment
Share on other sites

int type is faster with primary key when it compares or used in joins, than a varchar/char. it also save disk spaces. imagine you have multiple table connected to each other. if it uses character as foreign key it will take much more space than an integer overall.

Edited by birbal
Link to comment
Share on other sites

int type is faster with primary key when it compares or used in joins, than a varchar/char.
AFAIK, when the (VAR)CHAR field is a primary key, or any index, on both sides of the JOIN, this penalty is eliminated. I could be wrong on that one though (I haven't made enough benchmarks).
Link to comment
Share on other sites

I prefer to use autonumber int fields for pretty much every table. For a users table, that would let people change their username or email address without needing to update multiple tables. The integer foreign keys also take less space to store in the linked tables.

Link to comment
Share on other sites

The space issue is probably the most important one. For one of our larger clients, there is a lookup table that holds 2 unsigned integers per row, a user ID and another ID. Right now that table has over 38 million rows and takes up 3.4 GB. MySQL uses 4 bytes to store an int. If The user ID were a varchar, then in order for the table to be the same size each username would have to be only 3 characters long in order to use 4 bytes. It looks like the 65,579 users in that database have an average of 7.5 characters in their username, so that would mean that lookup table would be over twice as big to store the same data (and would also need to be updated if a user changed their username). That particular client is a bit of an anomaly regarding usernames, many of their usernames are 5-digit numbers. Other clients have an average username length of more than 10 characters.

Link to comment
Share on other sites

For a users table, that would let people change their username or email address without needing to update multiple tables.
Good point.
The integer foreign keys also take less space to store in the linked tables.
Also a good point. Hadn't really thought about that, but I guess that makes sense. Leave it to JSG to enlighten us. ^_^ Edited by ShadowMage
Link to comment
Share on other sites

so, concluding it seems that auto-number int is the proper "candidate " for primary key.

  • Like 1
Link to comment
Share on other sites

Guest So Called

Isn't that the default? I haven't gone to any special effort to define my tables because I don't need high performance. It seems to me that every time I add a new table to MySQL that the default primary key is 'ID' and it's int(10) auto-increment. It seems reasonable to me to just accept the default unless you have important reasons to use something else.

Edited by So Called
Link to comment
Share on other sites

MySQL doesn't create default columns that you haven't specified in a table. You might be using third-party software to create tables which will do that, but it's not part of MySQL itself. MySQL expects the database designer to specify exactly what they want.

Link to comment
Share on other sites

MySQL doesn't create default columns that you haven't specified in a table. You might be using third-party software to create tables which will do that, but it's not part of MySQL itself. MySQL expects the database designer to specify exactly what they want.
Exactly right-things must be done manually.
Link to comment
Share on other sites

when the (VAR)CHAR field is a primary key, or any index, on both sides of the JOIN, this penalty is eliminated
i can't think of how can it be eliminated. can you explain it further? or any link to resource?
Link to comment
Share on other sites

i can't think of how can it be eliminated. can you explain it further? or any link to resource?
When both columns of the JOIN are indexes (I mean index for the same value; like a primary key in one column being joined with a table where the column you'll be joining is a foreign key of that table), the indexes (which are ints) get checked for equality, not the (VAR)CHARs. This is possible since both indexes are based on the same set of values. The result set is of course formed from the VARCHARs which are extracted from the index after the JOIN.I'm not sure if this is applicable to MySQL (like I said, I haven't made benchmarks)... I learned this at a DB class at uni, which is based on MSSQL, not MySQL.But yeah, justomeguy's point about space remains valid - indexes take space, and an index for INT values will more often than not be less than the index for (VAR)CHAR values.
Link to comment
Share on other sites

Since we mentioned also the concept of INDEX I want to make the following question. When we make a column primary key, does that mean that it will be indexed also-by default?Can we choose to have a primary key and NOT be indexed?

Link to comment
Share on other sites

When we make a column primary key, does that mean that it will be indexed also-by default?
Yes. in InnoDB a clustered index is set on primary key. it is the first priority. if any PK is not there its try to set clustered index on UNIQUE constarinted NOT NULL column. lastly if still it is not there it creates a invisible column for clustered index.
Can we choose to have a primary key and NOT be indexed?
For the same reason as above. you can not.
Link to comment
Share on other sites

When both columns of the JOIN are indexes (I mean index for the same value; like a primary key in one column being joined with a table where the column you'll be joining is a foreign key of that table), the indexes (which are ints) get checked for equality, not the (VAR)CHARs. This is possible since both indexes are based on the same set of values. The result set is of course formed from the VARCHARs which are extracted from the index after the JOIN.I'm not sure if this is applicable to MySQL (like I said, I haven't made benchmarks)... I learned this at a DB class at uni, which is based on MSSQL, not MySQL.
I almost forget MSSQL (due to lack of practice) but what i know about Mysql is it uses some fixed memory to load indexes at a time.as integer takes 4 bytes which is smaller than the characters it can load much rows at a time which can be checked. where if it is char types it needs to load that index buffer more times than integer. thus it slows it down.
This is possible since both indexes are based on the same set of values.
same set of value but they will be in different tables. how does it be base on same set of values? Still they have to load the indexes for both of tables to match.
Link to comment
Share on other sites

same set of value but they will be in different tables. how does it be base on same set of values? Still they have to load the indexes for both of tables to match.
To define a foreign key constraint on a column, you must define a column where the values must come from (a "reference" column). It is from this characteristic that the value set is known => the foreign key's index will be created in a fashion compatible with the referenced column's index. Of course, if the referenced column is not indexed, the foreign key's index will be a completely new and independent one, and thus it will still have the JOIN penalty.
Link to comment
Share on other sites

Yes. in InnoDB ...
What about MyISAM. Are primary keys indexed automatically in the MyISAM storage engine?
Link to comment
Share on other sites

One final thought.I wonder if it is better to use the username as the primary key(instead of AUTO INCREMENT INT)-this because, looking the tables in the database, it will be easier to associate a particular action with a user(by looking at the username-as a foreign key) instead of using INT. If for example a user has made a purchase and an INT is associated with that user in the "purchases" table, than in order to learn who that is i have to look in the "users" table. In the other case(username as primary key) it might be not necessary the above. Maybe the DB can be maintained easier that way.

Link to comment
Share on other sites

I guess it depends what your priority is. If your priority is to make the database human-readable, then maybe it makes sense to use username as a primary key. If your priority is to make the database efficient, then it makes sense to not have duplicate data like a username in multiple tables. A simple query with a join will return the data you're looking for along with the user it points to.

Link to comment
Share on other sites

Guest So Called

It would be a bad idea to use username as a key if there is any chance of a user changing it. Every forum I've ever seen uses user numbers as the key to their member database. There must be a good reason for that.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...