-2

I am porting an old obsolete Java based Quiz application project onto LoopbackJS.

Background:

It is an MCQ Quiz app for Intelligence Scholarship Examination. Students can register on the app. There will be set number of questions for every grade. Students can practice by solving the questions, and will be rated based on the attempts taken to solve a question. There will be Leaderboards on which Students will be ranked. There will also be monthly Quizs to solve. these monthly quizs will just be randomly selected questions from the all the avaliable questions, based on the students grade.

Below is the Database schema for the Old Java Project which I will be building on.

enter image description here

Is the above Database design ok, or there is some scope of improvement.

I have also been going through stackoverflow to find similar questions and I found another which fits close my requirement. But i am bit confused on the #Test Questions part, struggling to fit it to my needs.

Rails Associations and db design for Quiz Application

Suggestions and Guidance would be much appreciated. Thanks

Dinesh
  • 41
  • 6
  • Can't judge a schema without seeing the main queries. – Rick James May 26 '19 at 05:08
  • Main queries are way to complicated and long ( to post here ), as i said this is an old and obsolete project, so everything is way to messed up in there :( – Dinesh May 26 '19 at 05:39

1 Answers1

0

  • Do not have redundant data. I see several cases of a ..._bin and ..._hex; I assume these are the same data, but in different encodings? It is easy enough to convert (using HEX() and UNHEX()) when fetching/storing.

  • Don't use UUIDs for keys unless you will be receiving data from multiple clients and they cannot avail themselves of AUTO_INCREMENT Primary Keys.

  • Think about SET or INT for implementing col_has_% columns in a single column. (This may or may not be an improvement.)

  • Be careful of how much sensitive data is being stored.

  • For ease of reading queries, don't include col_ in front of column names. Ditto for not including the table name in column names. When JOINing you have the opportunity (and obligation to the reader) to qualify columns with the table name.

  • When creating a many:many mapping table between two other tables, don't have an id -- the pair of ids is an excellent composite PRIMARY KEY.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • Yes the _hex and _bin seems to be the same, and I have started stripping them down. You suggest not using UUIDs for keys, any specific reason for it? Regardiing the col_has_% I am planning on completely dropping the idea coz i want to restructure that part a bit more. Can you elaborate on last point ? – Dinesh May 26 '19 at 05:34
  • @Dinesh - [_UUID discussion_](http://mysql.rjweb.org/doc.php/uuid); [_many-many_](http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table) – Rick James May 26 '19 at 14:17