I’m building online examination system. I have designed to table, Question
and GeneralExam
. The table GeneralExam
contains info about the exam like name, description, duration,…
Now I would like to design table GeneralQuestion
, it will contain the ids of questions belongs to a general exam.
Currently, I have two ideas to design GeneralQuestion table:
- It will have two columns: general_exam_id, question_id.
- It will have two columns: general_exam_id,
list_question_ids (string/text).
I would like to know which designing is better, or pros and cons of each designing.
I’m using Postgresql database.
12
The first design is the better one.
This is because you can then also create foreign keys to the questions. Also you have no further processing for parsing the list of ids. Also in this case you have the ability to get all questions together with the general exam query (only one round-trip to the database).
In the case of the second design you have to get the database entry for a general exam, parse the text and then query the database again for the questions…
1
- Nice and Tricky Question
It depends on usage. But most of the times INT key is preferred. Usually you will find something like ID to show the primary key.
Ultimately it depends on the usage. What kind of requirements you are going to have. Accordingly decide. But INT is most preferred.
Here I have explained both
For int IDs (First option) #
Identity field should be numeric and not string based, because
- Space saving (An int is 4 bytes, a string can be as many bytes as you like. Because of that, an int will always perform better)
- Performance reasons (matching keys on strings is slower than matching
on integers) - Data redundancy will be solved by int column. As Foreign keys need to
be updated (and/or deleted) whenever the data is updated. - Updates/deletes on a foreign key can be set to cascade.
For String/Text (Second option) #
- Using a foreign key means that to get user-readable data (ie, the
text) a JOIN must be made. Using a descriptive foreign key, no JOIN
needs to be made to get user-readable data. This includes reading
with SELECTs and when INSERTing or modifying data. For example, to
insert into AddressNum the groupId is needed. - Human-readable data in the database. This will make it easier for the
DBA to debug data issues, as they can use a SHOW CREATE TABLE to find
the foreign key references, and then get a sample of data from one
table and understand the scenario, without having to JOIN.
I like the @user1598390’s comment
OP is not asking whether to use int IDs or String IDs. He/she is
asking whether to used int IDs (one to many) or having all questions
IDs concatenated in a text column like “123311;121322;123455;123487”.
First option I took as int IDs and Second option I took as Text/String.
It depends on usage. What kind of requirements you are going to have. Accordingly decide. Most of time, First option is preferred. In this case, for first option you have no further processing for parsing the list of ids.
8