Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
waveform
Yak Posting Veteran
93 Posts |
 Posted - 2011-08-10 : 12:00:09
|
| Say we have a survey application, which has a table of Answers to Questions. The Answers table is basically like so:AnswerID INT IDENTITY NOT NULLQuestionID INT NOT NULLParticipantID INT NOT NULLOptionID INTThere is always a Question and a Participant of course, but there is not always a given answer. Some questions may be skipped. OptionID relates to a table containing all possible options to all questions. The detail there doesn't matter.My question is - if someone skips a question, I am tempted to use NULL as the OptionID, since there is "no answer", ie. no data. However is it better (for database efficiency for example) to *always* point to a record in a related table? That is, is it better to create a "no answer" record (say ID=0) and so make OptionID = 0 if the person skipped the question?Or does it make no real difference if it's NULL or not? |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2011-08-10 : 12:18:46
|
| I think in this case it makes more sense to leave it NULL. Normally, I would expect that each question has a set of Options that are selectable. If "No ANswer" is an option they can't select, then it's doesn't make sense to put that in as a option they user "selected." |
 |
|
|
waveform
Yak Posting Veteran
93 Posts |
Posted - 2011-08-10 : 12:34:43
|
| Many thanks. I often hear it's good practice not to use NULL, so I wondered what would be the best thing in this case.I did read somewhere that OUTER JOINs are less efficient than INNER JOINs, so that also made me wonder if NULL was advisable where foreign keys are concerned. Is that not the case any more with current db engines? |
|
|
|
|
|
|
very basic question