Rails - Apr 2006 - database design Q

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!

-- 
Posted via http://www.ruby-forum.com/.

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.