# Proper Database Design For A Newbie **URL:** \ **Category:** Rails\ **Created:** [April 23, 2006, 1:06am UTC](https://www.ruby-forum.com/t/proper-database-design-for-a-newbie/58550 "2006-04-23T01:06:03Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![acgreen](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/3bc359/32.png) [@acgreen](https://www.ruby-forum.com/u/acgreen)\ **Post date:** [April 23, 2006, 1:06am UTC](https://www.ruby-forum.com/t/proper-database-design-for-a-newbie/58550/1 "2006-04-23T01:06:03Z")
Hi all, I am starting out learning Ruby on Rails (coming from a PHP background) and I have a question about proper database design. Let me give you a quick example of what I want to do: Database: CREATE TABLE `users` ( `id` int(11) NOT NULL auto\_increment, `login` varchar(40) default NULL, `email` varchar(100) default NULL, `crypted_password` varchar(40) default NULL, `salt` varchar(40) default NULL, `created_at` datetime default NULL, `updated_at` datetime default NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE `messages` ( `id` int(11) NOT NULL auto\_increment, `message` text NOT NULL, `date` datetime NOT NULL default ‘0000-00-00 00:00:00’, `sender_id` int(11) NOT NULL default ‘0’, `receiver_id` int(11) NOT NULL default ‘0’, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; (I’m using acts\_as\_authenticated for my user management.) Whenever a message is sent, I want both the sender\_id and the receiver\_id to point to the user\_id. I guess I am wanting to know what is the best way to go about setting up the model file when you have two fields that point to the same table id. I realize that I could separate the message table out to something like this and use HABTM: CREATE TABLE `senders` ( `message_id` int(11) NOT NULL default ‘0’, `sender_id` int(11) NOT NULL default ‘0’, ) ENGINE=MyISAM DEFAULT CHARSET=latin1; CREATE TABLE `receivers` ( `message_id` int(11) NOT NULL default ‘0’, `receiver_id` int(11) NOT NULL default ‘0’, ) ENGINE=MyISAM DEFAULT CHARSET=latin1; CREATE TABLE `messages` ( `id` int(11) NOT NULL auto\_increment, `message` text NOT NULL, `date` datetime NOT NULL default ‘0000-00-00 00:00:00’, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; but that just seems like a lot of bloat when the original design would work great. It also does not excite me to have to create three tables for what would usually only take one table, especially when the database starts to expand to contain other tables that may have similar circumstances. Thanks in advance. ---
**Author:** ![acgreen](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/3bc359/32.png) [@acgreen](https://www.ruby-forum.com/u/acgreen)\ **Post date:** [April 23, 2006, 1:33am UTC](https://www.ruby-forum.com/t/proper-database-design-for-a-newbie/58550/2 "2006-04-23T01:33:41Z")
Anthony G. wrote: > `date` datetime NOT NULL default ‘0000-00-00 00:00:00’, 0000-00-00 00:00:00 isn’t a valid date or time. Why not just leave it null? -Robby ---
**Author:** ![acgreen](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/3bc359/32.png) [@acgreen](https://www.ruby-forum.com/u/acgreen)\ **Post date:** [April 23, 2006, 2:07am UTC](https://www.ruby-forum.com/t/proper-database-design-for-a-newbie/58550/3 "2006-04-23T02:07:44Z")
well, first things first, use schema.rb and migrations, it’s like SQL without the effort. [http://media.rubyonrails.org/video/migrations.mov](http://media.rubyonrails.org/video/migrations.mov) I think in terms of making that happen all you have to do is something like this: message.sender\_id = user.id message.receiver\_id = other\_user.id however you could do a third table like this: create\_table “senders\_receivers” |t| do t.column “message\_id”, :integer t.column “sender\_id”, :integer t.column “receiver\_id” :integer end and then do the models like that – sender has and belongs to many messages, receiver has and belongs to many messages, message has one sender, message has one receiver. – Giles B. [http://www.gilesgoatboy.org](http://www.gilesgoatboy.org) ---
**Author:** ![acgreen](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/3bc359/32.png) [@acgreen](https://www.ruby-forum.com/u/acgreen)\ **Post date:** [April 23, 2006, 2:14am UTC](https://www.ruby-forum.com/t/proper-database-design-for-a-newbie/58550/4 "2006-04-23T02:14:36Z")
Thanks a lot Giles. I guess we were posting at the same time. > message.sender\_id = user.id > message.receiver\_id = other\_user.id That is exactly what I was looking for. I was having a hard time searching for it on Google and other places. I did watch that video. I guess I still need to get used to the idea of migrations and how they completely work (when you are building a db scheme from scratch). I think I understand the overall concept and how they are supposed to fit into your programming. Anyway, thanks a lot for the help. I really appreciate it. ---
**Author:** ![acgreen](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/3bc359/32.png) [@acgreen](https://www.ruby-forum.com/u/acgreen)\ **Post date:** [April 23, 2006, 2:09am UTC](https://www.ruby-forum.com/t/proper-database-design-for-a-newbie/58550/5 "2006-04-23T02:09:18Z")
It was just something I setup quickly in PHPMyAdmin. Thanks for the tip. What I really need a response to is how to set up the model when you have two fields that both need to point to the same table id. Both the sender\_id and the receiver\_id should point to the users table id (One user will send a message and the other will be the recipient). I am trying to figure out how to set up the model for the messages table without having to extract it out to three tables. Any thoughts? ---
**Author:** ![acgreen](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/3bc359/32.png) [@acgreen](https://www.ruby-forum.com/u/acgreen)\ **Post date:** [April 23, 2006, 2:20am UTC](https://www.ruby-forum.com/t/proper-database-design-for-a-newbie/58550/6 "2006-04-23T02:20:06Z")
On Apr 22, 2006, at 5:05 PM, Giles B. wrote: > well, first things first, use schema.rb and migrations, it’s like SQL > without the effort. > > [http://media.rubyonrails.org/video/migrations.mov](http://media.rubyonrails.org/video/migrations.mov) Yes, and better because it’s DB agnostic and gives you a structured way to make changes over time. > I think in terms of making that happen all you have to do is something > like this: > > message.sender\_id = user.id > message.receiver\_id = other\_user.id or message.sender = user message.receiver = other\_user > sender, message has one receiver. > Nice. This would allow a message to go to more than one receipient. – – Tom M. ---
**Author:** ![acgreen](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/3bc359/32.png) [@acgreen](https://www.ruby-forum.com/u/acgreen)\ **Post date:** [April 23, 2006, 2:16am UTC](https://www.ruby-forum.com/t/proper-database-design-for-a-newbie/58550/7 "2006-04-23T02:16:57Z")
sure, no prob 🙂 On 4/22/06, Anthony G. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > scheme from scratch). I think I understand the overall concept and how – Giles B. [http://www.gilesgoatboy.org](http://www.gilesgoatboy.org) ---
**Author:** ![acgreen](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/3bc359/32.png) [@acgreen](https://www.ruby-forum.com/u/acgreen)\ **Post date:** [April 23, 2006, 3:11am UTC](https://www.ruby-forum.com/t/proper-database-design-for-a-newbie/58550/8 "2006-04-23T03:11:36Z")
What you are really looking for is associations with non-default keys and such. This is actually a lot easier to do that it sounds. Instead of belongs\_to :user you can do belongs\_to :sender, :class\_name =\> “User”, :foreign\_key =\> “sender\_id” I imagine you can extrapolate the correct way to add an association for recipient. There’s documentation on doing this on [api.rubyonrails.org](http://api.rubyonrails.org) as well as in the Agile book (check the section on Active Record).