Trying to model my database

[Vasilis Souvatzis]

Good morning everyone, the title is way too generic but so is my problem; I'm trying to create my database but am unsure how to structure it, I haven't done any serious databases so far. Let me give you a rough idea.

My thesis project resembles Udemy, Udacity etc. A User can register to a number of Courses and a Course has a number of Lectures. Of course, many users can register to the same Course.

So in the User entity, there would be a List<Course> and in the Course entity a List<Lecture>. That's as much I know so far.

Should I use Join tables so I don't duplicate data? Should I just use foreign keys? Maybe a Join table for Users-Courses and foreign keys for Courses-Lectures?

Any help would be greatly appreciated, last time I worked with databases was lots of semesters ago in university and really can't remember much...

[K. Tsang]

Is coding needed or just pure database design?

Is the system a transactional (OLTP) or analytical (OLAP) (eg data warehouse)?

For OLTP using foreign keys to map lectures and courses is a pretty good approach.

User table
userId, userName, courseId

Course table
courseId, courseName

Lecture table
lectureId, lectureTopic, courseId

The down side (not really a problem) I see with this approach is data will grow exponentially (very fast) because the relationship is 1-to-many
User table data would look like:
userId,userName,courseId
1,'John Doe',1
1,'John Doe',2
1,'John Doe',3

User John Doe (id=1) registers 3 courses (courseId 1,2,3).

Similarly for the course and lecture tables.

You also want to think about what the "de-normalized" raw data would look like. Then start from there normalizing it to suit the design.

[Vasilis Souvatzis]

Certainly not just database design, coding will be needed in JavaEE. NetBeans will generate the entities for me, that's why I want to have the DB designed properly.

I'm afraid I don't know what OLTP or OLAP is. But my thesis project will be small, around 10 test users, another 10 courses and some lectures, nothing serious. I mean for the size of my app, a single table with all the data would suffice anyway. It's too "dumb" though.

Also, denormalization isn't a factor really because again my data will be too little.

I guess I should probably make it with the notion of a "professional" application, designing the DB isn't in my thesis' topic though. I assume a simple design would be enough.

Thanks a lot for your help, we messed with DB relationships at university but not within an application scope.

[Vasilis Souvatzis]

I've put some more thought into it and have come up with this sketch:


Now, I'm throwing this on the table for a couple of reasons, my Users table is the one Glassfish uses to authenticate users upon login - so I can't have duplicates. Also, when a User un-enrolls from a course I'll only have to delete the record from the UserCourse table.

Thoughts on this approach?

[K. Tsang]

I see. Then to keep it really simple, your current approach will work. User table, Course table, UserCourse table.

You should also consider having a CourseLecture table storing the courseId and lectureId similar to the purpose of the UserCourse table. This way there won't need to be a courseId field in the Lecture table.

[Vasilis Souvatzis]

Hello again Mr. Tsang. I'm trying to set up my entities' relationships and I've ran into an issue. I don't think I've understood the mapping quite well, any clarification would be really helpful.

Let's take the Course - Lectures relationship. It's a unidirectional one, meaning that the Course is the owner of the relationship. So, the Course entity must have a List<Lectures> field and a @OneToMany(mappedBy="course").

The Lectures entity is a @ManyToOne relationship and the JoinTable is the CourseLectures one. Also, it should have a Courses course field based on this.

My entities are following:

public class Courses {
    @JoinTable(name = "COURSELECTURES", joinColumns = {
        @JoinColumn(name = "COURSE_ID", referencedColumnName = "ID")}, inverseJoinColumns = {
        @JoinColumn(name = "LECTURE_ID", referencedColumnName = "ID")})
    @OneToMany(mappedBy="course")
    private List<Lectures> lecturesList;
}

public class Lectures {
    @ManyToOne
    @JoinTable(name="COURSELECTURES")
    private Courses course;
}

Judging by the link I've given you it's the exact same case, only the Courses=Groups and Lectures=Users.

In my CoursesFacade class, I'm trying to return the Lectures that correspond to a given Course name. When I do select lec from Lectures lec where lec.course.title = :cname it crashes. Should this query be in the LecturesFacade and not in the CoursesFacade?

I thought I had understood the mapping after reading the specific chapter on "Beginning JavaEE7" but I guess I hadn't. Care to enlighten me?

[Vasilis Souvatzis]

I think I improved my mapping. By how much I don't know, they seem to work a bit though.

When changing to:

public class Lectures {
    @JoinTable(name = "COURSELECTURES", joinColumns = {
        @JoinColumn(name = "COURSE_ID", referencedColumnName = "ID")}, inverseJoinColumns = {
        @JoinColumn(name = "LECTURE_ID", referencedColumnName = "ID")})
    @ManyToOne
    private Courses course;
}

public class Courses {
    @JoinTable(name = "COURSELECTURES", joinColumns = {
        @JoinColumn(name = "COURSE_ID", referencedColumnName = "ID")}, inverseJoinColumns = {
        @JoinColumn(name = "LECTURE_ID", referencedColumnName = "ID")})
    @OneToMany
    private List<Lectures> lecturesList;
}

and running in the CourseFacade class select lec from Lectures lec join lec.course c where c.title = :cname it does return a single result. It should be more than one but still, I guess it's an improvement.

I could certainly use Native Queries and save myself from all this trouble (i tested it and works), but I'd rather have my mapping right and use entities' relationships.

[Vasilis Souvatzis]

I ended up changing my DB really, I couldn't get it to work properly.

I believe I read somewhere that Unidirectional mapping with Join tables isn't usual. So to save me any further headaches i changed it to Bidirectional, the Lectures table now holds the foreign key of the Courses and I'm good. Everything works just fine.

[Vasilis Souvatzis]

And I'm back... This time, the ManyToMany association is problematic. Most probably I'm doing something wrong but I can't figure out what. I'm following this guide.

Can you notice anything out of the ordinary in my code?

public class Course implements Serializable {

    @OneToMany(cascade = CascadeType.ALL, mappedBy = "courseid")
    private List<Lecture> lecturesList;
    
    @JoinTable(name="USERCOURSES", 
            joinColumns = { @JoinColumn(name = "COURSEID", referencedColumnName = "IDCOURSE") },
            inverseJoinColumns = { @JoinColumn(name = "USERID", referencedColumnName = "ID") })
    @ManyToMany
    private List<User> users;
}

public class User implements Serializable {
    
    @ManyToMany(mappedBy = "users")
    private List<Course> courses;

public class Lecture implements Serializable {
    
    @JoinColumn(name = "COURSEID", referencedColumnName = "IDCOURSE")
    @ManyToOne(optional = false)
    private Course courseid;

The Course - Lecture mapping works just fine. For the Course - User association, I've created a Join Table and entered the foreign keys. I'm following the tutorial I've linked above and I think I've done the mapping correctly...

[Vasilis Souvatzis]

Hmmm, it seems my mapping has absolutely no problems. Today I thought of completing the code to merge users and courses. It worked just fine. The EntityManager writes only to the Join Table and everything is OK.

I guess the problem was in the Netbeans' JPQL editor, it seems it freaks out every now and then, i don't know.

I'll have to check whether the relationships work in a retrieving manner but, I think this whole issue is over.