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.

  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!

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.

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs