MySQL - Database design - separate tables for users and profile

Question

From what I've been reading online, I understood that it's better to split the data into more tables, if possible because of the access times.

Right now I have a table in which I am storing usernames, passwords and join date This is how my table looks:

'user'
'user_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
user_username VARCHAR(80) NOT NULL,
user_password VARCHAR(255) NOT NULL,
user_join_date INTEGER UNSIGNED NOT NULL,
PRIMARY KEY (user_id) ');

I am trying to create a new table called profiles in which I want to store first name, last name, email, age and gender. What I think is that I need a one-to-one relationship, so I should be using the user_id but I'm not really sure how to implement it.

Should I create another table called profiles with profiles

profiles_id
first_name
last_name
email
age 
gender

and another one which should be the relationship ? e.g.

user_profiles
----------
user_id
profiles_id

Is this right? How would the SQL look for the user_profiles?

Thanks

Answer 1

Don't split the tables. Just add the new columns to your existing user table. You might find later on that splitting tables is a good idea based on actual queries and usage patterns but until you have that kind of data, keep things simple.

If you must create a profile table, don't create a user_profiles table. That would allow an m-to-n relationship which is probably not what you want. A simple user_id column in profiles is better. In fact, it could be both a foreign key and the primary key to make sure that each user row only have one and only one profile row (although by splitting the tables you might still have a user with no profile).