Q:

One-to-many relation database design

Currently I'm working on a students system, in my project i need to make each student has many courses registered in it ... What is the best way to design the database?
currently I've created a table named students which contains students info,
and a table named courses contains names of courses.
I know that the way to do that is by making a foreign key from students table references to courses table,and I've tried that but i'm confused how to that exactly.
thanks in advance.

A:

You will need to create a table to model the many-to-many relation:
Students [ id | name | etc..]
Courses  [ id | name | etc.. ]
Students in Courses [ id | student_id | course_id ]

You can details about the relationship in there as well (in what year did the student enter the course, in what year did he/she finish, was the tuition paid etc..)
The data in these tables will look something like this:
students
Id       Name   
------   --------
S1       John
S2       Jane
S3       Mike
S4       Larry

courses
Id       Name   
------   --------
C1       Java for beginners
C2       C# for dummies
C3       Phyton for Noobs

Student in Courses
Id     CourseId StudentId   
-----  ------   --------
01     C1       S1
02     C2       S1
.....
99     C3       S3

You can query the data by using an inner join on the tables (pseudo code):
Select S.Id, S.Name, C.Id, C.Name
From Students S
Inner Join 'Students in Courses' SC ON S.Id = SC.StudentId
Inner Join Courses C ON SC.CourseId = C.Id

