How do I model a Java object that holds instances of itself in a MySQL database?

Question

I have a webapp that has an object that contains a collection of itself. I am struggling with how to model this in a MySQL database. It is a Task object that contains a list of subtasks. Here is what the class looks like:

Task.java

public class Task{ 
    private int taskID;
    private String name;
    private String details;
    private List<Task> subTasks
    ...other properties

    public Task(int taskID, String name, String details){
        this.taskID = taskID;
        this.name = name;
        this.details = details;
        this.subTasks = new ArrayList<>();
    }

    Getters and Setters here...
}

Through some research and thinking it through on my own I have come up with one possible solution. I have an image of a UML diagram of it but can't post it since I just joined and my SO rep isn't high enough yet. So I did my best to show it with characters below:

---------------------         -------------------
|       Task        |         |    SubTaskMap   |
--------------------- 1   __∞_|-----------------
| int taskID        |----|__∞_|int subTaskID    |
| String taskName   |         |int parentTaskID |
| String taskDetail |         -------------------
--------------------

So both subTaskID and parentTaskID are primary keys in the table SubTaskMap as well as foreign keys to taskID in the table Task (again, all of them are for Task objects in Java). From what I can tell, to run queries to find all tasks associated with a parentTaskID on this setup I would have to create an alias of the Task table.

I modeled this all in MS Access and for a query to get all Tasks associated with a parentTaskID of 5, I designated a join between Task.taskID with SubTaskMap.subTaskID and another join between an alias table Task_Copy and SubTaskMap that linked Task_copy.taskID with SubTaskMap.parentTaskID got a SQL query like this:

SELECT Task.taskName, SubTask.parentTaskID
FROM Task AS Task_Copy INNER JOIN (Task INNER JOIN SubTask ON Task.taskID = SubTask.subTaskID) ON Task_Copy.taskID = SubTask.parentTaskID
WHERE (((SubTask.parentTaskID)=5));

So is this a good way to go about solving this problem or is there a better way?

Thanks for any help!

score 2 · Accepted Answer · edited May 23 '17 at 11:44

2

I think you have a case of the Composite pattern. Look at this post: What are the options for storing hierarchical data in a relational database? . I would go with the Adjacency List:

Add field ParentTaskId to your table, and do away with the SubTaskMap table completely.

edited May 23 '17 at 11:44

Community

1
1

answered Sep 10 '15 at 18:32

chrisl08

1,658
1
15
24

Ah, yes. That makes sense. Thanks. I clearly over complicated it. I'll also look into those patterns you linked. – arcademonkey Sep 10 '15 at 19:00

score 1 · Answer 2 · answered Sep 10 '15 at 18:31

If a sub task can have multiple parents, then you have a many-to-many relationship between the two instance. In this case your solution is the right one (aside the naming convention).

If your task has only one parent task, then you have a one-to-many relationship. In this case, you store your reference to the parent object in the child object. So your Task would have a parent field and also a subTasks list. This is of course easier to operate using an ORM framework.

The main difference is that with the first you have a separate table to hold the associations but with the second you can hold your reference in the rows of the same table (Task).

In a m-m relationship you don't have a parent-child connection.

Cool, that is a helpful clarification. Out of curiosity, what would you change about the naming convention? — arcademonkey, Sep 10 '15 at 19:02
A grate discussion about the topic: http://stackoverflow.com/questions/1813321/what-should-i-name-a-table-that-maps-two-tables-together . In your case I wouldn't use Task_Task, but something that describes your domain better. — Ákos Ratku, Sep 21 '15 at 17:06

score 0 · Answer 3 · answered Sep 10 '15 at 18:45

Like mentioned above if there can be only one parent of a sub-task, you can add an extra column to your task table and track the parent_task_id there. If it is the parent, then the column will have no value.

If one task can have multiple parents, then you will need a different table to maintain that relationship.

How do I model a Java object that holds instances of itself in a MySQL database?

3 Answers3