3

I'm trying to create a simple register login script with user profiles, I created a table with the following structure:

+------------+---------------+------+-----+---------+----------------+
| Field      | Type          | Null | Key | Default | Extra          |
+------------+---------------+------+-----+---------+----------------+
| id         | int(11)       | NO   | PRI | NULL    | auto_increment |
| username   | varchar(32)   | NO   |     | NULL    |                |
| password   | varchar(32)   | NO   |     | NULL    |                |
| first_name | varchar(50)   | NO   |     | NULL    |                |
| last_name  | varchar(50)   | NO   |     | NULL    |                |
| email      | varchar(1024) | NO   |     | NULL    |                |
| active     | int(1)        | NO   |     | 0       |                |
+------------+---------------+------+-----+---------+----------------+

How should I store other data like:

country, user level, registration date, domain name, email activation key, last login date, and so on ...

I have 2 options (maybe there are more I'm not aware of):

  • creating these as columns in this table

  • creating another table that is related by the user id

My questions are:

  • which one is better if later I want to add more data to be stored ?
  • If I take the first approach with one table is there going to be a problem when adding more columns later if we already have active users ?
  • and if we take the second approach how should I structure the data table ?

And thank you.

UPDATE:

What do you think about the WordPress approach in this matter ? WordPress has a usermeta table with this structure ?

+------------+---------------------+------+-----+---------+----------------+
| Field      | Type                | Null | Key | Default | Extra          |
+------------+---------------------+------+-----+---------+----------------+
| umeta_id   | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| user_id    | bigint(20) unsigned | NO   | MUL | 0       |                |
| meta_key   | varchar(255)        | YES  | MUL | NULL    |                |
| meta_value | longtext            | YES  |     | NULL    |                |
+------------+---------------------+------+-----+---------+----------------+

So what do you think about this way of storing users data ? should this be considered in an app that will have lots of users ?

1
  • Ask yourself: are those data belong to a user and only a user? If your answer is yes, put those into the user table. By the way, country and user level are candidates for being separated into their own tables and referenced here by foreign keys. Dec 19, 2012 at 13:44

1 Answer 1

7

This is a good "there's more than one way to do it" question.

Start by thinking about your data usage:

Think about which fields are going to be read and written more often.

For example, first name and last name are probably written once and are rarely changed, but depending on your app, they could be read very often.

Last login date could be written frequently, and you might want to keep every login date.

Things like activation key and registration date, depending on how authentication works in your app, might be written once and then very rarely read.

You should also think about what data is pulled at the same time, and try to denormalize to make those reads faster. For example, if you are always pulling username and password and first name together (but no other contact info), put firstname in the login table, too. That is, think about avoiding joins if this data will be large, and be kind to yourself.

Also think about which fields might be in the "where" clause of your queries, and index on those fields.

Here's one way I might do it:

Contact Table (one insert, rare updates, many reads)

+------------+---------------+------+-----+---------+----------------+
| Field      | Type          | Null | Key | Default | Extra          |
+------------+---------------+------+-----+---------+----------------+
| user_id    | int(11)       | NO   | PRI | NULL    | auto_increment |
| first_name | varchar(50)   | NO   |     | NULL    |                |
| last_name  | varchar(50)   | NO   |     | NULL    |                |
| address    | varchar(50)   | NO   |     | NULL    |                |
| city       | varchar(50)   | NO   |     | NULL    |                |
| state      | varchar(50)   | NO   |     | NULL    |                |
| country    | varchar(50)   | NO   |     | NULL    |                |
| email      | varchar(1024) | NO   |     | NULL    |                |
+------------+---------------+------+-----+---------+----------------+

Login Table (one insert, maybe updates, many reads; need first_name to be echoed on login)

+------------+---------------+------+-----+---------+----------------+
| Field      | Type          | Null | Key | Default | Extra          |
+------------+---------------+------+-----+---------+----------------+
| user_id    | int(11)       | NO   | PRI | NULL    |                |
| username   | varchar(32)   | NO   |     | NULL    |                |
| password   | varchar(32)   | NO   |     | NULL    |                |
| first_name | varchar(50)   | NO   |     | NULL    |                |
+------------+---------------+------+-----+---------+----------------+

Login History Table (many writes, multiple entries per user_id)

+------------+---------------+------+-----+---------+----------------+
| Field      | Type          | Null | Key | Default | Extra          |
+------------+---------------+------+-----+---------+----------------+
| user_id    | int(11)       | NO   | MUL | NULL    |                | 
| last_login | timestamp     | NO   | MUL | NULL    |                |
+------------+---------------+------+-----+---------+----------------+

Signup Table (one write, rarely read)

+------------------+---------------+------+-----+---------+----------------+
| Field            | Type          | Null | Key | Default | Extra          |
+------------------+---------------+------+-----+---------+----------------+
| user_id          | int(11)       | NO   | PRI | NULL    |                |
| registration_date| timestamp     | NO   |     | NULL    |                |
| activation_key   | varchar(50)   | NO   |     | NULL    |                |
+------------------+---------------+------+-----+---------+----------------+
3
  • 3
    Very nice analysis. The only thing I would add is that you would also want unique indexes on username or username/password. You certainly would not want anyone to be able to log in to multiple accounts with the same username and password combo.
    – HLGEM
    Dec 19, 2012 at 20:02
  • Thank you, very nice answer, but what do you think about the wordpress approach in this matter ? I updated my answer with the wordpress meta table.
    – Pierre
    Dec 20, 2012 at 7:58
  • 1
    Key-value tables have a place, sure. Again, it depends how you are going to query the data. If you are always going to ask for all key/values by umeta_id, not a problem (and maybe some advantage). If you end up with many rows and have to pull a query like: select value where key="login_date", then you could end up with a very long-running query. You also give up a bit of data integrity. Unless your app is very good at validating data and doesn't ever change, you'll end up with different keys with the same underlying business logic, etc. Dec 21, 2012 at 4:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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