• Post Reply Bookmark Topic Watch Topic
  • New Topic

MySQL insert logic  RSS feed

 
Jobert John
Greenhorn
Posts: 12
C++ Eclipse IDE MySQL Database
  • Mark post as helpful
  • send pies
  • 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 :'(
 
Paul Clapham
Sheriff
Posts: 22378
42
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • 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
C++ Eclipse IDE MySQL Database
  • Mark post as helpful
  • send pies
  • 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
Sheriff
Posts: 22378
42
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • 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
C++ Eclipse IDE MySQL Database
  • Mark post as helpful
  • send pies
  • 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
Sheriff
Posts: 22378
42
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • 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
C++ Eclipse IDE MySQL Database
  • Mark post as helpful
  • send pies
  • 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
Sheriff
Posts: 22378
42
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • 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.
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!