Forum: Ruby on Rails database design Q

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.
Liang G. (Guest)
on 2006-04-17 07:28
I am in the middle of setting up mysql database for my RoR application.

and here is some questions in my mind hope you can help.

1. this is a photoblog story telling like system, where people can creat
their own groups
the database tables are: people, groups, articls, pictures
People to Groups are many-to-many relationships
People to Articles are one-to-many
Group to Articles are one-to-many
Articles to Pictures are one-to-many

My question is: will this design work? when articles belong to both
people and group.

2. I want to track user's browsing history, like with
groups/articls/people page s he/she clicked/viewed in the past 3 month
or so. Any suggestions how to design it nicely with database without
adding a lot of tables?

3. What is the best way to store pictures, in a directory or in database
at a Blob? which way has better performance in the long run?

4. I want to tag every table in my database, what is a good way to
design tags in the database? should I have a seperate tags table, or
build a tag column in every table?

Thanks!
Grant Neufeld (Guest)
on 2006-04-17 09:51
(Received via mailing list)
At 5:28 AM +0200 4/17/06, liang gao wrote:
>1. this is a photoblog story telling like system, where people can
>create their own groups.
>the database tables are: people, groups, articls, pictures.
>People to Groups are many-to-many relationships
>People to Articles are one-to-many
>Group to Articles are one-to-many
>Articles to Pictures are one-to-many

The one thing you're missing in the above list is a join table to do
the many-to-many relationship for people and groups.

CREATE TABLE IF NOT EXISTS person_group (
	id int UNSIGNED PRIMARY KEY AUTO_INCREMENT,
	person_id int,
	group_id int,
	INDEX ( person_id, group_id ),
	INDEX ( group_id, person_id )
)	COMMENT = "Join people to groups.";

CREATE TABLE IF NOT EXISTS people (
	id int UNSIGNED PRIMARY KEY AUTO_INCREMENT,
	name char(255),
	# whatever other fields you want.
	INDEX ( name )
)	COMMENT = "Users of the system.";

CREATE TABLE IF NOT EXISTS groups (
	id int UNSIGNED PRIMARY KEY AUTO_INCREMENT,
	groupname char(255),
	# whatever other fields you want.
	INDEX ( groupname )
)	COMMENT = "Groupings of users, articles, etc.";

CREATE TABLE IF NOT EXISTS articles (
	id int UNSIGNED PRIMARY KEY AUTO_INCREMENT,
	person_id int,
	group_id int,
	title char(255),
	content text,
	# whatever other fields you want.
	INDEX ( person_id, group_id ),
	INDEX ( group_id, person_id ),
	INDEX ( title )
)	COMMENT = "An article.";

CREATE TABLE IF NOT EXISTS pictures (
	id int UNSIGNED PRIMARY KEY AUTO_INCREMENT,
	article_id int,
	# whatever other fields you want.
	INDEX ( article_id )
)	COMMENT = "A picture for an article.";

In your Person class (model/person.rb) you'll need something like:
	has_and_belongs_to_many :groups, :join_table => 'person_group',
		:foreign_key => 'person_id',
		:association_foreign_key => 'group_id'
And in your Group class (model/group.rb):
	has_and_belongs_to_many :people, :join_table => 'person_group',
		:foreign_key => 'group_id',
		:association_foreign_key => 'person_id'

>My question is: will this design work? when articles belong to both
>people and group.

Yes.


>3. What is the best way to store pictures, in a directory or in
>database at a Blob? which way has better performance in the long run?

That can vary depending on your setup. Ideally, keep a separate
server just for serving static files and optimize your web server
application for that content. If you're running everything off one
server, I recommend doing some profile tests of the different options
to see what actually works best in your setup.

In general, you can probably expect the filesystem to be faster than
a database, unless you're caching the database results so you don't
have to do queries every time the data needs to be accessed.
This topic is locked and can not be replied to.