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:

   `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` (
  `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.

  • 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. – NoChance Aug 3 '12 at 11:44
  • Can a user have more than one profile? – A-K Sep 2 '12 at 20:27
  • @AlexKuznetsov Nope, only one. – Abdulaziz Sep 3 '12 at 9:53

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.

| improve this answer | |
  1. Remove user_profiles table and keep all user's personal data in one table.

the user_profiles table is not storing any data about the nickname itself and you say a user can have no more than one nickname. I assume the nickname is optional. The only reason to have it in a separate table MIGHT be for performance to get a list of nicknames such as when a new nickname is created to check it hasn't been used already. But I doubt keeping it in a separate table is the best way to handle that.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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