Page 1 of 1

How do I write the insert sql statement for this scenario ?

PostPosted: Sun Jun 04, 2017 5:46 pm
by karengsh
Dear expert,

I have just started out working on database design and I'm not sure how to write the insert sql uinsg MySQL for this case :

I am trying to create 2 tables with a one-to-many relationship.
Table 1 - tutor
Table 2 - subject

In my table tutor, I have id as the Primary key and it is auto-incremental.

In my subject, I have id as the Primary key and it is auto-incremental, tutorNRIC as the foreign key and tutorNRIC in my tutor table as the reference key.

For my subject, there will be more than one entry in the column tutor_subject.

For example, a tutor will sign up and then all the subjects that she can teach will be entered into that column tutor_subjet.

I'd like to check if my insert sql is correct :
Code: Select all   Expand viewCollapse view
private final String INSERT_QRY1 = "INSERT INTO hi5project.subject tutor_subject, tutorNRIC VALUES ('?', '?') SET tutorNRIC = hi5project.tutor.tutorNRIC";


Thanks

Re: How do I write the insert sql statement for this scenari

PostPosted: Sun Jun 04, 2017 7:09 pm
by Villeroy
That would be a many-to-many relation where multiple subjects may belong to multiple tutors.
Many examples in this subforum have many-to-many relations.
In my [Example] Relations reflected by list boxes in forms the relation between "Persons" and "Things" is many-to-many. Such relation always requires a mapping table on database level and tricky subforms for editing (a subform grid with a list box of subitems).

Re: How do I write the insert sql statement for this scenari

PostPosted: Mon Jun 05, 2017 6:46 am
by karengsh
Hi Villeroy,

I went into the subforum but I have to open each of the thread to read and I can't find any many to many example. Could you point me one or two that is good for me ?
Your example is rather complicated for me at this stage.

Furthermore, I have attached my database schema.

Initially, I wanted to put subject into the tutor table, just to make coding easier.

I was advised against it.

So, I'd like to confirm if I would need one more join table in between the subject and tutor table for my case ?

Re: How do I write the insert sql statement for this scenari

PostPosted: Mon Jun 05, 2017 10:14 am
by Villeroy
The second link points to a small tutorial of mine with an attached database document. Any differences between HSQL and MySQL are not relevant here.
https://en.wikipedia.org/wiki/Many-to-many_(data_model)
First of all you need a third table mapping subject-IDs to tutor-IDs.
OpenOffice/LibreOffice provides the necessary form elements to edit this relation type so you will be able to add/remove/edit subjects belonging to tutors and tutors belonging to subjects.