How To Design My Table?

[niloufar]

Hello everyone I am creating a reporting system and I have two tables from students and one table from training. I need to join these tables and remove duplicates. however I have to normalize my tables first. The both have so many problems. Because at the end when I Union two tables I get duplicates result.



My first question is, how to design my training table, because students is simple and their IDs are uniq so I can have PK; however, I have one excel sheet with student many similar students IDs that have taken many training.

Student Table

ID	Name	DOB
1	AA	1/1/88
2	ABC	1/1/88
3	ADE	1/1/88
4	AFG	1/1/88
5	BHI	1/1/88
6	JFH	1/1/88

Training Table

ID	Name	Course
1	A	Course-A1
1	A	Course-A2
1	A	Course-A3
2	A	Course-A4
2	B	Course-A1
2	B	Course-A2
2	B	Course-A3
2	B	Course-A4
3	C	Course-A1

How I suppose to design this table? PK? Index? or normalize ? and HOW?

When I combine my both tables to get all student names who has taken or not taken the course, I get duplicate ID and name; BUT if I take the name out then I get right number of IDs:

CODE
SELECT DISTINCT CurrentStudent.[Student ID], CurrentStudent.[Student Name]
FROM (SELECT Student.[Student ID], Student.[Student Name]
FROM Student
GROUP BY Student.[Student ID], Student.[Student Name]

Union

SELECT Training.Username, Training.[Student Name]
FROM Training
GROUP BY Training.Username, Training.[Student Name]

) AS CurrentStudent
ORDER BY CurrentStudent.[Student ID];

Thank you

[hansendl]

You have a many-to-many relationship between students and courses, so you'll need three tables:

tblStudents:
StudentID (PK)
LastName
FirstName
...

tblCourses:
CourseID (PK)
Title
Description
Classroom
...

tblStudentCourses:
StudentID
CourseID

Both StudentID and CourseID together will form the PK for tblStudentCourses. Also, you'll need to establish a one-to-many relationship between tblStudent-->tblStudentCourses and between tblCourses-->tblStudentCourses.

If this thoroughly confuses you, recommend you do some reading on many-to-many relationships and how to normalize these types of relationships in an RDMS.

Good luck!

[niloufar]

Great, thank you very much