Need help designing joined table

[Sreyan Chakravarty]

Okay I am new to JPA using Hibernate. I have the following entities in my schema-:

Subject -{ subject_id(PK), subject_name, subject_credits }

Student- { student_id (PK), student_name } 

Now the table I want to generate is that - :

Marks - { student_id (references Student), subject_id (references Subject), internal_no, marks } (Pk is student_id, subject_id, internal_no)

The marks table just implies that a student may have to give multiple internal assessments on one particular subject. Here internal_no cannot be a sequential generated value.

In a normal RDBMS setup you can easily setup the marks table using SQL but how do you do it in case of JPA annotations ?

[Roel De Nijs]

Sreyan Chakravarty wrote:In a normal RDBMS setup you can easily setup the marks table using SQL but how do you do it in case of JPA annotations ?

There is no requirement that JPA should render the database tables. I use JPA in all my projects, but we never let JPA generate the database tables automatically, that's something we do ourselves.

[A.J. Côté]

Roel De Nijs wrote:

Sreyan Chakravarty wrote:In a normal RDBMS setup you can easily setup the marks table using SQL but how do you do it in case of JPA annotations ?

There is no requirement that JPA should render the database tables. I use JPA in all my projects, but we never let JPA generate the database tables automatically, that's something we do ourselves.

Well, I let hibernate modify the schema for me but I test it through several layers right up to prod in case it screws up; then I resort to the old school way to do things when it very rarely does. Most of the times, hibernate flags design errors instead of the other way around.

Also, most of my data update scripts (not schema) are actually java classes with a main method and they use hibernate.

Let me be clear here, it is really OK and to some extend more reassuring to go the old way if you know what you are doing; e.g. dbadmin background. For somebody less familiar with sql update scripts, it might be easier to let hibernate do the job, getting advice if problems are encountered.

In short, I wouldn't want the OP to think the old way is the only way to go.

Cheers,

[A.J. Côté]

Sreyan Chakravarty wrote:Okay I am new to JPA using Hibernate. I have the following entities in my schema-:

Subject -{ subject_id(PK), subject_name, subject_credits }

Student- { student_id (PK), student_name } 

Now the table I want to generate is that - :

Marks - { student_id (references Student), subject_id (references Subject), internal_no, marks } (Pk is student_id, subject_id, internal_no)

The marks table just implies that a student may have to give multiple internal assessments on one particular subject. Here internal_no cannot be a sequential generated value.

In a normal RDBMS setup you can easily setup the marks table using SQL but how do you do it in case of JPA annotations ?

Hello,

I am just an old fart that has been using hibernate since it existed, quite a while before JPA existed.

I am not familiar with the annotations you list in your post.

Anyway, your problem sounds easy enough to solve but I haven't got time to learn yet another annotation syntax.

I use hibernate specific annotations at the cost of portability with other persistence engines. Evaluate if you need that portability in your project.

I used to use xml config files with hibernate but I finally moved to annotations lately. They just don't look like yours.

Anyway, just reply with a link to the reference for the annotations you use, maybe I could help you if I figured it out quickly. Still, keep in mind that hibernate existed long before JPA and that it is more user friendly and provides more functionality at the cost of less portability with other persistence engines.

Cheers,

[Sreyan Chakravarty]

Roel De Nijs wrote:

Sreyan Chakravarty wrote:In a normal RDBMS setup you can easily setup the marks table using SQL but how do you do it in case of JPA annotations ?

There is no requirement that JPA should render the database tables. I use JPA in all my projects, but we never let JPA generate the database tables automatically, that's something we do ourselves.

I am sorry what do you mean by you never let Hibernate generate your tables ? Don't you have to create entities and define there mappings ? If you don't let Hibernate or JPA generate your tables how do you work with it ? I am sorry I am confused.

[Sreyan Chakravarty]

A.J. Côté wrote:

Sreyan Chakravarty wrote:Okay I am new to JPA using Hibernate. I have the following entities in my schema-:

Subject -{ subject_id(PK), subject_name, subject_credits }

Student- { student_id (PK), student_name } 

Now the table I want to generate is that - :

