Page 1 of 2 12 LastLast
Results 1 to 40 of 42

Thread: SQL Schema for simple survey

  1. #1

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    SQL Schema for simple survey

    Hi,

    I want to create a database for a survey with following criteria:

    For Survey

    1 - It is only one survey that consist of many questions - everyone shall complete this one survey
    2 - All questions have only YES/NO answer (there is about 300 questions)

    For Respondents

    1 - One respondent can complete this survey many times
    2 - This survey can be completed by many respondents

    For the interface

    1 - Respondents shall complete the survey on their pc (userform with questions where user just select YES or NO to each question. I found many examples of survey schemas online but not really what I wish for, it is too complex and in no of them I could see how user is presented with questions - just how user responses is captured basically in database)


    What I do so far:

    Attachment 158907

    tblRespondent - For keeping it simple, I just add name (later I shall add more details)

    A respondent can answer survey many times, so each time the date and place where survey was took is captured in tblSurveyDetails

    Because one respondent can answer same survey many times, I have create a one to many relation between respondent and answer.

    I don't know how to present respondents with questions.. everyone must always answer same set of questions.

    If a user logs in she must see list of her own surveys, and must be able to open and view answers that was given...

    Only one survey for everyone (meaning there will never be other surveys, only this one survey with 300 questions)

    Thanks
    Last edited by schoemr; May 9th, 2018 at 02:52 AM.

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: SQL Schema for simple survey

    The tblAnswer table should be related to tblSurveyDetails, not tblRespondent. It should also be related to tblQuestions. Each time a respondent fills out the survey you will create a record in tblSurveyDetails and, related to that, you will create a record for each question in tblAnswer.

    By the way, you should work out whether you want your table names to be singular or plural and stick with that decision. Mixing and matching for no apparent reason is not a good idea

  3. #3
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: SQL Schema for simple survey

    As far as how you will present this to the user, you would need another DataTable and table adapter in your DataSet that combines questions and answers, because that's what you are going to present to the user. You would need to populate that DataTable with one row for each record in the tblQuestions table and then save the results to the tblAnswer table. Remember that, while the wizard will generate DataTables and table adapters that query one database table and save back to that same table, you can write whatever SQL is useful as long as it matches the schema in your DataTable.

  4. #4

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    Re: SQL Schema for simple survey


  5. #5

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    Re: SQL Schema for simple survey

    As far as how you will present this to the user, you would need another DataTable and table adapter in your DataSet that combines questions and answers, because that's what you are going to present to the user.
    I think this is what is troubling me.. I was trying to accomplish this by means of a schema design

  6. #6
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: SQL Schema for simple survey

    You need to give your relationships a bit more thought. Every relationship there is 1:Many so how can tblAnswers have a QuestionsID and tblQuestions have an AnsweerID? You also need to think about which is the 1 and which is the Many in each relationship. How does it make sense for tblSurveyDetails to have an AnswerID column? Do you really think that each time a survey is filled out there will only be one answer?

  7. #7

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    Re: SQL Schema for simple survey

    okay I am sure that a respondent can have many surveys, so that relation I think is good.

    Then.. If I understand you correct, "a" survey can have one set of answers. I make that one to one relation (?)
    Then, "a" set of questions can only have a set of answers.. also one : one (?)

    Attachment 158913

  8. #8
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: SQL Schema for simple survey

    They all need to be 1:Many relationships. A respondent can fill in the survey many times, each survey response has many answers and each question is answered many times.

  9. #9
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: SQL Schema for simple survey

    You need to give your relationships a bit more thought
    <Sigh> Don't we all

    If it helps, I like to think of problems like this in terms of "Templates" and "Instances". So the design of a survey, including all it's questions, is a "Template". When a Respondant completes a survey, including the answers they gave to each question, that's an "Instance" of your template.

    So start with the simple bit, design your template. Forget about the Respondent for now, we'll come back to that. You've said a survey consists of many questions so your template is ultra-simple, it's a Survey Table and a Question Table - Question has a foreign key to Survey.

    So we've got a Template, now let's think about Instances.

    To create an instance of a Template you need a corresponding set of tables that key back to the instance. So we need a SurveyInstance table (you have this, it's your Survey details table). This represents your respondent completing a survey. And you need AnswerInstance table which represents the answer given for each question on the instance - you don't need to relate it directly to the Respondent because it gets that relationship via the SurveyInstance table.

    If you draw this out you'll notice it forms a "square" with the template on the top and the instance on the bottom. (I don't have a design tool here so I hope this'll come out right):-
    Code:
    Survey <- Question
        ^        ^
        |        |
    Survey <- Answer
    Instance   Instance
    Note I've deliberately left the respondent off because I wanted to demonstrate the "square". So the last thing you need to of is relate the instance back to the respondent. That's just a matter of adding a foreign key to the Survey Instance. Here's that using your own table names:-
    Code:
                 Survey <- Question
                   ^          ^
                   |          |
    Respondent <- Survey <- Answer
                  Details
    Does that help?
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  10. #10

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    Re: SQL Schema for simple survey

    Does that help?
    Yes, very much! I see now that I needed another table to.. uhm... "keep" the answers for each user.. Maybe just by relevant ID's.. Perhaps a table with composite ID's between the respondent and the questions.. (i think) e.g.

    Code:
    tblAnswers
     - RespondentID
     - QuestionID
    Thank you very much I shall post back here

  11. #11
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: SQL Schema for simple survey

    between the respondent and the questions
    close but no cigar. It should be between the Instance Id (or SurveyDetailsId in your case) and the question. You would work out the Respondent for a particular answer by looking at the instance the answer was for and then looking at which respondent that instance was for.

    Only one thing in the structure needs to relate back to the respondent and that's the survey instance. Everything else hangs off that.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  12. #12

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    Re: SQL Schema for simple survey

    I am lost for I have no idea if this is right


    Attachment 158917

  13. #13
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: SQL Schema for simple survey

    You said that there's only one survey so you don't actually need the tblSurveys table. The rest is still wrong. Just forget that this is a database and think about how the world actually works.

    You said yourself that a single respondent can complete more than one survey so obviously the respondent is the one and the survey details is the many, so the tblSurveyDetails table need s foreign key to the tblRespondents table. You've got that part right but for some unknown reason you've made that foreign key column part of the primary key too. Every survey completed is going to consist of an answer for each question, so multiple answers, so the tblSurveyDetails table is the one and the tblAnswers table is the many, so the tblAnswers table needs a foreign key to the tblSurveyDetails table. You've got that relation the wrong way around. Do you really think that one answer is going to be part of multiple completed surveys? Etc.

    Think about the real world first because it is what your database is supposed to represent. Pick up a pen and paper and write it down: which is the one and which is the many.

  14. #14
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: SQL Schema for simple survey

    You said that there's only one survey so you don't actually need the tblSurveys table
    Oops, I missed that Yeah, get rid of the Survey table.

    To put what JM is saying another way, think of the relationships in terms of "Belongs To" and "Has". An Answer "Belongs To" an instance of a survey. an instance of a survey "Has" answers. Entities should have a foreign key to the Entities the "Belong To". They should not have a foreign key to the Entities they "Have".

    Similarly, a Question "Has" Answers. An answer "Belongs To" a question. So should Answer have a foreign key to Question, or should Question have a foreign key to answer?
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  15. #15
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: SQL Schema for simple survey

    The thing that frustrates me most is that you're tripping over stuff that I already gave you in your previous thread. I provided a schema diagram there that includes equivalents to all your tables but tblRespondents so you already have the relationships. My Response, Question and QuestionResponse tables are equivalent to your tblSurveyDetails, tblQuestions and tblAnswers tables respectively. All you need to do is extend the same principle to one more table.

  16. #16

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    Re: SQL Schema for simple survey

    Guys I am not trying to frustrate anyone. John, I did post back on that schema, but by then you obviously decided that you have reached the limit of how much you was prepared to assist. I wanted to rephrase the question and the thread was start to go off course anyways (as happened with many of my other threads) I therefore decided to resolve that thread.

    But if you feel frustrate then I apologize, it was not my intention. Sometimes I read something like 'this must have a foreign key back to that' (example) now to me this means it it must have the 'opposite direction' (the meaning of the word "back" ..etc) So when I am presented with cryptic clues to a problem it is not always easy to decipher all the meanings.

    I was learning many valuable things on vbforums, for which I am grateful. Sometimes easy, and also sometimes with pain.

    Okay as was suggested, I look this without thinking of a database.

    I want to give [Respondents] a set of [Questions] to [Answer]

    thus I can see that I need for starting, at least the following tables:

    1 - tblRespondents
    2 - tblQuestions
    3 - tblAnswers

    TblRespondents

    There can only be one unique person so it shall have a Primary Key and Identity

    TblQuestions

    There are 300 unique questions so this table shall have a Primary Key and Identity

    TblAnswers

    Every answer shall come from a respondent (ID) and relates to a specific question (ID) and shall have a Primary Key and Identity

    Now, I need another table. This table is the 'heart' of the survey. This table (tblSurveyDetails) must tell me:

    1 - When and where the survey was completed
    2 - By who it was completed (RespondentID)
    3 - What the question was (QuestionID)
    4 - What the answer was (AnswerID)

    Also this table can have many many records, so the columns all is many (relationship)


    Am I on track so far?
    Last edited by schoemr; May 9th, 2018 at 08:19 AM.

  17. #17

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    Re: SQL Schema for simple survey

    Code:
    USE [myDataBase]
    GO
    
    CREATE TABLE [dbo].[tblRespondents](
    	[RespondentID] [int] NOT NULL,
    	[RespondentName] [varchar](200) NULL,
    		primary key (RespondentID)
    );
    
    CREATE TABLE [dbo].[tblQuestions](
    	[QuestionID] [int] NOT NULL,
    	[Question] [varchar](200) NULL,
    		primary key (QuestionID)
    );
    
    CREATE TABLE [dbo].[tblAnswers](
    	[AnswerID] [int] NOT NULL,
    	[Answer] [varchar](200) NULL,
    		primary key (AnswerID)
    );
    
    CREATE TABLE [dbo].[tblSurveyDetails](
    	[SurveyDetailsID] [int] NOT NULL,
    	[RespondentID] [int] NOT NULL,
    	[QuestionID] [int] NOT NULL,
    	[AnswerID] [int] NULL,
    	[Date] [date] NULL,
    	[Place] [varchar](200) NULL,
    
    	primary key (SurveyDetailsID)
    );

  18. #18
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: SQL Schema for simple survey

    Quote Originally Posted by schoemr View Post
    Guys I am not trying to frustrate anyone.
    I realise that it's not the intention but you're ignoring information that has already been provided and repeating the same mistake more than once, so it is the result. You don't have to care if we/I get frustrated but it's a fact regardless.
    Quote Originally Posted by schoemr View Post
    John, I did post back on that schema, but by then you obviously decided that you have reached the limit of how much you was prepared to assist.
    Actually, I went to that thread to post a response only to find that you'd decided to give up on that one and start another. Keep in mind that we all have lives and responsibilities and we won't always have the time to provide what we consider a worthwhile response at a time that's convenient to you.
    Quote Originally Posted by schoemr View Post
    Now, I need another table. This table is the 'heart' of the survey. This table (tblSurveyDetails) must tell me:

    1 - When and where the survey was completed
    2 - By who it was completed (RespondentID)
    3 - What the question was (QuestionID)
    4 - What the answer was (AnswerID)

    Also this table can have many many records, so the columns all is many (relationship)
    No, absolutely not. A survey is composed of multiple questions, right? If that is the case, how can a survey details record possibly have a QuestionID? Yet again, one survey details record is related to multiple answer records. Which side of a 1:Many relationship does the foreign key go? The Many side. Is tblSurveyDetails the Many side? Again, it's the answer record that is related to the question record, NOT the survey details. One question has multiple answers, i.e. one for each survey details. The foreign keys are like this:

    tblSurveyDetails contains RespondentID
    tblAnswers contains SurveyDetailsID and QuestionID

    That's it, that's all.

  19. #19
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: SQL Schema for simple survey

    You're awfully close but not quite. Let's pick apart your statements one at a time:-
    TblRespondents

    There can only be one unique person so it shall have a Primary Key
    Yep, that sounds right and your create table statement for it looks right too. (Minor criticism, Identity has a specific meaning in SQLServer and it's not what you did in your create, don't worry too much about that now, just pretend you hadn't written "and Identity")

    TblQuestions

    There are 300 unique questions so this table shall have a Primary Key
    Again, spot on in both the statement and the corresponding create table statement

    Now's where it starts to go wrong though:-
    TblAnswers

    Every answer shall come from a respondent (ID) and relates to a specific question (ID) and shall have a Primary Key and Identity
    An answer certainly links to a question, that's correct. It [/I]could[/I] link to a respondent - but a respondent could complete the survey several times - meaning they could give several answers to the question. How will you know which occasion they gave each answer on?

    The answer is to link an answer to an "occasion" (or instance) of a survey. I think there's a bit of confusion over what you're doing with SurveyDetail and, I think, that's where you're problem really lies. So let's throw the SurveyDetail table away - it's creating confusion. Let's, instead, think about a "Completed survey"

    What does a Completed Survey consist of? It has a date, a respondent and a set of answers. Note that last bit: "has a set of", that's important. You can't express "a set" of something on a single record - so you can't put the answers directly on the table. Instead they'll be held in a child table (answers) which we'll come to in a second. So our CompletedSurvey consists of a primary key, a date and a respondant id:-
    Code:
    Create Table dbo.tblCompletedSurvey
    (
       CompletedSurveyId int Primary Key,
       CompletedDate Date,
       RespondentId int --<--this will be a foreign key to respondent
    )
    OK, now lets look at answers. An answer is an answer to a specific question, given by a specific respondent on a specific completed survey. So what would that look like. Your first pass might look like this:-
    Code:
    Create Table dbo.tblAnswer
    (
       AnswerId Primary Key,
       QuestionId, --<--This will be a foreign key to the question table so we know which question was answered
       RespondentId, --<this will be a foreign key the respondent table so we know who answered it
       CompletedSurveyId  --<-- this will be a foreign key to CompletedSurvey so we know when they answered it
    )
    That's a complete schema. Can you see that this allows you to keep track of the occasions that a respondent completed the survey (the tblCompletedSurvey table) and the answers they gave on each occasion (because the answers table refers to the Completed Survey table?

    There's a sting in the tail though: We don't need to keep the respondent in the answers table. We know which Completed Survey the answer was given on because we have the CompletedSurveyId in the Answer table. And we know who completed each Completed Survey because we have the RespondentId in the CompletedSurvey table. So we can infer the respondent for each answer by looking it up in the Completed Survey table.

    It's a fundamental of database design that you don't hold the same information twice (it allows inconsistency), so having the respondent id in both the Answers table and the CompletedSurvey table is a bad thing. We can't remove it form the CompletedSurvey table or we wouldn't be able to tell who completed the survey. But we can remove it from the Annwers table because, as explained above, we can infer the respondent for an answer by looking it up in the Completed Survey table. So we remove it from the Answers table where it's redundant. You're left with:-

    Code:
    Create Table dbo.tblAnswer
    (
       AnswerId Primary Key,
       QuestionId, --<--This will be a foreign key to the question table so we know which question was answered
       CompletedSurveyId  --<-- this will be a foreign key to CompletedSurvey so we know when they answered it
    )
    And that's now a complete schema.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  20. #20

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    Re: SQL Schema for simple survey

    Keep in mind that we all have lives and responsibilities and we won't always have the time to provide what we consider a worthwhile response at a time that's convenient to you
    .

    I realize that John, and once more, thank you very much for your time. I appreciate that tremendously.

    It has been a few days that I am struggling with these 4 tables, for something that I thought would be simple. This is where I am at now:

    Attachment 158919

    I have headache. I shall come back later or tomorrow.

    Thanks Dexter for your help as well.

  21. #21
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: SQL Schema for simple survey

    You can get rid of the SurveyID column in tblQuestions and you're done. The thing is though, if you don't understand how relationships and foreign keys work then you'll have the same issues again next time. You'll need to find a way to make sense of it.

  22. #22
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: SQL Schema for simple survey

    That looks about right.

    The only thing I can see which is questionable is the presence of the SurveyId in tblQuestions. That looks like some hangover from a previous iteration that you've forgotten to remove. It's not doing anything, though, so get rid of it.

    Don't let your struggle with this get to you. Once they click, foreign keys become kinda obvious. But I've known people struggle for ages before it finally clicked into place.

    JM is right though, you need to spend time getting your head round them because they're an absolute fundamental of DB design.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  23. #23

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    Re: SQL Schema for simple survey

    Don't let your struggle with this get to you. Once they click, foreign keys become kinda obvious. But I've known people struggle for ages before it finally clicked into place.
    Yeah, for you guys it is like second nature, you not even have to think about it. I am very far from that. I can only say that I am trying very hard and in this field there is HUGE amounts of things to learn. To be honest I only make one database before.

    I think to make it CompletedSurvey is better word... It makes more sense.....


    May I please ask some follow up question?

    Code:
    Create Table dbo.tblAnswer
    (
       AnswerId Primary Key,
       QuestionId, --<--This will be a foreign key to the question table so we know which question was answered
       CompletedSurveyId  --<-- this will be a foreign key to CompletedSurvey so we know when they answered it
       Answer
    )
    Musnt there be a column to keep the answer? Like above?

    Also, and this is where I cannot wrap my brain around it. I look at many survey schema this week, and in all of them is one think that is not clear to me.

    How is questions given or presented to respondnet? Meaning....

    The respondent must see a form with a list of all her "instances" where she answered the survey. If she open a survey she can see her answers to all the questions. Also, if a new survey (another instance of that survey) is started there must be a form with questions where she can give the answers.


    If there is no link a respondent and questions, how is this achieved. JMC say to create a datatable, but even so, that table will not work if there is no link? Or how?

    Lets say there is 2 questions:

    1- How do you feel today?
    2- What did you have for breakfast this morning?

    Mary can answer these two questions every day of week. Every time she answers this, is new 'instance' or "date" (this is how I understand what you say above) Every time Mary answer this questions it is a new completed survey.

    But Jane can also answer these two questions multiple times...

    Thank you so much for all the advice and information thus far
    Last edited by schoemr; May 10th, 2018 at 02:19 AM.

  24. #24

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    Re: SQL Schema for simple survey

    Just for interest, this one schema I found online at http://www.vertabelo.com/blog/techni...-survey-part-1

    Attachment 158927

  25. #25
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: SQL Schema for simple survey

    Quote Originally Posted by schoemr View Post
    Also, and this is where I cannot wrap my brain around it. I look at many survey schema this week, and in all of them is one think that is not clear to me.

    How is questions given or presented to respondnet?
    What your data looks like in the database doesn't have to be anything like what your data looks like in your application. There is usually a fairly close correlation but it certainly doesn't have to be 1:1.

    One option is to create a DataTable with columns for CompletedSurveyID, QuestionID, Question and Answer. When you display it to the user, you'd only show them the last two columns. When it comes time to save, you'd save to the CompletedSurvey table first and get back a CompletedSurveyID for that new record and propagate that to each row of the DataTable. You'd then save CompletedSurveyID, QuestionID and Answer columns to the Answer table. The Question column gets ignored because it was only for display purposes. This DataTable doesn't map directly to anything in the database. It is for presentation purposes. You simply do the required massaging when you retrieve data (you'd populate it from the Question table with the first and last columns remaining NULL) and when you save data.

  26. #26
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: SQL Schema for simple survey

    Musnt there be a column to keep the answer? Like above?
    Yes. That was an oversight on my part I guess I was too focused on explaining the keys and relationships.

    What your data looks like in the database doesn't have to be anything like what your data looks like in your application. There is usually a fairly close correlation but it certainly doesn't have to be 1:1
    I want to give JMc a big hug for saying this! Unfortunately the forum doesn't have a hug smiley so he'll just have to make do with the physic one I'm willing on him.

    They do not have to match. The database should be designed to hold the data in a normalized form (if you're not sure what I mean by that, have a google for "Third Normal form"). That can broadly be translated to "the minimum structure required to hold all the necessary data". The UI should be designed to present that data to a user in an easily digestible manner. It's likely to contain all sorts of redundancy and repetition. Then we write a load of wiring in the middle to translate from one to the other and back again. Annoyingly, every online tutorial you find seems to map the UI straight onto the DB or vice versa - probably because most of them are trying to demonstrate how clever the latest framework is at auto-generating one from the other. Grrrr!

    JMc's answer focusses on the technical side so I'll offer a more high level response to your questions - hopefully both will be useful.


    1.
    The respondent must see a form with a list of all her "instances" where she answered the survey
    OK, so you'll populate some kind of selection device (probably a combobox or similar) with all of a users completed surveys. The query will look something like "Select CompletedSurveyId, Place + ' ' + Date as Display From CompletedSurveys Where RespondentId = @CurrentRespondent".

    2.
    If she open a survey she can see her answers to all the questions.
    So when she makes a selection in the combobox, that will give you the CompletedSurveyId she selected. You use that to get the answers she gave and display them on your form. The query would look something like "Select Q.Question, A.Answer From Questions Q Join Answers A on Q.QuestionId = A.QuestionId Where A.CompletedSurveyId = @CompletedSurveyId"

    Note that in step 2 we no longer care that we're dealing with one of Mary's completed surveys. We just know we're dealing with whatever survey was selected. It happens to be one of Mary's because those are the only ones we made available for selection in step 1... but all we care about in step 2 is that Survey X was selected. So we go and get the answers to Survey X.

    Also, if a new survey (another instance of that survey) is started there must be a form with questions where she can give the answers.
    No problem. So the query to get the questions, with no answers is simply "Select Q.QuestionId, Q.Question From Questions Q".

    Once Mary completes the survey you create a new CompletedSurvey record with the time and the place: "Insert into Completedsurveys (CompletedSurveyId, RespondentId, Date ,Place) Values @NewCompletedSurveyId, @RespondentId, @Date, @Place)" and you create a record in the answers table for each of the answers she gave (so this might be in some sort of loop): "Insert into Answers (AnswerId, CompletedSurveyId, QuestionId, Answer) Values (@NewAnswerId, @NewCompletedSurveyId, @QuestionId, @Answer)"

    Note, these are just sample queries and yours will look slightly different but hopefully you'll get the idea. For example, you might choose to auto-generate your NewCompletedSurveyId and NewAnswerId values using identity fields, in which case they wouldn't need to be passed in as parameters. You might choose to use Data Tables and Adapters to handle all of this, in which case the need to create a loop to handle your answers is likely to be hidden away from you and handled automatically.

    All I'm really saying here is, don't get wedded to the queries I've suggested here - yours are likely to differ depending on how you choose to wire it all up.

    But hopefully this demonstrates why an answer doesn't need to know which respondent it belongs to. An answer belongs to a completed survey. A completed survey belongs to a respondent. Therefore an answer belongs to a respondent.

    But Jane can also answer these two questions multiple times...
    Just to check, she can only answer them once on each Completed Survey, correct? They wouldn't appear more than once on a survey? That's the assumption both JMc and I have made and, if it's wrong, then we'd need to revisit the advice we've given you.

    How do you feel today?
    Lovely

    What did you have for breakfast this morning?
    Eggs. They were lovely too. Nomnomnom.
    Last edited by FunkyDexter; May 10th, 2018 at 04:56 AM.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  27. #27

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    Re: SQL Schema for simple survey

    Hi Guys,

    I am very happy for all the information and for most your forbearance with this thread that you have given. Thank you very much... I think I have enough information now to get started. There is just one aspect I please can clarify - for all of this above to work this part is very, very important:

    JMC:

    When it comes time to save, you'd save to the CompletedSurvey table first and get back a CompletedSurveyID for that new record and propagate that to each row of the DataTable. You'd then save CompletedSurveyID, QuestionID and Answer columns to the Answer table.

    What you are saying is I first have to make 'a' save to the database in order to get CompletedSurveyID back? So I [think] then I would somehow need to get that last row that was added in that table and once that ID was retrieve I must do some
    Code:
    For Each Row in DataGridviewRows
    to insert that ID in the Answers table. (?) But... what happens if say Person A press save button and immediately after Person B press save button. Person A shall get the wrong ID back?

    What happens (theoretical) if 10 persons complete survey at same time?


    Or how?

  28. #28
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: SQL Schema for simple survey

    Quote Originally Posted by schoemr View Post
    But... what happens if say Person A press save button and immediately after Person B press save button. Person A shall get the wrong ID back?

    What happens (theoretical) if 10 persons complete survey at same time?
    If you do it properly, it doesn't matter how many users save at the same time. Using SQL Server, you can incorporate the query to get the auto-generated ID into the same command as the INSERT statement and you'll always get the ID that was generated within the scope of that command. There's no chance of interference between commands, e.g.
    vb.net Code:
    1. Dim parentId As Integer
    2.  
    3. Using connection As New SqlConnection("connection string here"),
    4.       command As New SqlCommand("INSERT INTO Parent (ParentName) VALUES (@ParentName); SELECT @ParentId = SCOPE_IDENTITY();", connection)
    5.     Dim parameter As SqlParameter
    6.  
    7.     With command.Parameters
    8.         .Add("@ParentName", SqlDbType.VarChar, 50).Value = "New Parent"
    9.         parameter = .Add("@ParentId", SqlDbType.Int)
    10.         parameter.Direction = ParameterDirection.InputOutput
    11.     End With
    12.  
    13.     connection.Open()
    14.     command.ExecuteNonQuery()
    15.  
    16.     parentId = CInt(parameter.Value)
    17. End Using
    It's the SQL Server SCOPE_IDENTITY function that gets the last ID generated within the current scope. The @@IDENTITY variable is similar but is global, so using that could cause issues with multiple users.

    I should have some time this weekend to throw together a quick demo app. Are you going to be using a typed DataSet for this?

  29. #29

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    Re: SQL Schema for simple survey

    Hi, yes it is a typed dataset

    I should have some time this weekend to throw together a quick demo app.
    Now I also want that hug smiley!

  30. #30
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: SQL Schema for simple survey

    Awww, I'm feeling the love.

  31. #31
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: SQL Schema for simple survey

    Here is the promised demo project, created in VS 2017 against .NET 4.7.1. Note that SurveyResponseDataTable in the typed DataSet is what I added myself, while the other DataTables were generated automatically from the database. It includes columns that are conceptually from the Question and Answer tables and it has a foreign key to the Survey table. The corresponding table adapter retrieves data from the Question table and saves data to the Answer table. You should examine it in the DataSet designer and make use of the Properties window because that's what I had to do to create it.
    Attached Files Attached Files

  32. #32

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    Re: SQL Schema for simple survey

    Hello John,

    Thank you so very much for this! I hope all people out there wants to do survey can find this thread. I can now examine carefully how you do this, and hopefully learn more

    Oh how I wish I have your brains!!!

  33. #33
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: SQL Schema for simple survey

    JMc doesn't have brains. Just some cogs and a few resistors. True fact.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  34. #34
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: SQL Schema for simple survey

    Quote Originally Posted by FunkyDexter View Post
    JMc doesn't have brains. Just some cogs and a few resistors. True fact.
    More like spider webs and a few dead cockroaches.

  35. #35
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: SQL Schema for simple survey

    Since I moved to Bristol I've running on a pint of cider and a couple of pasties, so you've got me beat.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  36. #36

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    Re: SQL Schema for simple survey

    JMC, may I please ask (for curiosity) how long did it take you to make this demo?

  37. #37
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: SQL Schema for simple survey

    Quote Originally Posted by schoemr View Post
    JMC, may I please ask (for curiosity) how long did it take you to make this demo?
    The difference between the timestamps on the solution file for the project and the ZIP file I uploaded is 2 hours and 18 minutes.

  38. #38
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: SQL Schema for simple survey

    I've been trying to send another PM but it keeps failing so I'll try here:

    I tried the following and it worked, so it should work for you.

    1. Create a new database against your SQL Server instance in SSMS.
    2. Execute the following script in SSMS against that database to create the schema and add the default data:
    sql Code:
    1. CREATE TABLE [dbo].[Respondent] (
    2.     [RespondentId] INT          IDENTITY (1, 1) NOT NULL,
    3.     [Name]         VARCHAR (50) NOT NULL,
    4.     PRIMARY KEY CLUSTERED ([RespondentId] ASC)
    5. );
    6.  
    7. CREATE TABLE [dbo].[Survey] (
    8.     [SurveyId]     INT          IDENTITY (1, 1) NOT NULL,
    9.     [RespondentId] INT          NOT NULL,
    10.     [Date]         DATE         NOT NULL,
    11.     [Location]     VARCHAR (50) NOT NULL,
    12.     PRIMARY KEY CLUSTERED ([SurveyId] ASC),
    13.     CONSTRAINT [FK_Survey_Respondent] FOREIGN KEY ([RespondentId]) REFERENCES [dbo].[Respondent] ([RespondentId])
    14. );
    15.  
    16. CREATE TABLE [dbo].[Question] (
    17.     [QuestionId] INT          IDENTITY (1, 1) NOT NULL,
    18.     [Text]       VARCHAR (50) NOT NULL,
    19.     PRIMARY KEY CLUSTERED ([QuestionId] ASC)
    20. );
    21.  
    22. CREATE TABLE [dbo].[Answer] (
    23.     [AnswerId]   INT IDENTITY (1, 1) NOT NULL,
    24.     [SurveyId]   INT NOT NULL,
    25.     [QuestionId] INT NOT NULL,
    26.     [Response]   BIT NOT NULL,
    27.     PRIMARY KEY CLUSTERED ([AnswerId] ASC),
    28.     CONSTRAINT [FK_Answer_Question] FOREIGN KEY ([QuestionId]) REFERENCES [dbo].[Question] ([QuestionId]),
    29.     CONSTRAINT [FK_Answer_Survey] FOREIGN KEY ([SurveyId]) REFERENCES [dbo].[Survey] ([SurveyId])
    30. );
    31.  
    32. SET IDENTITY_INSERT [dbo].[Respondent] ON
    33. INSERT INTO [dbo].[Respondent] ([RespondentId], [Name]) VALUES (1, N'Peter Smith')
    34. INSERT INTO [dbo].[Respondent] ([RespondentId], [Name]) VALUES (2, N'Paul Jones')
    35. INSERT INTO [dbo].[Respondent] ([RespondentId], [Name]) VALUES (3, N'Mary Johnson')
    36. SET IDENTITY_INSERT [dbo].[Respondent] OFF
    37.  
    38. SET IDENTITY_INSERT [dbo].[Question] ON
    39. INSERT INTO [dbo].[Question] ([QuestionId], [Text]) VALUES (1, N'Do you like eating chocolate?')
    40. INSERT INTO [dbo].[Question] ([QuestionId], [Text]) VALUES (2, N'Do you like playing with puppies?')
    41. INSERT INTO [dbo].[Question] ([QuestionId], [Text]) VALUES (3, N'Have you ever travelled overseas?')
    42. SET IDENTITY_INSERT [dbo].[Question] OFF
    3. Open the solution from the ZIP file in VS.
    4. Delete the Database1.mdf file from the Solution Explorer.
    5. Edit the App.config file and change the connection string appropriately, e.g.
    Data Source=(local);Initial Catalog=SurveyDemo;Integrated Security=True
    I did that and ran the project and was able to successfully create and save three surveys. The three records were added to the Survey table and nine records were added to the Answer table as expected. Unexpectedly, the RespondentId was 1 and the Location was empty for all three Survey records. Obviously something is not right there but I'll leave that to you.

  39. #39

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    Re: SQL Schema for simple survey

    Hello John,

    I finally got it to work

    Unexpectedly, the RespondentId was 1 and the Location was empty for all three Survey records. Obviously something is not right there but I'll leave that to you.
    To overcome this I added the following to the save button (but I think you already knew that)

    Code:
    Me.SurveyBindingSource.EndEdit()
    Once again, thank you very much for making this demo. I am very appreciative for this. I am sure maybe other people too can use this.

    Michelle

  40. #40

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    Re: SQL Schema for simple survey

    Quote Originally Posted by jmcilhinney View Post
    Here is the promised demo project, created in VS 2017 against .NET 4.7.1. Note that SurveyResponseDataTable in the typed DataSet is what I added myself, while the other DataTables were generated automatically from the database. It includes columns that are conceptually from the Question and Answer tables and it has a foreign key to the Survey table. The corresponding table adapter retrieves data from the Question table and saves data to the Answer table. You should examine it in the DataSet designer and make use of the Properties window because that's what I had to do to create it.
    Hi John,

    I know this is an old thread and resolved but I was hope to ask follow up question...

    In your demo with new button you place following code:

    Code:
    Private Sub newButton_Click(sender As Object, e As EventArgs) Handles newButton.Click
            Dim survey = DirectCast(DirectCast(Me.SurveyBindingSource.AddNew(), DataRowView).Row, Database1DataSet.SurveyRow)
    
            'Set default values for Survey record.
            With survey
                .RespondentId = Me.Database1DataSet.Respondent(0).RespondentId
                ._Date = Date.Today
                .Location = String.Empty
            End With
    
            Me.SurveyBindingSource.EndEdit()
    
            'Do not call AcceptChanges so all rows are Added and ready to insert.
            Me.SurveyResponseTableAdapter.Adapter.AcceptChangesDuringFill = False
    
            'Create Question/Answer records for survey.
            Me.SurveyResponseTableAdapter.Fill(Me.Database1DataSet.SurveyResponse)
    
            'Each Answer will automatically have its SurveyId set when the Survey record is inserted.
            For Each surveyResponse As Database1DataSet.SurveyResponseRow In Me.Database1DataSet.SurveyResponse
                surveyResponse.SurveyRow = survey
    
            Next
    
            'Configure UI.
            Me.newButton.Enabled = False
            Me.respondentComboBox.Enabled = True
            Me.datePicker.Enabled = True
            Me.locationTextBox.Enabled = True
            Me.surveyResponseDataGridView.Enabled = True
        End Sub
    I am unable to figure out this part in red. I have try and try I just don't get it.

    What I have done is to start a new project, then I added the dataset from the database and created the two extra tableadapters, exactly the way you have done. Everything is exactly like your demo. But the error say that
    Code:
    SurveyRow is not a member of DataBase1DataSet.SurveyResponseRow
    What am I missing, please?
    Last edited by schoemr; Oct 30th, 2018 at 08:52 AM.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width