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 ?