Marks - { student_id (references Student), subject_id (references Subject), internal_no, marks } (Pk is student_id, subject_id, internal_no)

The marks table just implies that a student may have to give multiple internal assessments on one particular subject. Here internal_no cannot be a sequential generated value.

In a normal RDBMS setup you can easily setup the marks table using SQL but how do you do it in case of JPA annotations ?

Hello,

I am just an old fart that has been using hibernate since it existed, quite a while before JPA existed.

I am not familiar with the annotations you list in your post.

Anyway, your problem sounds easy enough to solve but I haven't got time to learn yet another annotation syntax.

I use hibernate specific annotations at the cost of portability with other persistence engines. Evaluate if you need that portability in your project.

I used to use xml config files with hibernate but I finally moved to annotations lately. They just don't look like yours.

Anyway, just reply with a link to the reference for the annotations you use, maybe I could help you if I figured it out quickly. Still, keep in mind that hibernate existed long before JPA and that it is more user friendly and provides more functionality at the cost of less portability with other persistence engines.

Cheers,

Just tell me something. Can an Entity in Hibernate have two Foreign Keys from two different tables as many to one mapping. And then can those two foreign keys be taken together to form a composite primary key ?

[Roel De Nijs]

Sreyan Chakravarty wrote:I am sorry what do you mean by you never let Hibernate generate your tables ? Don't you have to create entities and define there mappings ? If you don't let Hibernate or JPA generate your tables how do you work with it ? I am sorry I am confused.

I create my entities and use JPA annotations like @Entity, @Column and @OneToMany to add all necessary mappings. But I don't let Hibernate generate the DML statements to create the tables, I write the DML statements myself. In fact, most of the time I create my database tables before I create my JPA entities

[Roel De Nijs]

A.J. Côté wrote:I used to use xml config files with hibernate but I finally moved to annotations lately. They just don't look like yours.

I don't see any annotations in the OP, just a description of the tables and their columns. So not sure to which annotations you are refering

[Roel De Nijs]

Sreyan Chakravarty wrote:Just tell me something. Can an Entity in Hibernate have two Foreign Keys from two different tables as many to one mapping. And then can those two foreign keys be taken together to form a composite primary key ?

That's not a problem at all for JPA (and Hibernate).

One of the best available resources is the Java Persistence WikiBook. Whenever I have doubts or looking for an example of a particular mapping, I check this (free) resource first!

You want (need) a composite primary key, so you should look at the Embedded Id section. Composite (primary) keys can get tricky, so I would only use them as a last resort.

What's internal_no? It sounds like some key as well? Is it the key of the Student or Subject? Or is it the key for the exam you are storing the marks for?

[Sreyan Chakravarty]

Roel De Nijs wrote:

Sreyan Chakravarty wrote:Just tell me something. Can an Entity in Hibernate have two Foreign Keys from two different tables as many to one mapping. And then can those two foreign keys be taken together to form a composite primary key ?

That's not a problem at all for JPA (and Hibernate).

One of the best available resources is the Java Persistence WikiBook. Whenever I have doubts or looking for an example of a particular mapping, I check this (free) resource first!

You want (need) a composite primary key, so you should look at the Embedded Id section. Composite (primary) keys can get tricky, so I would only use them as a last resort.

What's internal_no? It sounds like some key as well? Is it the key of the Student or Subject? Or is it the key for the exam you are storing the marks for?

Okay let me elaborate on what I want.

There is a Student table which contains all Student data.

Then there is a Subject table which contains all Subject data.

Now one subject can be studied by many students and one student can study many subjects. So its a many to many relationship. Correct me if I am wrong.

Now there can be multiple internal exams that a Student might have to take, thus the number of internals are not fixed, hence internal_no. So the Marks table looks something like-:

Marks {Student_id, Subject_id, internal_no, marks} (Here Student_id, Subject_id, internal_no is the composite primary key)

Now what is confusing me is this. If Student and Subjects share a many to many relationship then what relationship should hold in the marks table ? Since both Student_id and Subject_id are referenced from Student and Subject table respectively.

The most sensible way is doing a many to one relationship but doesn't that break the many to many relationship already there ?

