|
|
You're in the
sql server (alternate)
group:DB design question - survey system
sql server (alternate):
You didn't explain how the survey is being generated, but that they may not matter. However, if this is a web application that builds dynamic forms, experience indicates the best solution is to provide a form builder via an Admin application. Store the meta-data about your forms and provide an interface for adding form meta-data via building and modifying forms. Whenever a form is created or updated, generate the form (asp, aspx, whatever) using a Generator Service that monitors a queue. The queue gets PUSHes from the Admin application whenever something triggers a form regeneration, and the responsibility of the Generation Service is to POP the queue and generate forms from the meta-data. You also want to add the feature to generate sets of forms based on the results of a stored procedure. I suggest building the queue in SQL Server. Most developers lean towards MSMQ, however the problem with MSMQ is that it has limited interfaces - you can't POP and PUSH with Transact SQL. We migrated a queue that runs 24x7 capturing data from our customers for their sites from a 3rd party queue product to SQL Server . We have implemented MSMQ in other areas of our organization, however we are migrating them to SQL Server. Our software runs about 5,000 sites and we provide our site administrators the ability to build forms that capture normalized data. Within five seconds after an administrator builds or modifies the form, the new form is on their site (which is on a 15-server farm) capturing and redirecting normalized data based on business rules for the recipient(s).
I've written several survey systems in which the majority of the questions
have the same or similar responses (Yes/No, True/False, scale of 1 - 5, etc). But this latest survey system I'm working on has 8-10 sections, with a variety of question attributes and answer scales. Some items have just a description and require a Yes/No answer, others have a description and an active status and require a Yes/No and price answer, some require a comment, etc. Rather than build a separate response table for each survey section, I was thinking of building one generic response table, and trying to force all sections to fit by adding columns - some of which won't apply to some items. Like this: Survey Category (will apply to all items) Survey Section (will apply to all items) Item Description (will apply to all items) Item YN (will apply to all items) Item Price (will apply to about 10% of the items) Item Points (will apply to about 10% of the items) Item Active YN (will apply to about 10% of the items) Item Fail YN (will apply to about 10% of the items) Item Comment (will apply to about 10% of the items) For instance, in the structure above the field "Item YN" would represent multiple types of answers: is the item in use?, is the item in place?, is the item given away for free?, is the item on display?, etc. Basically, anywhere a Yes/No answer is used. The advantage is one source table (rather than 8) for storing answers, and it might be easier to query and report on. The disadvantages I see are 1) it's more difficult to understand the meaning of the responses when the answer field is named Item YN, and 2) you have a non-normalized table that's difficult for a 3rd party to understand. If I have the questions and responses in separate tables, I'll use names like "ItemComplimentaryYN" and "ItemUsedYN" depending on the question. It's easier for others to learn the data. I actually don't like the "generic" approach, and probably won't use it, but I figured I'd try to get some input from others who've written survey systems. Thanks
[quoted text, click to view]
maxl@msn.com wrote: > Normalize. > > You didn't explain how the survey is being generated, but that they > may not matter. However, if this is a web application that builds > dynamic forms, experience indicates the best solution is to provide a > form builder via an Admin application. > > Store the meta-data about your forms and provide an interface for > adding form meta-data via building and modifying forms. Whenever a > form is created or updated, generate the form (asp, aspx, whatever) > using a Generator Service that monitors a queue. The queue gets > PUSHes from the Admin application whenever something triggers a form > regeneration, and the responsibility of the Generation Service is to > POP the queue and generate forms from the meta-data. You also want to > add the feature to generate sets of forms based on the results of a > stored procedure. > > I suggest building the queue in SQL Server. Most developers lean > towards MSMQ, however the problem with MSMQ is that it has limited > interfaces - you can't POP and PUSH with Transact SQL. We migrated a > queue that runs 24x7 capturing data from our customers for their sites > from a 3rd party queue product to SQL Server . We have implemented > MSMQ in other areas of our organization, however we are migrating them > to SQL Server. > > Our software runs about 5,000 sites and we provide our site > administrators the ability to build forms that capture normalized > data. Within five seconds after an administrator builds or modifies > the form, the new form is on their site (which is on a 15-server farm) > capturing and redirecting normalized data based on business rules for > the recipient(s). Awesome answer, maxl. Thanks. It's an Access application, so I use a variety of Access tab controls and forms: datasheet and continuous view mainly. The surveys are slightly dynamic, in that an administrator will be revising the questions, points, items, etc and pushing the new survey data down to the field each week or so. The survey itself is generated at run-time, by the user, from lookup tables containing the questions. I copy the questions (necessary parts anyway) and items into survey tables, along with a unique ID for the survey instance. I want to do some web survey systems, but I can deliver this one much quicker under Access. And with only 5 to 6 users, code updates and distribution isn't an issue. Thanks for your help.
[quoted text, click to view]
John Bell wrote: > Hi > > You talk about a single table per section, but I am not sure that > that sort of design would be any different to your suggested one. Well, I was considering aggregating all responses into one table. As I described, it would require the addition of several columns that are irrelevant to 90% of the questions. The result would be a non-normalized mess, though it would probably be easier to query and report on. [quoted text, click to view] > You could have a separate table for each type of questions i.e have > all Y/N answers in one table, also Fail/Active/Nothing/Complementary > can be a second attribute therefore you will not have separate > columns for the Y/N part. Interesting. That's kind of in-between where I am now and my "generic" approach under consideration. I'll look into this a little more. I do have some tables with similar response types I could combine. [quoted text, click to view] > You may also want to check out using SQL_VARIANT as a data type. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_setu-sus_4stw.asp?frame=true> It's an Access system, so text would be my only real option if I wanted to combine response types into one column. Thanks for your help. [quoted text, click to view] > Full text searching would possibly be a different solution. > > John > > "DFS" <nospam@nospam.com> wrote in message > news:41Yyd.2693$Zp1.2514@fe07.lga... >> I've written several survey systems in which the majority of the >> questions have the same or similar responses (Yes/No, True/False, >> scale of 1 - 5, etc). >> >> But this latest survey system I'm working on has 8-10 sections, with >> a variety of question attributes and answer scales. Some items have >> just a description and require a Yes/No answer, others have a >> description and an active status and require a Yes/No and price >> answer, some require a comment, >> etc. >> >> Rather than build a separate response table for each survey section, >> I was thinking of building one generic response table, and trying to >> force all sections to fit by adding columns - some of which won't >> apply to some items. >> Like this: >> >> Survey Category (will apply to all items) >> Survey Section (will apply to all items) >> Item Description (will apply to all items) >> Item YN (will apply to all items) >> Item Price (will apply to about 10% of the items) >> Item Points (will apply to about 10% of the items) >> Item Active YN (will apply to about 10% of the items) >> Item Fail YN (will apply to about 10% of the items) >> Item Comment (will apply to about 10% of the items) >> >> For instance, in the structure above the field "Item YN" would >> represent multiple types of answers: is the item in use?, is the >> item in place?, is the item given away for free?, is the item on >> display?, etc. Basically, anywhere a Yes/No answer is used. >> >> The advantage is one source table (rather than 8) for storing >> answers, and it might be easier to query and report on. >> >> The disadvantages I see are 1) it's more difficult to understand the >> meaning >> of the responses when the answer field is named Item YN, and 2) you >> have a non-normalized table that's difficult for a 3rd party to >> understand. >> >> If I have the questions and responses in separate tables, I'll use >> names like "ItemComplimentaryYN" and "ItemUsedYN" depending on the >> question. It's >> easier for others to learn the data. >> >> I actually don't like the "generic" approach, and probably won't use >> it, but >> I figured I'd try to get some input from others who've written survey >> systems. >> >> Thanks
Hi
You talk about a single table per section, but I am not sure that that sort of design would be any different to your suggested one. You could have a separate table for each type of questions i.e have all Y/N answers in one table, also Fail/Active/Nothing/Complementary can be a second attribute therefore you will not have separate columns for the Y/N part. You may also want to check out using SQL_VARIANT as a data type. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_setu-sus_4stw.asp?frame=true Full text searching would possibly be a different solution. John [quoted text, click to view] "DFS" <nospam@nospam.com> wrote in message news:41Yyd.2693$Zp1.2514@fe07.lga... > I've written several survey systems in which the majority of the questions > have the same or similar responses (Yes/No, True/False, scale of 1 - 5, > etc). > > But this latest survey system I'm working on has 8-10 sections, with a > variety of question attributes and answer scales. Some items have just a > description and require a Yes/No answer, others have a description and an > active status and require a Yes/No and price answer, some require a > comment, > etc. > > Rather than build a separate response table for each survey section, I was > thinking of building one generic response table, and trying to force all > sections to fit by adding columns - some of which won't apply to some > items. > Like this: > > Survey Category (will apply to all items) > Survey Section (will apply to all items) > Item Description (will apply to all items) > Item YN (will apply to all items) > Item Price (will apply to about 10% of the items) > Item Points (will apply to about 10% of the items) > Item Active YN (will apply to about 10% of the items) > Item Fail YN (will apply to about 10% of the items) > Item Comment (will apply to about 10% of the items) > > For instance, in the structure above the field "Item YN" would represent > multiple types of answers: is the item in use?, is the item in place?, is > the item given away for free?, is the item on display?, etc. Basically, > anywhere a Yes/No answer is used. > > The advantage is one source table (rather than 8) for storing answers, and > it might be easier to query and report on. > > The disadvantages I see are 1) it's more difficult to understand the > meaning > of the responses when the answer field is named Item YN, and 2) you have a > non-normalized table that's difficult for a 3rd party to understand. > > If I have the questions and responses in separate tables, I'll use names > like "ItemComplimentaryYN" and "ItemUsedYN" depending on the question. > It's > easier for others to learn the data. > > I actually don't like the "generic" approach, and probably won't use it, > but > I figured I'd try to get some input from others who've written survey > systems. > > Thanks > > >
[quoted text, click to view]
DFS wrote: > I've written several survey systems in which the majority of the questions > have the same or similar responses (Yes/No, True/False, scale of 1 - 5, > etc). > > But this latest survey system I'm working on has 8-10 sections, with a > variety of question attributes and answer scales. Some items have just a > description and require a Yes/No answer, others have a description and an > active status and require a Yes/No and price answer, some require a comment, > etc. Do use one table for answers. I mean for questions. There may be some frame you need around this table; but I feel, and then I mean my database intuition, that one table is the best way to get statistics and reports. Let me think aloud. If one question can have an answer in more parts, that troubles the setup. You mention yes/no and a price answer; but is this the type "No" or "Yes, namely $32" ? In that case the yes/no part can be made implicit. What you can use is some tag, indicating the meaning of the answer value. I see this table before my eye: Question( [ID,] questionNumber, questionText, answerType, answerTag) Decide for yourself if the questionnumber would be the primary key, or you use a separate, non-user-visible key (I prefer the latter). answerType sits in a small table, containing values like {"yes/no","number","text"}--much like the DataType of a field. You have this table, appropriately called AnswerType, one-to-many to the question table. Btw I name my tables single rather than plural. That's a choice. answerTag can be any description that differentiates to you what kind of datum the answer represents. So far this structure can produce empty survey forms. Real surveys provide answers; you might store those in Survey(surveyID) SurveyAnswer(surveyID, questionNumber, givenAnswer) where you can derive the properties of givenAnswer from the Question table. I recommend the generic approach. It enables you to change the survey setup without data changes, it does take some getting used to as it is a little more abstract than the straight table but once you get the hang of it you won't want something else anymore ;-) -- Bas Cost Budde, Holland http://www.heuveltop.nl/BasCB/msac_index.html I prefer human mail above automated so in my address
Don't see what you're looking for? Try a search.
|
January 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
| home · blog · groups | Questions? Comments? Contact the dn |