• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Trying to model my database

 
Vasilis Souvatzis
Ranch Hand
Posts: 81
1
Chrome Java Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Bartender
Posts: 3583
16
Android Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 81
1
Chrome Java Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 81
1
Chrome Java Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Bartender
Posts: 3583
16
Android Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 81
1
Chrome Java Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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:


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
Ranch Hand
Posts: 81
1
Chrome Java Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I think I improved my mapping. By how much I don't know, they seem to work a bit though.

When changing to:


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
Ranch Hand
Posts: 81
1
Chrome Java Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 81
1
Chrome Java Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?





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
Ranch Hand
Posts: 81
1
Chrome Java Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic