I am creating a database for a school results analyser.
In my database i have tables like "students and subjects"... I need a table that takes the marks scored by each student(marks table) .
For this table am considering 2 options..
1 : i will have a table(marks) with many columns i.e. students_regno,(columns for all subjects - could be around 20.
This table will have fewer rows. Maybe 700
Or
2 : i could have a marks table with few columns i.e. students_regno,subject_id,marks this will have only 3 columns but the rows may span to over 5000 rows.
Which way should I go?
Second one is definitely the way to go.
You always want to keep in mind that you want to be able to expand your system. If a new subject is introduced in 5 years. You want to be able to add that. And with that you don't want to add an extra field and leave that empty for all the records you already have.
It's also better to keep everything seperated. There will probably be alot of times where you won't need 90% of that data. It's better to just get it all from 1 marks table then.
So I'd say go for option 2. Make a marks table with student_regno, subject_id, mark