filling a M:N table foreign keys
-
I have simple db with 4 tables: class, book, student and studentbook. studentbook has st_id and book_id as foreign keys and an extra field for result. book has some book for classes. I just insert new students. I'm quite beginner in db and don't know how to fill studentbook. should I fill it (just foreign keys) when filling student table? or it can be automatically? I don't know the routine. I should insert students and result in different times. Extra information: I use visual studio 2010 for programming and access 2003 for db. for user-friendly reason I want a datagridview that shows students and result in on table to insert result for each student. I got some success but it's not quite update but inserting action, that my insert two or more rows for a student in studentbook table cause clicking of updating button. Please any idea or simple code?
Thanks, Sia swar Baluch
-
I have simple db with 4 tables: class, book, student and studentbook. studentbook has st_id and book_id as foreign keys and an extra field for result. book has some book for classes. I just insert new students. I'm quite beginner in db and don't know how to fill studentbook. should I fill it (just foreign keys) when filling student table? or it can be automatically? I don't know the routine. I should insert students and result in different times. Extra information: I use visual studio 2010 for programming and access 2003 for db. for user-friendly reason I want a datagridview that shows students and result in on table to insert result for each student. I got some success but it's not quite update but inserting action, that my insert two or more rows for a student in studentbook table cause clicking of updating button. Please any idea or simple code?
Thanks, Sia swar Baluch
siaswar wrote:
should I fill it (just foreign keys) when filling student table?
Your data model isn't clear. I will presume that any one book can only be used by one class. Thus one book is associated with only one class. So your tables and fields would look like the following. class: class_id, class_name book: book_id, book_name, class_id student: student_id, student_name Students takes classes so there must be a way for a student to be associated to a class. So that gives another table. student_class_link: student_id, class_id For this table you would populate it as follows. 1. Create classes - fill class table. 2. Assign books to classes - fill in book table and include appropriate class id. 3. Create students - fill student table 4. Assign students to class - fill in student_class_link table. (It is NOT 'automatic' you must add the values.) If I want to find what books are associated with which student I do NOT need another table. That is because I can use the following relationship to get the books. student -> student_class_link -> class -> book
-
siaswar wrote:
should I fill it (just foreign keys) when filling student table?
Your data model isn't clear. I will presume that any one book can only be used by one class. Thus one book is associated with only one class. So your tables and fields would look like the following. class: class_id, class_name book: book_id, book_name, class_id student: student_id, student_name Students takes classes so there must be a way for a student to be associated to a class. So that gives another table. student_class_link: student_id, class_id For this table you would populate it as follows. 1. Create classes - fill class table. 2. Assign books to classes - fill in book table and include appropriate class id. 3. Create students - fill student table 4. Assign students to class - fill in student_class_link table. (It is NOT 'automatic' you must add the values.) If I want to find what books are associated with which student I do NOT need another table. That is because I can use the following relationship to get the books. student -> student_class_link -> class -> book
I don't use class in this manner. I mean grade for classes such class A is for student of 7 years old and class B is for student in age 8. student table has class_id field too. in this way one student can have every book of class like, math, english,... and each book belong to all student in class. so I create studentbook table which has book and student ID and an extra field: RESULT. well student table will be fill by a user of software. afther a long time he/she will import student result which goes in studentbook table. question is how and when studentbook.st_id and studentbook.book_id should give value. suggest a query and when use the query, when importing the students or when want to import results?! As you know it's my first database as homework. :wtf:
Thanks, Sia swar Baluch
-
I don't use class in this manner. I mean grade for classes such class A is for student of 7 years old and class B is for student in age 8. student table has class_id field too. in this way one student can have every book of class like, math, english,... and each book belong to all student in class. so I create studentbook table which has book and student ID and an extra field: RESULT. well student table will be fill by a user of software. afther a long time he/she will import student result which goes in studentbook table. question is how and when studentbook.st_id and studentbook.book_id should give value. suggest a query and when use the query, when importing the students or when want to import results?! As you know it's my first database as homework. :wtf:
Thanks, Sia swar Baluch
siaswar wrote:
when want to import results?!
Your requirements are still not clear to me. But regardless your "studentbook" table still represents a link table (two tables linked together.) You fill it in when you have the necessary information to fill it in. Which, as best as I can figure, you would have when you assigned a student to a class. However if there is a 'check out book' process (a manual process perhaps done the first day of class) then it would be done then.