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

dBase, Calc, CSV, MS ACCESS, MySQL, PostgrSQL, OTHER
Post Reply
karengsh
Posts: 10
Joined: Sun Jun 04, 2017 5:36 pm

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

Post 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

private final String INSERT_QRY1 = "INSERT INTO hi5project.subject tutor_subject, tutorNRIC VALUES ('?', '?') SET tutorNRIC = hi5project.tutor.tutorNRIC";
Thanks
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Post 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).
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
karengsh
Posts: 10
Joined: Sun Jun 04, 2017 5:36 pm

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

Post 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 ?
Attachments
one to many-resize.jpg
OpenOffice 3.1 on Windows Vista
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Post 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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply