• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

MySQL insert logic

 
Greenhorn
Posts: 12
Eclipse IDE MySQL Database C++
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hello,

I have a little bit logic confusion here, So im new to Joins and kindly find it hard to circulate on the 4 types..

But I need practical explanation..

So for example I have 3 table

Student
student_id | student_name | course

Subject
subject_id | subject_name | subject_time | subject_time2 | subject_day| teacher_id

Teacher
teacher_id | teacher_name | department

Scheduling
sid | student_id | subject_id | teacher_id

the problem is insertion.

Suppose Teacher 3 will insert a subject
my question is How could I insert the subject in the same time, also insert in scheduling.


P.S One thing! is my relations alright? What I really intended on this is    
>each Teachers can register their subjects with definite time (and no conflict in time)
>and then assign students to those subjects
>So if the Student log in they can see their subjects with their designated teacher.

current questions
>Assigning students to a single subjects logic
>And assigning those subjects to their specific teacher logic

P.P.S I am first building database relation, and prioritizing queries before GUI so that I will not be more confused

Critization is alright, because I know im noob :'(
 
Marshal
Posts: 28193
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
But inserts have nothing to do with joins. You can't insert into the result of a query involving a join. If you want to insert into two tables, then two inserts are required -- one for each table. You should consider creating a transaction to contain the two inserts, so that you don't end up with inconsistent data because only one insert was done and the other insert failed for some reason.

As for your Scheduling table: if it has a foreign key relating it to the Subject table, as it appears to do, then its "teacher_id" column is redundant. The Subject table already has that column.

Likewise I don't understand why the Student table has a "course" column. But this time the problem is that I don't understand what "course" means there. To me a Course is something like "Philosophy 403 (First-order Predicate Logic)" and such a thing wouldn't belong in a Student record. The Student could certainly be registered for that course but then you'd have another table to record that fact, since Student::Course would be an N-to-N relationship. But perhaps "course" means something else in your design?
 
Jobert John
Greenhorn
Posts: 12
Eclipse IDE MySQL Database C++
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Paul Clapham wrote:But inserts have nothing to do with joins. You can't insert into the result of a query involving a join. If you want to insert into two tables, then two inserts are required -- one for each table. You should consider creating a transaction to contain the two inserts, so that you don't end up with inconsistent data because only one insert was done and the other insert failed for some reason.

As for your Scheduling table: if it has a foreign key relating it to the Subject table, as it appears to do, then its "teacher_id" column is redundant. The Subject table already has that column.

Likewise I don't understand why the Student table has a "course" column. But this time the problem is that I don't understand what "course" means there. To me a Course is something like "Philosophy 403 (First-order Predicate Logic)" and such a thing wouldn't belong in a Student record. The Student could certainly be registered for that course but then you'd have another table to record that fact, since Student::Course would be an N-to-N relationship. But perhaps "course" means something else in your design?



>So I really must use two inserts for that.

>Oh right two FK of teacher_id. (But im using Joins like Schedule and Teacher, Schedule and Subject, Schedule and Student I dont use them for var. interlapping is that ok or my logic is wrong?)

>Course is kinda like department of the teacher table

So what is the better approach for my problem?

>about the many student to 1 subject
>many subject to 1 teacher (used FK for this and im basing on day and time so no conflicting schedules)

 
Paul Clapham
Marshal
Posts: 28193
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Jobert John wrote:>Oh right two FK of teacher_id. (But im using Joins like Schedule and Teacher, Schedule and Subject, Schedule and Student I dont use them for var. interlapping is that ok or my logic is wrong?)



Whether you're going to access data by joining two tables shouldn't have anything to do with how you should design those tables. Beyond that I don't understand what "var interlapping" means so I can't answer.

>Course is kinda like department of the teacher table



"Department"... another undefined term. If I didn't understand what a "course" was, even though you used an ordinary word, you shouldn't expect me to understand "department". Perhaps you aren't using the ordinary meaning there either.

So what is the better approach for my problem?



You're using ordinary words which mean obvious things in the context of students attending a school and taking courses. If you don't intend those words to have their ordinary meanings, you'd be better off to use other words. Preferably other words which reflect what the tables are supposed to represent. Preferably words whose ordinary meanings are what the tables are supposed to represent. If you don't do that then you should say what your meanings are for those words. I find that examples often help.
 
Jobert John
Greenhorn
Posts: 12
Eclipse IDE MySQL Database C++
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator


You're using ordinary words which mean obvious things in the context of students attending a school and taking courses. If you don't intend those words to have their ordinary meanings, you'd be better off to use other words. Preferably other words which reflect what the tables are supposed to represent. Preferably words whose ordinary meanings are what the tables are supposed to represent. If you don't do that then you should say what your meanings are for those words. I find that examples often help.



Those tables are presenting the information of them
Student table for student information
>name >Course (ex. BSCS, BSIT, and etc I would just use it for search purposes)

Teacher for teacher information
>name >department (Education Department, Computer Department, and etc also for searching purposes only)

and Subject as given describes the subject
>name( names can be duplicate, because different teachers teach sometimes the same subject)
>time1 and time2 (For conflicting purposes )
>day (also for conflicting purposes)

because this is a trial scheduling project (just to help me learn more about JDBC and Mysql)

P.S Sorry for my lack of knowledge :'(
 
Paul Clapham
Marshal
Posts: 28193
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Probably in real life "Subject" would be a more complicated structure, since a subject might be taught by more than one teacher at different times on different days, and so on. But if you're looking for something to practice SQL and JDBC then you don't need to worry much about whether your data structures match reality. Although it helps if they don't conflict with your own understanding of what they're supposed to represent. So carry on with your project and good luck!
 
Jobert John
Greenhorn
Posts: 12
Eclipse IDE MySQL Database C++
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Do you have any suggestions as to what is the best possible aproach of this kind of problem,

can't think of an algorithm
as to many to 1
and that 1 is to many.
 
Paul Clapham
Marshal
Posts: 28193
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Sorry, I've lost track of what your problem is. Maybe you could explain it again, taking into account what's been discussed in this thread.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic