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. -- Posted via http://www.ruby-forum.com/.
Anthony Graddy 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
well, first things first, use schema.rb and migrations, it''s like SQL without the effort. 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 Bowkett http://www.gilesgoatboy.org On 4/22/06, Anthony Graddy <anthonygraddy@yahoo.com> wrote:> 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. > > -- > Posted via http://www.ruby-forum.com/. > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
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? -- Posted via http://www.ruby-forum.com/.
Thanks a lot Giles. I guess we were posting at the same time.> message.sender_id = user.id > message.receiver_id = other_user.idThat 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. -- Posted via http://www.ruby-forum.com/.
sure, no prob :-) On 4/22/06, Anthony Graddy <anthonygraddy@yahoo.com> wrote:> 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. > > -- > Posted via http://www.ruby-forum.com/. > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-- Giles Bowkett http://www.gilesgoatboy.org
On Apr 22, 2006, at 5:05 PM, Giles Bowkett wrote:> well, first things first, use schema.rb and migrations, it''s like SQL > without the effort. > > http://media.rubyonrails.org/video/migrations.movYes, 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.idor message.sender = user message.receiver = other_user> 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.Nice. This would allow a message to go to more than one receipient. -- -- Tom Mornini
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 as well as in the Agile book (check the section on Active Record). -- Posted via http://www.ruby-forum.com/.