I just want to know what will be right.

[Roel De Nijs]

Sreyan Chakravarty wrote:Now one subject can be studied by many students and one student can study many subjects. So its a many to many relationship. Correct me if I am wrong.

You are correct!

Sreyan Chakravarty wrote:Now there can be multiple internal exams that a Student might have to take, thus the number of internals are not fixed, hence internal_no.

For each Subject there are an undefined number of internal exams. So for subject A there could be just one exam, but for subject B there are 5 exams.

So you could have an Exam table { exam_id, internal_no, subject_id } and then the Marks table would be something like { student_id, exam_id, marks }. But that would require an extra join each time you want to query the marks, for example on a specific subject for all students (or one specific student).

Sreyan Chakravarty wrote:Now what is confusing me is this. If Student and Subjects share a many to many relationship then what relationship should hold in the marks table ? Since both Student_id and Subject_id are referenced from Student and Subject table respectively.

You'll need a JoinTable with additional columns. And table Marks will be this join table; Student_id, Subject_id, internal_no will be the composite primary key; and the marks column will be the additional column.

[Sreyan Chakravarty]

Roel De Nijs wrote:

Sreyan Chakravarty wrote:Now one subject can be studied by many students and one student can study many subjects. So its a many to many relationship. Correct me if I am wrong.

You are correct!

Sreyan Chakravarty wrote:Now there can be multiple internal exams that a Student might have to take, thus the number of internals are not fixed, hence internal_no.

For each Subject there are an undefined number of internal exams. So for subject A there could be just one exam, but for subject B there are 5 exams.

So you could have an Exam table { exam_id, internal_no, subject_id } and then the Marks table would be something like { student_id, exam_id, marks }. But that would require an extra join each time you want to query the marks, for example on a specific subject for all students (or one specific student).

Sreyan Chakravarty wrote:Now what is confusing me is this. If Student and Subjects share a many to many relationship then what relationship should hold in the marks table ? Since both Student_id and Subject_id are referenced from Student and Subject table respectively.

You'll need a JoinTable with additional columns. And table Marks will be this join table; Student_id, Subject_id, internal_no will be the composite primary key; and the marks column will be the additional column.

Okay. I have seen the link that you have referred me to.

Now it says basically to model the Join Table as a separate entity by decomposing all the many-to-many relationships into one-to-many and many-to-one relationships. Now my question is that - doesn't this decomposing into one-to-many and many-to-one destroy the conceptual mapping of many-to-many ?

I mean is one-to-many and many-to-one really the same thing ? Doesn't that destroy the original data model ?

[Roel De Nijs]

Sreyan Chakravarty wrote:Now it says basically to model the Join Table as a separate entity by decomposing all the many-to-many relationships into one-to-many and many-to-one relationships. Now my question is that - doesn't this decomposing into one-to-many and many-to-one destroy the conceptual mapping of many-to-many ?

I mean is one-to-many and many-to-one really the same thing ? Doesn't that destroy the original data model ?

If you don't need additional columns, you can use the @ManyToMany annotation. But if you need additional columns and it seems your data model requires them, so as far as I know there's no other solution than the one described in the link I provided. I don't see any problem with this approach.

[Sreyan Chakravarty]

Roel De Nijs wrote:

Sreyan Chakravarty wrote:Now it says basically to model the Join Table as a separate entity by decomposing all the many-to-many relationships into one-to-many and many-to-one relationships. Now my question is that - doesn't this decomposing into one-to-many and many-to-one destroy the conceptual mapping of many-to-many ?

I mean is one-to-many and many-to-one really the same thing ? Doesn't that destroy the original data model ?

If you don't need additional columns, you can use the @ManyToMany annotation. But if you need additional columns and it seems your data model requires them, so as far as I know there's no other solution than the one described in the link I provided. I don't see any problem with this approach.

I understand. But my question was that is Many-to-one and One-to-Many conceptually same as Many-to-Many ?

[Roel De Nijs]

Sreyan Chakravarty wrote:But my question was that is Many-to-one and One-to-Many conceptually same as Many-to-Many ?

Why wouldn't it be?