Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

OK, I'm inexperienced in terms of database design, so hopefully my question isn't too silly.

I have 2 tables users and user_profiles these two tables has one-to-one relation between them. so(obviously) each row in users table will be linked to a row in user_profiles table.

And, when I'm displaying a user's profile in the web app I'm creating I have to do two SELECT statements(or JOIN) to get the required data, which is -somehow- overkill for me, and I'm looking for an alternative to it, I'm trying to get the required data with one SELECET statment from one table.

users schema:

CREATE TABLE IF NOT EXISTS `users` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `username` varchar(50) COLLATE utf8_bin NOT NULL,
   `password` varchar(255) COLLATE utf8_bin NOT NULL,
   `email` varchar(100) COLLATE utf8_bin NOT NULL,
   PRIMARY KEY (`id`)
)

user_profiles schema:

CREATE TABLE IF NOT EXISTS `user_profiles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `nickname` varchar(50) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`id`)
)  

So for example, if I want to grab the nickname, user and email I'll definitely use JOIN or double SELECT. Which will lead me to use two tables for such a simple query.

So, I have currently three solutions in mind:

  1. Use JOIN or SELECT and hopefully my website won't be too slow because of this. Will this have huge impact on the performance of my web app anyway?
  2. Remove user_profiles table and keep all user's personal data in one table.
  3. Duplicate columns in two tables, so for example the email column will be present in users and user_profiles. Which will result in double INSERTS at the time of user registration.

Looking forward to hear your opinions.

share|improve this question
1  
Definitely 1. That's how things are supposed to work in a relational DB, therefore it won't be slow. (Or, if you are really sure that the contents of user_profiles depends only on user_id, than merge the tables. This will depend on your use case.) –  dezso Aug 3 '12 at 10:21
1  
It is rarely a good idea to have a 1-1 relationship beteween tables. Fix your design to have user and user profile in 1 table. –  Emmad Kareem Aug 3 '12 at 11:44
    
Can a user have more than one profile? –  AlexKuznetsov Sep 2 '12 at 20:27
    
@AlexKuznetsov Nope, only one. –  Rev3rse Sep 3 '12 at 9:53
add comment

1 Answer 1

I think that you incorrectly identified relationship between users and user_profiles; I usually see it's either 1:0..1 (user can -but doesn't have to- have at most one profile) or 1:0..n(user can have many profiles). Surely, if it's really 1:1 in your system, it makes sense to get rid of user_profiles and add nickname to users.

There is nothing wrong with having join in the query, as dezso mentioned in comments, it's RDMS main purpose and responsibility to perform such operations in fast and reliable way.
Also, in my understanding there are just very few places in the system where you need to retrieve information from user_profiles; normally you just display basic information from users.

Finally, if it's 1:0..1 relationship, there is absolutely no need in user_profiles.id column - user_profiles.user_id should be primary key and foreign key to users.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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