Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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.

share|improve this question
2  
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. –  Doc Brown Nov 16 '12 at 12:05
    
Please don't post the same question on multiple stack exchanges. Cross posting is not encouraged here. stackoverflow.com/questions/13415511/… –  Oded Nov 16 '12 at 12:08
    
@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. –  Kien Thanh Nov 16 '12 at 12:10
    
@Oded sorry sir, i just want to have a quick answer :( –  Kien Thanh Nov 16 '12 at 12:12
1  
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. –  HLGEM Nov 16 '12 at 21:35

2 Answers 2

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

share|improve this answer
    
thanks for explain :) –  Kien Thanh Nov 16 '12 at 12:03
  • 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.

share|improve this answer
    
thanks for exlain, i think i should be do first solution but i don't know why i should, now i'm know :) –  Kien Thanh Nov 16 '12 at 12:03
    
Most welcome. Glad as this answer helped you. Also thanks for your nice and tricky question. :) –  Md. Mahbubur R. Aaman Nov 16 '12 at 12:06
1  
@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. –  Doc Brown Nov 16 '12 at 12:13
    
@DocBrown, You are right. But as a whole and explaining as pros/cons i would like to refer the point. –  Md. Mahbubur R. Aaman Nov 16 '12 at 12:22
1  
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". –  user61852 Nov 16 '12 at 20:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.