Dbo.ApplicantSentences



  • In a previous life, I was responsible for helping an important client maintain an instance of one of our webapps, which allowed members of the general public to apply for services rendered by our client. They were making some changes to the content, and wanted to verify that everything would work properly.

    "The Strengths and Weaknesses question isn't showing on this applicant's profile page," said my contact at the client, with a hint of disappointment in his voice.

    "Can you give me the applicant's information? I'll look into the backend and let you know what I find," I confidently replied. The app should have been collecting the data. This also would allow me to end the call and get a temporary respite. A few minutes later, I had dug up the page that wasn't displaying the information that it should have, and found the query that should have been fetching the question and response:

    select *
    from dbo.ApplicantSentences
    where ApplicantId = @ApplicantId
    and Question like '%Strengths & Weaknesses%'
    

    I take a look at all the rows for this particular applicant, and notice that there are no questions containing that particular text, but there is one that's "Strengths and Weaknesses." The client must have changed the wording on that particular question. Now, where did the questions get stored?

    create table dbo.ApplicantSentences
    (
        ApplicantId int not null,
        Question varchar(896) not null,
        Response text,
        constraint pk_ApplicantSentences primary key (ApplicantId, Question)
    )
    

    The phone rings. It's my contact again. "Hey, one more thing. I noticed that some of the longer questions seem to be getting truncated when I fill in answers with my test account. While you're looking into the profile page issue, can you look at that as well?"



  • This will also work pretty well with internationalization, I presume.



  • On first thought it seems like a bad idea to store the question as a string within the table each time, but what if the question ever gets changed, this keeps the question history correct with the answer.


  • Notification Spam Recipient

    @Ashley_Sheridan said:

    bad idea to store the question as a string within the table
    Yeah, and I haven't implemented version history for questionnaires submitted pre-change.
    ...
    Oh wait, we're not talking about the stuff I built in a week, are we? 😅
    I'll leave this here...


    Filed under: This is the schema of a database I was talking about in this thread...



  • @cartman82 said:

    This will also work pretty well with internationalization, I presume.

    At the time, the company that had this lovely schema was entering the international market and just starting to wrap its head around the idea that it is possible for dates to be expressed in formats that aren't MM/DD/YYYY, for currencies to be not in $, and for local time that disagrees with "whatever the server says it is."

    They were lucky that the ANSI codepage in use contained enough accented characters to be usable in most of Western Europe.



  • @Ashley_Sheridan said:

    On first thought it seems like a bad idea to store the question as a string within the table each time, but what if the question ever gets changed, this keeps the question history correct with the answer.

    There's a good argument for baking the question as originally posed along with the answer. However, making it part of the primary key, or making the question text a key for reporting presents some... problems... as shown above.

    SQL Server supports up to 900 bytes per index key. If you're using anything close to that amount, you're probably :doing_it_wrong:, and having a PK that wide is going to ensure that any other indexes on the table will be similarly bloated, in addition to all the fragmentation issues inherent in wide, natural PKs.



  • Hmm.

    • Why is FormInfo not part of Form? Most of that appears to be 1:1.
    • How come some tables are named as singular and others as plural?
    • How come some column names have underscores and some don't?
    • Is QuestionValues a template for QuestionData? If so, is Answer a stringly-typed bucket?

  • Notification Spam Recipient

    @Groaner said:

    FormInfo not part of Form?

    Not sure. I think the rationale was that it is possible to create the form with just a name and SectionID, but not necessarily need the other bits (though, creation date and all that could just as easily have defaults...).

    @Groaner said:

    How come some tables are named as singular and others as plural?
    Microsoft. I blame Entity Framework entirely for that, and by the time I figured out where to turn off the setting that "Automatically pluralize object names", it was too late.

    @Groaner said:

    How come some column names have underscores and some don't?

    Corporate best practices mandate that tables that have rows that can be changed have CREATED_BY, CREATED_ON (etc) in them. I didn't want to catch flak for that later.

    @Groaner said:

    Is QuestionValues a template for QuestionData? If so, is Answer a stringly-typed bucket?

    QuestionValues holds (optional) values for the input type configured for that question. If it's a free-form text, there should be no values, but if it's a CheckBox, Radio Button, or DropDown, there should be at least one value (to select from, of course).
    Answer is definitely a string (varchar(200) IIRC).

    It's going to be interesting when I start working on reporting capabilities for this thing, since forms (at present) can be modified at any time, so any flat-file representation of the submission results needs to be highly dynamic.
    At present, I've managed to write an Excel XML export utility that spits out a sheet describing the current form question state, and a sheet with the question data (in the current question order).



  • @cartman82 said:

    internationalization

    jbojevysofkemsuzgugje'ake'eborkemfaipaltrusi'oke'ekemgubyseltru is only 63 letters. Are you telling me the question is so complicated that it has to include "USSR lojban speakers" more than ten times?



  • In the past ten years, over all the interactions you've had with $COMPANY_NAME_LONG_FORM_INC_PLC_LTD_AB, including its subsidiaries, how would you rate your customer experience, whether in our physical $COMPANY_NAME stores or on our website, $COMPANY_NAME_DOT_COM, as it applies to the ease of use, cleanliness, friendliness, usefulness, and speed of the the service you receive in the checkout area, on a scale from 1 (Extremely Dissatisfied) to 10 (Extremely Satisfied)? Please do not include any personal identifying information in your answer.

    Damn, only 547 characters. How do they get longer than that?



  • @Tsaukpaetra said:

    "Automatically pluralize object names"

    :wtf:

    Why is that even important?

    Reminds me of the old days when I'd write something like: "I went to their party. They’re having a lot of fun." Microsoft wanted to 'correct' "their" to "they're" for me. At least that was sort of worth trying to program in.


  • Notification Spam Recipient

    @TwelveBaud said:

    Damn, only 547 characters. How do they get longer than that?

    You've never had auditors make questions, have you?

    @redwizard said:

    Why is that even important?

    Perhaps they want code to read like conversations?



  • @Tsaukpaetra said:

    Microsoft. I blame Entity Framework entirely for that, and by the time I figured out where to turn off the setting that "Automatically pluralize object names", it was too late.

    Interesting. I've met people who insist that all table names should be singular, or that all table names should be plural, but rarely are the two philosophies mixed. These sorts of differences tend to incite religious wars.

    @Tsaukpaetra said:

    Corporate best practices mandate that tables that have rows that can be changed have CREATED_BY, CREATED_ON (etc) in them. I didn't want to catch flak for that later.

    I've also met people who insist that table names should be PascalCased, and others who prefer underscores, but never both in the same table. These sorts of differences also tend to incite religious wars.

    @Tsaukpaetra said:

    QuestionValues holds (optional) values for the input type configured for that question. If it's a free-form text, there should be no values, but if it's a CheckBox, Radio Button, or DropDown, there should be at least one value (to select from, of course).Answer is definitely a string (varchar(200) IIRC).

    It's going to be interesting when I start working on reporting capabilities for this thing, since forms (at present) can be modified at any time, so any flat-file representation of the submission results needs to be highly dynamic.At present, I've managed to write an Excel XML export utility that spits out a sheet describing the current form question state, and a sheet with the question data (in the current question order).

    As long as there's some way to persist the type along with the value (such as QuestionValues.ValueID), you can mitigate EAV type conversion hell. I try to avoid anything that resembles EAV unless the values can be strongly typed.


  • Notification Spam Recipient

    @Groaner said:

    As long as there's some way to persist the type along with the value (such as QuestionValues.ValueID), you can mitigate EAV type conversion hell. I try to avoid anything that resembles EAV unless the values can be strongly typed.

    Well, most of the questions are either free-form text or integer values, and the ones that make the questions are aware of that. They're probably fine keeping everything as a string (since most of those strings are going to be things like "Y" or "N") when they're doing their analysis (It sounds like they want to do everything in Excel anyways).

    My trouble is making a (somewhat) consistent export that won't eat up memory when the number of submissions gets large.

    @Tsaukpaetra said:

    I've managed to write an Excel XML export utility
    But I anticipate this method won't be very efficient in the long run...

    @Groaner said:

    These sorts of differences also tend to incite religious wars.
    Oh, trust me, I want a religious war on this project. The whole thing pretty much incites me just knowing all the things I've had to violate to get it off the ground...



  • The databases designs make me want to say, "Normalize!" in a chirpy teacher voice.

    Then we get to the method of searching for question answers...and I just want to scream and run.


  • Notification Spam Recipient

    Oh, if you just want straight answers, that's easy. Just look for QuestionDatas of a given QuestionID. 😛



  • @Groaner said:

    select *
    from dbo.ApplicantSentences
    where ApplicantId = @ApplicantId
    and Question like '%Strengths & Weaknesses%'

    What was giving me the screaming :doing_it_wrong: horrors was this little gem:

        select *
        from dbo.ApplicantSentences
        where ApplicantId = @ApplicantId
        and Question like '%Strengths & Weaknesses%'
    

    The correspondent in the other database would be to search table Question for Title like '%Strengths & Weaknesses%', and then get the QuestionID and...*shudder*

    Broken, as discussed, by the users revising the question to say "Strengths and Weaknesses". For which I suppose the recommended fix would be to just do this:

        select *
        from dbo.ApplicantSentences
        where ApplicantId = @ApplicantId
        and ( Question like '%Strengths & Weaknesses%'
              or Question like '%Strengths and Weaknesses%' )
    

    Talk about fragile code. So, like, what, are they going for job security here?



  • @CoyneTheDup said:

    Broken, as discussed, by the users revising the question to say "Strengths and Weaknesses". For which I suppose the recommended fix would be to just do this:

    Provided that the underlying schema can't change, that's probably your best bet, sadly enough.

    @CoyneTheDup said:

    Talk about fragile code. So, like, what, are they going for job security here?

    The guy who designed dbo.ApplicantSentences was at his first job out of college and had probably never touched a database in his life prior. Management had this belief that if you are about to build a new application, you should assign it to the new guy and not someone with architectural and domain experience because New Blood, New Ideasâ„¢. That guy exited the company after about a year and left behind much other technical debt, not surprisingly.

    The guy who designed the profile page (which was built about a year later) that was supposed to report on that unreportable data was probably cursing his name left and right while implementing that good-enough-for-now hack that breaks the moment the client rewords a question. By then it was way too late to fix anything.


Log in to reply