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.
-
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...
-
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.
-
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 , 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?
-
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...).
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.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.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).
-
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?
-
"Automatically pluralize object names"
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.
-
Damn, only 547 characters. How do they get longer than that?
You've never had auditors make questions, have you?
Why is that even important?
Perhaps they want code to read like conversations?
-
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.
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.
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.
-
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.
I've managed to write an Excel XML export utility
But I anticipate this method won't be very efficient in the long run...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.
-
Oh, if you just want straight answers, that's easy. Just look for QuestionDatas of a given QuestionID.
-
select *
from dbo.ApplicantSentences
where ApplicantId = @ApplicantId
and Question like '%Strengths & Weaknesses%'What was giving me the screaming 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
forTitle like '%Strengths & Weaknesses%'
, and then get theQuestionID
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?
-
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.
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.