So I’ve worked through Agile Web D. with Rails and I’m now
trying my first little app to get into the swing of things. Its a task
tracking app where people can create tasks and assign them to others,
and also log time against the tasks.
I’m having trouble working out the model relationships. This is what
I’ve got so far, but its not right as I’m getting lots of errors when I
try to view a task:
class Client < ActiveRecord::Base
has_many :tasks
…
class Log < ActiveRecord::Base
belongs_to :users
belongs_to :tasks
…
class User < ActiveRecord::Base
has_many :tasks
has_and_belongs_to_many :tasks
has_many :logs
has_many :users, :through => :logs
…
class Task < ActiveRecord::Base
belongs_to :client
belongs_to :user
has_and_belongs_to_many :users
has_and_belongs_to_many :parents,
:class_name => “Task”,
:join_table => “parents_task”,
:association_foreign_key => “parent_id”,
:foreign_key => “task_id”
has_many :logs
has_many :users, :through => :logs
…
My db schema is below. If anyone has some pointers for me I’d REALLY
appreciate it.
Thanks
Nick
CREATE TABLE tasks (
id int(4) NOT NULL auto_increment,
parent_id int(4) default NULL, #Parent task
title varchar(100) NOT NULL default ‘’,
description text NOT NULL,
estimated_hours int(4) NOT NULL default ‘0’,
start_on date default NULL,
due_on date NOT NULL default ‘0000-00-00’,
user_id int(4) NOT NULL default ‘0’, #Task owner
client_id int(4) NOT NULL default ‘0’,
type_id int(4) NOT NULL default ‘0’,
urgent tinyint(4) default NULL,
on_hold tinyint(4) default NULL,
percent_complete int(4) NOT NULL default ‘0’,
PRIMARY KEY (id),
KEY parent_id (parent_id),
KEY client_id (client_id),
KEY type_id (type_id),
KEY user_id (user_id)
) TYPE=InnoDB AUTO_INCREMENT=1 ;
#For M:M relationship between tasks and users, for the task assignment
CREATE TABLE tasks_users (
user_id int(4) NOT NULL default ‘0’,
task_id int(4) NOT NULL default ‘0’,
assigned_on datetime NOT NULL default ‘0000-00-00 00:00:00’,
KEY user_id (user_id),
KEY task_id (task_id)
) TYPE=InnoDB;
CREATE TABLE users (
id int(4) NOT NULL auto_increment,
firstname varchar(100) NOT NULL default ‘’,
surname varchar(100) NOT NULL default ‘’,
hashed_password varchar(100) NOT NULL default ‘’,
email varchar(100) NOT NULL default ‘’,
active tinyint(1) NOT NULL default ‘1’,
PRIMARY KEY (id)
) TYPE=InnoDB AUTO_INCREMENT=1 ;
CREATE TABLE logs (
id int(4) NOT NULL auto_increment,
user_id int(4) NOT NULL default ‘0’,
task_id int(4) NOT NULL default ‘0’,
hours decimal(8,2) NOT NULL default ‘0.00’,
worked_on date NOT NULL default ‘0000-00-00’,
logged_at datetime NOT NULL default ‘0000-00-00 00:00:00’,
PRIMARY KEY (id),
KEY user_id (user_id),
KEY task_id (task_id)
) TYPE=InnoDB AUTO_INCREMENT=1 ;
CREATE TABLE task_types (
id int(4) NOT NULL auto_increment,
title varchar(100) NOT NULL default ‘’,
PRIMARY KEY (id)
) TYPE=InnoDB AUTO_INCREMENT=17 ;
CREATE TABLE clients (
id int(4) NOT NULL auto_increment,
name varchar(100) NOT NULL default ‘’,
contact varchar(100) default NULL,
contact_email varchar(100) default NULL,
contact_phone varchar(30) default NULL,
PRIMARY KEY (id)
) TYPE=InnoDB AUTO_INCREMENT=1 ;
Constraints for table logs
ALTER TABLE logs
ADD CONSTRAINT 0_243 FOREIGN KEY (user_id) REFERENCES users
(id),
ADD CONSTRAINT 0_244 FOREIGN KEY (task_id) REFERENCES tasks
(id);
Constraints for table tasks
ALTER TABLE tasks
ADD CONSTRAINT 0_315 FOREIGN KEY (type_id) REFERENCES task_types
(id),
ADD CONSTRAINT 0_307 FOREIGN KEY (parent_id) REFERENCES tasks
(id),
ADD CONSTRAINT 0_309 FOREIGN KEY (user_id) REFERENCES users
(id),
ADD CONSTRAINT 0_312 FOREIGN KEY (client_id) REFERENCES clients
(id);
