Forum: Ruby on Rails Proper Database Design For A Newbie

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Anthony G. (Guest)
on 2006-04-23 03:06
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.
Robby R. (Guest)
on 2006-04-23 03:33
(Received via mailing list)
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
Giles B. (Guest)
on 2006-04-23 04:07
(Received via mailing list)
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 B.
http://www.gilesgoatboy.org
Anthony G. (Guest)
on 2006-04-23 04:09
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?
Anthony G. (Guest)
on 2006-04-23 04:14
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.
Giles B. (Guest)
on 2006-04-23 04:16
(Received via mailing list)
sure, no prob :-)

On 4/22/06, Anthony G. <removed_email_address@domain.invalid> wrote:
> scheme from scratch). I think I understand the overall concept and how
>
--
Giles B.
http://www.gilesgoatboy.org
Tom M. (Guest)
on 2006-04-23 04:20
(Received via mailing list)
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

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.
Bryan D. (Guest)
on 2006-04-23 05:11
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).
This topic is locked and can not be replied to.