CODEWITHSUNDEEP
phpmysqlsqldatabase
mihai121
mihai121

Reputation: 73

MySQL - Database design - separate tables for users and profile

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

Upvotes: 7

Views: 15338

Answers (6)

Unix von Bash
Unix von Bash

Reputation: 730

Add a new field in the User table, ProfileId, and set it as Foreign Key (FK). Each time you create a User, you have to assign to it a profile (which will be the ProfileId PK from profile table). If you want to see also the profile information of a user, you have to do a join

Select username, first_name,second_name
From user u, profile p
Where u.profileId = p.profileId

this

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

is used in a many-to-many relationship. By example, you want to assign to an admin some privileges, but those privileges can be also assigned to more admins. Then, you have to create a 3rd table to solve this problem. Here is an example, but you don't need to do this. enter image description here

Upvotes: 2

Joan-Diego Rodriguez
Joan-Diego Rodriguez

Reputation: 2547

It is true that having more than one tables is a good idea. I am not sure what you mean about access time, but there are other advantages. - Your users database containing passwords etc is "sacred", you never change its structure and you limit the rights to it (read, write) to the strict minimum. - You can then have several "satelites" tables such as profiles, private messages, etc which are more flexible, less sensitive and which you can change all the time.

About your question per se, there is no need for a separate table with the relationships. In fact is a very bad idea which will complicate your queries and doesn't have any advantage. Instead, in your profiles database you will have one column that refers back to the user id.

users
--------
id
user_name
email
password


users_profiles
---------
id
user_id
favourite_animal

Upvotes: 1

Guillaume Poussel
Guillaume Poussel

Reputation: 9822

Usually, you create an association table, like user_profiles you have described when one user could have more than one profile, and/or one profile could belong to one or more user.

As you have said, here you have a one-to-one relationship between user and profile. So, you can simply add a user_id column to your profile table, and define it as a foreign key to user table.

Then, a simple JOIN will allow you to query both tables at the same time:

SELECT u.*, p.*
FROM user u
JOIN profile p ON u.user_id = p.user_id

Upvotes: 3

zkanoca
zkanoca

Reputation: 9918

Table user

user_id |user_username |user_password |user_join_date |profile_id

Table profile

profile_id |first name |last name |email |age |gender

When selecting a user by user id:

SELECT u.*, p.* FROM user AS u INNER JOIN  `profile` AS p ON u.profile_id = p.profile_id WHERE u.user_id = 1

But a user should only one gender, one age, one name and surname. Maybe e-mail adresses might be many. I suggest you there is no need to join tables which have a 1-to-1 relation. Instead merge those tables.

Upvotes: 0

acfrancis
acfrancis

Reputation: 3661

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).

Upvotes: 9

xsc
xsc

Reputation: 6073

You could add a user_id field to your profiles table and JOIN the tables on user_id.

SELECT user.user_username, ..., profiles.first_name, ...
FROM user
INNER JOIN profiles
ON user.user_id = profiles.user_id

This should fetch data combining information from those rows where the JOIN condition is met (i.e. user.user_id = profiles.user_id).

Upvotes: 1

Related Questions