1

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?

pickman murimi
  • 53
  • 7
  • Clarify your question please. Use of rows and columns not consistent, for example this statement: 'this will have only 3 rows but the rows may span to over 5000 rows.' doesn't make sense. Where did you mean column exactly? – Sasang Apr 16 '17 at 21:25
  • They are not called marks. They are called grades. – Mike Nakis Apr 16 '17 at 21:32
  • @shogunivar that option will result to over 5000 rows when marks are fed to the database... Will having all those rows affect the speed of the database?... Speed is my other major concern. The subject_id and students_regno will also repeat over and over ..is that a problem? – pickman murimi Apr 16 '17 at 22:20
  • 1
    @MikeNakis Maybe not where you come from. – philipxy Apr 16 '17 at 22:23
  • @pickmanmurimi 5000 rows is not something to be worried about. When indexed correctly it shouldn't really effect your speed that much. Since your student_regno and subject_id will be a composite primary key together they should be repeating, that's fine – Shogunivar Apr 16 '17 at 22:44
  • @shogunivar What number of rows should concern me?..the database could grow to over 1000000 rows in a case we have many students. – pickman murimi Apr 16 '17 at 22:50
  • @pickmanmurimi aslong as your tables are properly indexed you shouldn't have to worry about it whatsoever. You can check out the answers [here](http://stackoverflow.com/q/1926079/5610030) for more on that – Shogunivar Apr 16 '17 at 23:04
  • @shogunivar Thanks alot – pickman murimi Apr 16 '17 at 23:05

1 Answers1

2

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

Shogunivar
  • 1,237
  • 11
  • 18