Which design better when use foreign key instead of a string to store a list of id

Question

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:

1. It will have two columns: general_exam_id, question_id.
2. 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.

• Doc Brown: Your question title is about performance, but you ask which design is better. This is a common misconception - you think better performance = better design. Actually, you should first focus on a well structured, maintainable design and ask the performance question only when you face real performance problems.
• Oded: Please don't post the same question on multiple stack exchanges. Cross posting is not encouraged here. stackoverflow.com/questions/13415511/…
• Thanh: @DocBrown sorry, i just think, with 1 general exam with 100 questions, first solution need 100 rows, second solution need only one row, so i just thought second solution can get id of question faster, so i thought that performance, i will edit my title.
• Thanh: @Oded sorry sir, i just want to have a quick answer :(
• HLGEM: Virtually any time you want to store a string of values in one field, you have made a mistake. One of the very first rules of database design is to NEVER put more than one piece of information in a field. Fields with concatenated information are MUCH harder to query and tend to perform poorly due to the queries being unable to use an index.

Answer 1

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...

• Trev: The last point about extra queries to the DB was the deal breaker for me.

Answer 2

• 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.

• Thanh: thanks for exlain, i think i should be do first solution but i don't know why i should, now i'm know :)
• Md Mahbubur Rahman: Most welcome. Glad as this answer helped you. Also thanks for your nice and tricky question. :)
• Doc Brown: @MahbuburRAaman: perhaps I misunderstood you, but the "foreign key cascades" work only correctly when DB foreign keys are used. If I got the OP right, the String/Text suggestion is about having a list of IDs encoded in one string - this is a solution avoiding DB relations, so foreign keys and indexing won't work.
• Md Mahbubur Rahman: @DocBrown, You are right. But as a whole and explaining as pros/cons i would like to refer the point.
• Tulains Córdova: 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".