0

I'm in the process of creating an app and I need to create a table to store login credentials. I've been searching the internet to make sure I do this correctly, but I figured it best to run this by you people before I implement my strategy. I will be running MySQL serverside.

This is what I have for my user table. I do not want to use 'user' because it is reserved. I do not want to use 'users' because it's my understanding that you don't want a plural as a table name. Is 'users' an exception?

CREATE TABLE usercredential
(
    id int unsigned unique auto_increment,
    username varchar(32) unique not null,
    salt char(32) not null,
    hash char(64) not null
);

The id is used so users can change their username without me having to go into every other table and make the appropriate change. Will setting a reference remove the need for having a unique id? Is there any reason to start the unique id at a certain number other than 1?

I will be using SHA256, so the hash length of 64 should suffice.

The questions:

  1. Is there a commonly accepted name for a user login table?
  2. Do I need to have the unique id? I plan on allowing users to change their username, so I want to account for that. Is a unique id the best way to go?
  3. Is there any reason to start my unique id at anything other than 1?
  4. Are there any other issues you see with my table? I want to make sure it is secure, but security is not my specialty and I cannot hire someone else to take care of it.

Thank you in advance.

Matt
  • 1,392
  • 12
  • 24
  • I call my tables tbl_users all the time. Nothing wrong with that, unless you're trying to implement some kind of clever class - table mapping scheme. Class names are singular, tables - plural and the conversion could be tricky. – Sergey Nov 05 '14 at 01:28
  • 1
    If you use one of the prepackaged functions to hash your passwords, you don't need a separate field for the salt; it's built into the hashed password. Read this: http://php.net/manual/en/faq.passwords.php If you roll your own password hashing function, you are asking for trouble from cyber badguys. – O. Jones Nov 05 '14 at 01:50
  • I've read it's much better to have unique salts than one generic salt. If I do this, how else would I store them? I understand it adds a step, but for the extra security, I'd say it's worth it. – Matt Nov 05 '14 at 04:27
  • On the provided link to the PHP manual, scroll down to ["How do I store my salts?"](http://php.net/manual/en/faq.passwords.php#faq.password.storing-salts). As @OllieJones said, your (unique) salts will be stored within the same column! – wolfgangwalther Nov 05 '14 at 04:31
  • Yep. That makes sense. And just to clarify, I have my app call the PHP script that will then connect to the DB and retrieve JSON results. I then parse the JSON and send it back to my app, correct? – Matt Nov 05 '14 at 05:05

2 Answers2

0

Will setting a reference remove the need for having a unique id?

Those are unrelated issues. All ids should be unique, regardless of how they are used. Having one field change in one table and have that change cascade to other tables, is a separate issue. You can do that with foreign key constraints on InnoDB engine (easy).

Is there any reason to start the unique id at a certain number other than 1?

No, but in case a user will ever see their id, you may want to pretend like they are not user number 7. User number 29837 looks way more established.

Id should be your primary key and username should be your unique index.

Sergey
  • 494
  • 2
  • 6
0

First of all "user" is not reserved in MySQL. It is however in SQL Server and Oracle.

I personally prefer to name my tables in the singular, not plural. This is however just a question of individual style. You should be consistent, though!
Here are some discussions about this on the network:
Table Naming Dilemma: Singular vs. Plural Names
Singular or plural database table names?
Plural vs Singular Table Name
Interestingly enough, all three of those discussions have a different approach for their accepted answer.

Second, you should set id as the primary key of your table. Being a primary key will make it unique anyway. You will then use this column as the foreign key in other tables.

I would start id at 1 and only use it internally after that. Whenever referencing that user in the frontend, where the id can be seen, I would use the username for that, since it is unique as well. You should probably add an index to your username column for that as well. The reason behind this is, that you can't predict other usernames from a single one. You can, however, predict ids if you have one of those.

Personal opinion: I would change the name of username to name. Repeating the table name in the column just makes queries unneccessarily longer. user.name is just much better to read than user.username. There is a discussion about that on SO as well: Is prefixing each field name in a table with abbreviated table name a good practice?

Community
  • 1
  • 1
wolfgangwalther
  • 1,226
  • 7
  • 15
  • Thank you. Everything makes sense and I appreciate the links. But, if I use a foreign key, then why do I still need to use an id along with the username (especially if I index the username)? I can simply link each table via the foreign key (username) and not have to ever worry about the id. If the user modifies his username, that should propagate due to how foreign keys work, right? – Matt Nov 05 '14 at 04:18
  • See http://stackoverflow.com/questions/337503/whats-the-best-practice-for-primary-keys-in-tables/338424#338424 for a couple of reasons. While it is true, that those `username` foreign keys would be updated on a change, that could involve, depending on the amount of related data, quite a few updates to the database, that would not be neccessary otherwise. Storage size of your fk columns is another important thing to consider. Short: Using the `id` column as primary key is better for performance. – wolfgangwalther Nov 05 '14 at 04:28