March 13, 2012 at 1:57 pm
Hi all,
Got a simple architectural question.
Let's say you have a table which has 10 columns, one of which is an ID column representing a unique identifier for the row.
Each of the other 9 columns contains a foreign key value to a lookup table, which in turn represents a set of choices that a user can select through an interface.
Now, let's say you want to allow the user to select an option "Other", and then type in a value representing their choice. Assume that the choice of "Other" will occur roughly 5% of the time.
What would you say is the best way of modeling that in your database structure?
From what I can tell there are three options:
1) In your table, for each column that is a foreign key reference, add a new column and store the value that is typed in by the user in that column if "Other" is selected for that column. The problem with this idea is that every time you need to add a new lookup column, you need to add a second one, so you're effectively doubling the size of your table.
2) In your lookup table, every time the user enters a value for "Other", add a new record to that table, and allow that record to be selectable in the future. The problem with this option is that you run the risk of cluttering up your lookup table with values that you don't really want to be seeing, since they are could often be one-time scenarios.
3) Create a new table. In this table, store the ID of the record from your main table, store the name of the column, or even the column ID from sys.columns, and then have a varchar field to store the value. Problem with this option is that joining to the table is ugly, since you need to specify a join for every one of your columns in your original table. If you use the sys.column ID, maybe you can join to sys.columns and then use that to join to the new table?
Anyone else have any cleaner options?
March 13, 2012 at 2:32 pm
It sounds like the table you are starting this conversation is not normalized. It contains columns 2-10 which are in effect the same type of information. The scenario you are describing will required table changes when the number of questions changes. I think you want to avoid this.
The way I have handled this type of thing in the past is to create a Response table AND an answer table. This way each person has a single reponse and any number of answers. Then you answer table has only 2 columns (the lookup table identifier and Other), plus whatever column(s) you need to tie it back to the response. That would be my approach.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 13, 2012 at 2:41 pm
Interesting. Hadn't really thought about it like that. So, for getting the values afterwards, I'd use some sort of dynamic cross-tab? Interesting ... gonna need to think about that.
March 14, 2012 at 9:09 am
Man, I almost wish you hadn't suggested that Sean. I was quite happy with the solution I had devised, then you have to go and suggest a better idea to me -_- Now I need to re-do the work I've done! 😛
Anyway - I like your idea. So the system would work like this, in my mind:
Primary table would contain an ID, and any values that are static.
Question table would contain a Question ID, and a Question value. Answer table would contain an AnswerID, a QuestionID, and an IsOther column. Initially, the Question table would be populated with the different questions, and the Answer table would be populated with the different answers, with IsOther set to 0 for the ones entered by the system. Every time a user answers a question with the choice "Other", and specifies their answer, it would get entered into the Answer table, with IsOther set to 1.
Finally, there would be a Question/Answer table, containing an ID, a foreign key referencing the Primary table, a Question ID, and an Answer ID.
March 14, 2012 at 9:17 am
LOL. Sorry to add rework to your plate. 😛 What you describe sounds like a pretty solid approach. I don't know if the additional table is needed for the "other" or just an extra column in the answer table. They both have their merits for sure. Glad I could help even if it made more work for you now.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 14, 2012 at 9:37 am
I made up some DDL for this. If only 5% of answers have "other" as an answer, then what do you think about adding a sparse comment field to your response table? Something like this?
create table #question (questionID int,question varchar(500));
create table #answer (answerID int, answer varchar(25));
create table #response (questionID int, answerID int, comment varchar(500) sparse null)
insert into #question (questionID,question)
select 1,'what color is the grass' union
select 2,'what color is the sky';
insert into #answer (answerID,answer)
select 1,'green' union
select 2,'blue' union
select 3,'other';
insert into #response (questionID,answerID,comment)
select 1,1,null union
select 2,2,null union
select 1,3,'i have rose colored glasses on' union
select 2,3,'it is dark out'
select q.question,a.answer,r.comment
from #response r
inner join #question q
on r.questionID = q.questionID
inner join #answer a
on r.answerID = a.answerID
drop table #question
drop table #answer
drop table #response
March 14, 2012 at 10:15 am
I was considering the option of having it be an extra column but if I ever want to make those extra answers usable as future choices, I would need to add a new row, instead of just changing it from IsOther = 1 to 0. Also, is sparse an option for SQL Server 2005 standard?
March 14, 2012 at 10:17 am
Good call, no 🙁
Here I thought I was being nifty in suggesting it. Upgrade!
I saw you posted in 2005 and even made changes to my script to work in 2005 and still missed that!
March 14, 2012 at 10:19 am
I believe that sparse columns was not available until 2008.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply