Database design Q

Rails
1

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.

  1. 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?

  2. 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?

  3. 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!

2

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.

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