I want to implement database for following application:-

User creates task and assigns to other user who can furthur split it into subtasks and assign it to other users.creaking a tree like structure.

Should i use graph database or what?


You could use a graph database, or a plain old relational one:

/* set parent_id for child tasks */

create table task (
  id serial primary key,
  parent_id integer null references task(id),
  subject text not null,
  body text null,
  due_at date null

create table task_role_type(
  id smallint primary key,
  name text not null

insert into task_role_type values 
(1, 'Created By'),
(2, 'Assigned To'),
(3, 'Completed By');

create table task_role (
  task_id integer not null references task(id),
  party_id integer not null references party(id),
  role_id smallint not null references task_role_type(id),
  from_date timestamp not null default current_timestamp,
  to_date timestamp null,
  primary key (task_id, party_id, role_id, from_date)

Create a task:

insert into task (subject, due_at) values ('do stuff', '2013-02-18');

insert into task_role (task_id, party_id, role_id) values (1,1,1);

Assign a task:

insert into task_role (task_id, party_id, role_id) values (1, 2, 2);

Split up a task:

insert into task (parent_id, subject) values (1, 'do stuff B');
insert into task_role (task_id, party_id, role_id) values (2, 1, 1);

Complete a task:

insert into task_role (task_id, party_id, role_id) values (1, 2, 3);

I would recommend PostgreSQL, as it can handle hierarchical data better than MySQL

  • Thanks this helped how to do things in sql.but are there any advantages of using graph over sql or sql over graph database? – Rishikesh Feb 18 '13 at 12:07

In terms of SQL vs Graph databases, assuming your database can handle hierarchies well....

The major tradeoff is this schema flexibility vs reporting flexibility (it's more complicated than that, but that gives you an idea).

Basically SQL dbs usually[1] operate over sets of tuples. Graph dbs usually operate over individual entities. Aggregation reporting is usually managed using MapReduce. SQL aggregation reporting tends to be done using well understood approaches (and on some clustered databases these have some similarity to MapReduce, but significant differences as well).

The big difference typically is in reporting. In general your reporting will be much easier on SQL. In general if you need or may need ad hoc reporting, SQL is the way to go. If you know for a fact that you never will, NoSQL, including graph dbs may be the way to go.

The best way to go IMO is to start with PostgreSQL with the idea that if you need to add a graph db as an auxiliary, you can do so. Many of these dbs have a great deal of utility when used as adjuncts to the RDBMS, but if you start with the RDBMS, it will be easier to add the other than it will be to go the other way around.

[1] Informix is an exception, as it operates over sets of objects. This leads to interesting results when child tables have more columns than parent tables. In that case, the rows of child tables will be returned when the parent table is queried using select *, but with the additional fields, meaning that different rows may have different fields and field semantics.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.