Design assistance - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Design assistance

Author

Topic

kloek8
Starting Member

5 Posts

Posted - 2013-08-05 : 15:30:22

Hi,

I am designing a database that will function as a student information system. My problem is that in my original design i did not see that the identity of the student was not carry`t in to the rest of the database (design flaw.

So i redesigned the database for a part and i just cant find out how to efficiently carry the student identity into the rest.

I have included a access 2010 model of the database (picture).

As you can see i have tried to enter the student in the table 'Vak'
This is so because i need to link a course(Vak).

If anyone has advice or a better design i would welcome it allot?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-06 : 00:54:55

why should Studentnumber be in Vak? If Vak represents a course, then that would mean All vaks should have students assigned. Also in case of multiple students assigned to course, all course details have to be repeated for each of students which may cause update anamolies.
I would have made it into three tables as below

1. Student - current student table you've
2. Vak - Current Vak table removing studentnumber field
3. New bridge table StudentVak - this will have two fields studentnumber and Vak_ID which will be linked to above two tables using Foreign Key (fk) constraint
Then wherever you need a reference to cource you can include VakID as a fk in them an similarly for relating to student add studentnumber as a fk.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

kloek8
Starting Member

5 Posts

Posted - 2013-08-06 : 03:34:02

Hi,

I had that at first but that means that the course information will be repeated a thousand times. That does not seem efficient.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-06 : 05:36:26

quote:
Originally posted by kloek8

Hi,

I had that at first but that means that the course information will be repeated a thousand times. That does not seem efficient.

'how course information will be repeated?
thts not true
course details will just be one time inside vak table
FOr each student in course you'll just have a record in new bridge table coursevak with studentnumber and vak_id. no other vak details will get repeated
Vak details will get repeated only in your posted case where you had studentnumber also stored inside vak table.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

kloek8
Starting Member

5 Posts

Posted - 2013-08-06 : 05:52:14

Alright, but how do i carry the student to the table "Toets"(Test) and "Cijfers"(Grades)

Because for each course there are multiple tests and grades.
The original link between the student and course worked in my own first draft. But the grades where not linked to the students.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-06 : 05:57:21

for relating grades and test to student you can add bridge table which will have studentnumber,vak_id with testdetails as well as with the gradedetails. so each record will represent a test/grade of student on a particular course.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

kloek8
Starting Member

5 Posts

Posted - 2013-08-06 : 06:38:28

I don`t think i understand.
Can you give a a graphical example?

Mar
Starting Member

47 Posts

Posted - 2013-08-06 : 08:13:12

This is what visak is trying to tell you:

Student table Course table Student Courses table
------------- ------------ ---------------------
Student ID Course ID Student ID
Name Name Course ID
etc. etc. Room Number

You should read about database normalization, that should help you. One reason you do not put a student id in a course table is because that course can exist without that student. You only want related things in the same table. Perhaps you should start more simply. Just with students and courses. When you have that design right than add more.

kloek8
Starting Member

5 Posts

Posted - 2013-08-06 : 08:41:10

Thanks,

I have had database normalization and used it in my original design. I just did not use it when i changed the design.

I think i understand now and will try it. Thanks.

In anyone has more advice or ideas they are still welcome.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources