New MySQL tables for user data

[elimelt]

We need to design a schema for all of the data entities that come along with user accounts.

[elimelt]

here is a non-exhaustive schema for users, settings, and games

-- key is the hashed and salted password users ( username varchar(256) primary key, salt varchar(salt_length), key varchar(hash_length), date_created date, last_login date )

games ( id int primary key auto_increment, username varchar(256) references users(username), snippet_id int, total_time int, total_characters int, wpm_data text, wpm_avg float, accuracy float, num_mistakes int, time_stamp datetime, )

settings ( type varchar(256), username varchar(256) references users(username), value varchar(256) primary key (type, username) )

[Harshi-R]

We could also keep track of any goals the user has for themselves.

goals ( date_created date, goal_end date, goal type text, login_streak int, games_played int, accuracy float, (taken only from goal timeline) wpm_avg float, (taken only from goal timeline) num_mistakes int, (taken only from goal timeline) )

[Harshi-R]

We created the tables for games, tables and users.

[elimelt]

we have completed this. finalized schemas:

users
+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| userID        | int          | NO   | PRI | NULL    | auto_increment |
| username      | varchar(256) | NO   |     | NULL    |                |
| salt          | varchar(32)  | YES  |     | NULL    |                |
| hash_password | char(64)     | NO   |     | NULL    |                |
| date_created  | date         | YES  |     | NULL    |                |
| last_login    | date         | YES  |     | NULL    |                |
+---------------+--------------+------+-----+---------+----------------+

games
+------------------+----------+------+-----+---------+----------------+
| Field            | Type     | Null | Key | Default | Extra          |
+------------------+----------+------+-----+---------+----------------+
| id               | int      | NO   | PRI | NULL    | auto_increment |
| userID           | int      | NO   | MUL | NULL    |                |
| snippet_id       | int      | NO   |     | NULL    |                |
| total_time       | int      | NO   |     | NULL    |                |
| total_characters | int      | NO   |     | NULL    |                |
| wpm_data         | text     | NO   |     | NULL    |                |
| wpm_avg          | float    | NO   |     | NULL    |                |
| accuracy         | float    | NO   |     | NULL    |                |
| num_mistakes     | int      | NO   |     | NULL    |                |
| time_stamp       | datetime | NO   |     | NULL    |                |
+------------------+----------+------+-----+---------+----------------+

settings
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| type   | varchar(16)  | NO   | PRI | NULL    |       |
| userID | int          | NO   | PRI | NULL    |       |
| value  | varchar(256) | NO   |     | NULL    |       |
+--------+--------------+------+-----+---------+-------+