0

I am using devise for authentication and i have added some user-personal fields which i am not sure is the correct way to continue developing my app and is the proper database design...

users   CREATE TABLE `users` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `email` varchar(255) NOT NULL DEFAULT '',
 `encrypted_password` varchar(255) NOT NULL DEFAULT '',
 `reset_password_token` varchar(255) DEFAULT NULL,
 `reset_password_sent_at` datetime DEFAULT NULL,
 `remember_created_at` datetime DEFAULT NULL,
 `sign_in_count` int(11) DEFAULT '0',
 `current_sign_in_at` datetime DEFAULT NULL,
 `last_sign_in_at` datetime DEFAULT NULL,
 `current_sign_in_ip` varchar(255) DEFAULT NULL,
 `last_sign_in_ip` varchar(255) DEFAULT NULL,
 `created_at` datetime NOT NULL,
 `updated_at` datetime NOT NULL,
 `username` varchar(255) NOT NULL DEFAULT '',
 `twitter` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
 `facebook` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
 `location` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
 `website` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
 `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
 `bio` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `index_users_on_email` (`email`),
 UNIQUE KEY `username` (`username`),
 UNIQUE KEY `index_users_on_reset_password_token` (`reset_password_token`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1

should i create another table and store the personal fields of the user such as

  • twitter
  • facebook
  • location
  • website
  • name
  • bio

or its fine to keep them as they are currently?

stergosz
  • 5,754
  • 13
  • 62
  • 133

2 Answers2

1

I would abstract those attributes out to a different table. Maybe create a 'Profile' table where you can keep things like this. Then a User would belongs_to Profile, and a Profile has_one User.

Mark Locklear
  • 5,044
  • 1
  • 51
  • 81
  • this is what i thought at first and how i have done it using php & mysql as well so i think this is the way to go! thanks. – stergosz Jul 05 '12 at 23:26
0

You should check the database normalization. location, website, name and bio are columns where redundant data can appear, but I would hazard the consequences of denormalization, so you don't need a JOIN-Statement to link two tables, which is faster than linking them.

Chris
  • 4,255
  • 7
  • 42
  • 83