Design question

[Guest]

I have to build a database for an application process with a lot of questions
to be answered in the form of checkboxes, ie yes/no fields. I am not sure as
to where to put these. Should I make a separate table for each category,
throw them all together into 1 table, or not even put them into a separate
table at all?

 

[(PeteCresswell)]

Per Niniel:

to be answered in the form of checkboxes, ie yes/no fields. I am not sure as
to where to put these. Should I make a separate table for each category,
throw them all together into 1 table, or not even put them into a separate
table at all?

My kneejerk reaction would be to put them all in one table... *IF* I were
reasonably sure there would never, ever, be more than 250 questions and if
I didn't expect to clone the app for other situations that might have more
questions.


OTOH, I suspect that the technically-correct approach from a database design
perspective would be something like one table for the responses - but with a
"QuestionID" field.

One person's responses would generate many records in this table: one for each
question. Then the QuestionID field would point to a lookup table containing
one record for each question.

One advantage to the more complicated design would be scalability. Now you can
cope with a virtually-unlimited number of questions on a survey.

The downsides would be in gathering and reporting.

On the gathering side, you'd have to do extra coding on the input form - maybe
build the checkboxes dynamically depending on the records in the lookup table.

On the reporting side there would likewise be more coding: maybe a pivot table
presentation of the data with dynamic assignment of column headings.


Manhour-wise, I'd think the diff between the two approaches would be rather
large - like maybe as much as 1:10.

 

[Guest]

A single record is limited to 2000 characters. Yes/No is a very small
character set. So when you say you have "alot" of questions - then of course
one is not quite clear really how many you actually have.

That aside; if you have 100+.....the description of the question could
drive the character count way up above 2000 for any single record. So what
you want is table with all the questions and each question is numbered; i.e.

Q1 blah blah blah?
Q2 more blah blah blah?

Then you want another table with a record of each person's answers. But
only refer to the question by ID# Not the actual question - to keep the
character count down. So it will look like this:

Name Q1 Q2 Q3 Q4 Q5 etc

Joe Y N Y N Y etc.
Fred N Y N Y N

In this table you just want the person's ID and their response - you don't
want to re-record the actual question each time.

You would have a basic 1-to-many relationship between the Question Table and
the Answers Table for the question ID column
Obviously in terms of long term database management one can never edit the
Questions table or re-use a question ID - - otherwise prior answers really
are no longer valid data....

 

[Guest]

Yes, it could very well be 100 + questions, depending on how I organize
things. So that character limit you mentioned is definitely something I need
to keep in mind, thank you.
Also thank you for your design idea. I will have to discuss that, especially
the limitations this would carry.

 

[Smartin]

PMFJI, Memo fields only contribute a handful of bytes to the "character"
count, to handle the pointers.

According to F1 (A2K2):
"Number of characters in a record (excluding Memo and OLE Object fields)
: 2,000"

 

[Guest]

The statement doesn't mention bytes, it only mentions characters.

LDN

 

[Smartin]

roccogrand wrote:

Sorry, I was freely exchanging characters for bytes.

Regardless, a memo typed field weighs in only for its existence, not for
its content.

HTH

 

[Smartin]

roccogrand wrote:

Sorry, I was freely exchanging characters for bytes.

To put it another way, the "character" count specified in F1 more likely
is based on an underlying byte limitation. Since one character usually
costs one byte, I substituted.

The reason is, the "characters" that are counted are not really
"characters" at all when considering a memo field in a table or query.
Rather, they are a handful of bytes that point to the memo field's
content. In comparison to other field values, these pointers bear little
resemblance to "characters".

 

[John Nurick]

Although Help says "Number of characters in a record (excluding Memo and
OLE Object fields) 2,000", the practical limit is often greater. The
actual limit seems to be about 4,000 bytes, i.e. 2,000 two-byte Unicode
characters.

If one's using a western European alphabet and Unicode compression is
enabled, the 2,000 bytes can potentially hold a lot more than 2,000
characters.

OTOH I don't know what happens with Unicode characters that don't have a
two-byte representation. Has anyone tried?

 

[Guest]

Here's another issue that I have with NTS' response to Niniel:

"one can NEVER (caps are mine) edit the questions table..."

I disagree. If there is a typographical or grammatical error in the master
Questions table then one needs the ability to EDIT this table.

In a questionnaire module that I built into one of my databases, I included
a form for that purpose. Users can add new questions and delete old ones
from the master Questions table, and change the text as they see fit.

Questions answered about each 'project' are unchanged because they are
stored in a separate 'project questions table.' And yes a user with
knowledge of Access can even bulk edit them but my users don't currently have
those skills. I could bulk change the questions if I wanted to as long as I
was sure that I wouldn't change the original meaning.

Another way of thinking about this issue is, "Are normal database
constraints applicable to questionnaires?" What if a person changes their
mind about a question/issue weeks after they filled out the questionnaire?
Is that a new questionnaire? In database terms, Is that another 100+
records?

LDN