[FIXED] Enabling manual sorting for entities

Issue

I have two entities, "Course" and "Person".

1) One or more Persons are responsible for a course

2) A course can contain of other courses.

Now I want to realize the ability to add a manual sort sequence for both, the responsible persons and the contained courses.

That means, it should be able to display the courses like this:

Course 1:
  Responsible Persons:
   1. Person A
   2. Person B
   3. Person C

Course 2:
  Responsible Persons:
   1. Person B
   2. Person A
   3. Person C

And this order can be declared when creating a Course. But there is nothing like a role-concept for different users, who are using the application. So to say, there will only be "one" user and nothing like a User class.

As you can see in the diagram below. I thought of two new classes called "SortableCourse" and "SortablePerson" which contain an additional ID for the sorting.

1) Is this a good approach?

2) I will use Java and Hibernate for the OR-Mapping:

2.1) Do I miss something, that could cause problems later on with the mapping / creation of the tables?

2.2) Could it be a problem, that Person has a List of Courses, but the Course has a list of SortablePersons? Because from the database-view I guess, it would be enough to have a table with Course IDs and SortablePerson IDs. Does anyone have experience how Hibernate will "translate" this?

Hope my idea got clear, thanks in advance for any hints!

Solution

Instead of creating a table layout or UML diagram lets look what JPA is able to do:

@Entity
public class Course {
    ...
    @OneToMany
    @OrderColumn
    private List<Person> responsiblePersons;

    @OneToMany
    @OrderColumn
    private List<Course> consistsOf;
}

@Entity
public class Person {
    ...
    @OneToMany(mappedBy = "responsiblePerson")
    private List<Course> responsibleFor;
}

The important thing is the List with the @OrderColumn declaration which indicates that the mapping has a stable order.

This will create four tables:

COURSE
------
id, ...

PERSON
------
id, ...

COURSE_PERSON
------
course_id, responsiblePersons_id, responsiblePersons_order

COURSE_COURSE
------
course_id, consistsOf_id, consistsOf_order

So no need to invent some "wrapper" entities.

Answered By - Tobias Liefke
Answer Checked By - David Marino (JavaFixing Volunteer)