Proper Database Design For A Newbie

Rails
1

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.

2

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

3

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

4

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.

5

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?

6

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.

7

sure, no prob :slight_smile:

On 4/22/06, Anthony G. [email protected] wrote:

scheme from scratch). I think I understand the overall concept and how

–
Giles B.
http://www.gilesgoatboy.org

8

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).