Hi,

I am a newbie to database design and have a database design question. This is my problem.

I have a certain profile (as i call it) which contains information about a student. The profile table is something like this

Profile(student name,
student age,
profile name,
profile id,
college name,
fee,
semester
); etc.


Now i must add information about the subjects he takes. What is the best way to do that? The subjects could be modified at any time, more subjects may be added or old subjects deleted.

I was thinkign of having a separate table Subjects(sub1, sub2, sub3) etc. and somehow relate it to the profile.

Also, let the first profile be named profile 1 (say). There can also exist another profile (profile 2) for the same student with just the subjects being different from profile 1.

Please tell me is the best way to design some thing like this? Also how can i link the two tables etc. Thanks a lot for ur help.

You should create a primary key for the table. Suppose you call it "student_id" It can just be a running number but can be anything as long as it is unique. This must be unique for each row.

You then create another table, subjects which also has the subject_id and a subject (and whatever other columns you need. Then for each student add a row using the same student_id and a subject. Add as many rows for each subject as you like.



eg
Students table
1 Tom
2 Dick
3 Harry
....

Student/Subjects
1 Math
1 History
2 Math
2 Biology
3 Latin
...


Don't want to confuse you at this point, but the subjects itself should be in its own table, with a subject_id, and subject_name/description. The above table should have a student_id and a subject_id.
e.g.
Subject
1 Math
2 History
3 Biology
4 Latin

The student/subject table will then look like

1 1
1 2
2 1
2 3
3 4