Win a copy of Java 9 Modularity: Patterns and Practices for Developing Maintainable Applications this week in the Java 9 forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

How should I design my database ?  RSS feed

 
tangara goh
Ranch Hand
Posts: 275
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi expert,

I am quite confused what is the correct way to design my database and hope to have some pointers.

Basically, I have a table - tutor and another table - subject.

When a user select the subjects, it will go into the table subject but at the same time I want the tutorName or tutorUniqueIdentifier to be associated with the list of subjects.

Should I create a third table or ?

Here's my table 1: tutor

tutorId
tutorName'
tutorNRIC(PK)
subjCode(FK) ?

table 2 : subject

subjId
subjName
subjCode (PK)
tutorNRIC (FK) should I put this here or I should create a third table ?

Basically, I want to know which tutor can teach what kind of subjects.



 
Jeanne Boyarsky
author & internet detective
Sheriff
Posts: 37384
531
Eclipse IDE Java VI Editor
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes, this is a great example of when you'd want a third table. It allows you to map the n-to-n relationship that you have in your design.
 
tangara goh
Ranch Hand
Posts: 275
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Jeanne Boyarsky wrote:Yes, this is a great example of when you'd want a third table. It allows you to map the n-to-n relationship that you have in your design.


Hi Jeanne,

Can the above achieved what I want as per the attachment ?
database-schema-v3.jpg
[Thumbnail for database-schema-v3.jpg]
 
tangara goh
Ranch Hand
Posts: 275
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sorry but I left out a subj_Id in my tutor_subject table.
 
Dave Tolls
Ranch Foreman
Posts: 2996
37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
tutor_subject only needs to have the tutor_id and the subject_id.
 
Campbell Ritchie
Marshal
Posts: 56195
171
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Why are you calling it tutor_subject? Why not something like teaches? And as DT says, you only require tutor (or tutor_ID) and subject (or subject_ID) in the teaches table.
Had you created a UML diagram before writing those two tables, you would have realised that there is a potential nn relationship:-
 
tangara goh
Ranch Hand
Posts: 275
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dave Tolls wrote:tutor_subject only needs to have the tutor_id and the subject_id.


The thing is that I can't really use subj_id cos it will auto-increment.  And then the insertion will be 1 follows by subjectName, it doesn't appear like what I had shown in my diagram.

I'd like to know if it is possible to have insertion shown in my diagram ?  which is subj 1 and then the subject column will have the subject names appear depending on how many users had ticked ?

Should I take off the auto_increment when I write my sql create table ?

Cos right now, I am using this insert as per below :



Cos with the above qry, the subj_Id will appear with the subjectName side by side
 
Dave Tolls
Ranch Foreman
Posts: 2996
37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Not sure what you mean by being unable to use the subject_id as it auto-increments.

That's not a problem.

You can't assign subjects to a tutor before both the tutor and the subject exist.

So, if your application (whatever it is) allows you to create a tutor and assign new subjects to them, then you have two three types of INSERT to do.
You need to INSERT into the Subject table for each new Subject, storing the ids returned by the PreparedStatement.
Then INSERT into the Tutor table, also storing the id for the new Tutor.
Finally INSERT into the Tutor_Subject table for each Tutor/Subject id pairing.
 
tangara goh
Ranch Hand
Posts: 275
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

I do not assign subjects to tutor.  The tutor or rather the user that register to become a tutor will pick the subjects they would like to teach.

If I am going to use the qry that I have written, I would not be able to achieve the effect that I have drawn in my diagram.

I'd like to know how should I write the insert statement that enable me to achieve the effect in my subject diagram.  Tks.
 
Dave Tolls
Ranch Foreman
Posts: 2996
37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
But your diagram makes no sense as a database model.

And it doesn't matter who pushes the buttons in the GUI, at the end of the processing your code will be assigning subjects to tutors.  And those subjects will have ids.  As will the tutors.
 
Campbell Ritchie
Marshal
Posts: 56195
171
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
tangara goh wrote:. . . The thing is that I can't really use subj_id cos it will auto-increment.
Why not? You autoincrement the ID in the subject table, and use it as if a foreign key in the teaches table.
. . . it doesn't appear like what I had shown in my diagram.
Please explain what you are really doing. If you show us something different from what you are actually doing, you are liable to get misleading advice. Look at this FAQ.
I'd like to know if it is possible to have insertion shown in my diagram ?
So tutor 1 teaches subject 3 and sibject 4, and tutor 2 teaches subject 4 and subject 5? Yes, you can, but I have forgotten how to do it.
. . . the subj_Id will appear with the subjectName side by side
Isn't that what you want, so you can insert a tutor_ID and a subject_ID into the teaches table?
 
tangara goh
Ranch Hand
Posts: 275
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

A question now pop into my mind.

If I go with your suggestion to have sub_id and tutor_id, this third table will store the subject which tutor can teach.

So, I'd have to add a function like a search on this table, is that right ?  Is this the purpose of this table?

Sorry, I am just really bad with database etc cos I did not have a chance to get my hands on more complicated design app till now I decided to try it out.
 
Dave Tolls
Ranch Foreman
Posts: 2996
37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This is where JOINs come into your queries.

Using the 'teaches' table (taking Campbell's name here), if you wanted all the subjects taught by a particular tutor then the following query would work:

SELECT s.*
FROM subject s JOIN teaches t ON s.id = t.sub_id
WHERE t.tut_id = ?;

then you would bind the tutor id to that parameter.
That would then give you a row for each subject that tutor teaches.

If you want to assign a subject to a tutor then:

INSERT INTO teaches
(tut_id, sub_id)
VALUES
(?,?);
 
tangara goh
Ranch Hand
Posts: 275
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dave Tolls wrote:This is where JOINs come into your queries.

Using the 'teaches' table (taking Campbell's name here), if you wanted all the subjects taught by a particular tutor then the following query would work:

SELECT s.*
FROM subject s JOIN teaches t ON s.id = t.sub_id
WHERE t.tut_id = ?;

then you would bind the tutor id to that parameter.
That would then give you a row for each subject that tutor teaches.

If you want to assign a subject to a tutor then:

INSERT INTO teaches
(tut_id, sub_id)
VALUES
(?,?);


Tks Dave.  OK. I get the picture now.  The 3rd table is useful for storing the subjects that the 2nd table - subjects that the tutor has ticked in the form.  And with this teaches table, I will insert the sub_id from the 2nd table and tutor_id from the tutor table.  I hope I got the understanding correct.

Now, another problem I have is that tud_id and sub_id are both inserted via auto-increment.

How do I retrieve the tud_id and sub_id and create this third query ?

Furthermore, I am using DAO and it seems I have to create the attribute id in the tutor and subject now in order to set the parameters.  I am not very sure the how-to or is there a better way to do it ?

 
Dave Tolls
Ranch Foreman
Posts: 2996
37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Don't look at the table as just "finding subjects taught by a tutor"...it works the other way as well.
That's the nature of many-to-many.

For the ids generated, Statement (and so PreparedStatement) provide a getGeneratedKeys method, which returns a ResultSet of the ids generated from the last operation.

So, you would create the tutor (INSERT INTO tutor etc) and, after the executeUpdate you can then get the id:

(that second line might be slightly wrong, but it should be the first column).
 
tangara goh
Ranch Hand
Posts: 275
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dave Tolls wrote:Don't look at the table as just "finding subjects taught by a tutor"...it works the other way as well.
That's the nature of many-to-many.

For the ids generated, Statement (and so PreparedStatement) provide a getGeneratedKeys method, which returns a ResultSet of the ids generated from the last operation.

So, you would create the tutor (INSERT INTO tutor etc) and, after the executeUpdate you can then get the id:

(that second line might be slightly wrong, but it should be the first column).


Hi Dave,

Tks for the hint.

However, I tried very hard but I still do not know how should I get the GeneratedKeys from the return GeneratedKeys from my tutorDAOImpl and subjectDAOImpl and put them into the teachesDAOImpl.

Could you give me more hint how should I go about achieve the above ?

Here's how I do it for my tutorDAOImpl : 


 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!