J
Jim C
Hello All,
I'm trying to design my first somewhat complex database, and can use
some help. Basically, it's an employee Exit Interview database, and
was wondering if someone could review the table design that I'm trying
to put together.
The gist of my difficulties is that I have three different types of
answers (yes/no, text, and likert-type scales . . . eg excellent, good,
fair, poor . . . i know that these are essentially multiple yes/no data
type reponses . . . ), I'm not sure if I'm handling the answer types
and table designs correctly. Does anyone have any suggestions? I have
looked at the "At Your Survey" database that was created by an Access
MVP, but want to actually create my own application as a learning
process.
As a reference, I've reviewed a number of postings about survey
database designs, including the threads and info found in the following
links:
http://www.dbforums.com/showthread.php?t=1081046&highlight=survey+database
http://www.databaseanswers.org/data_models/index.htm
http://groups.google.com/group/comp....5bd716d4fb08c7
I've also put a basic outline of the table design that I've come up
with in an excel spreadsheet, and it's available here:
http://www.j1m.net/lj/db/draft_of_table_design.xls , but if you won't
want to download the file, here is what I've come up with so far:
(**the beginning stuff is pretty basic . . . **)
tbl_staff_info
auto_number
hrid (the employee's ID number)
f_name
l_name
dept_id
jobtitle_id
location_id
suprvsr_name (I know that it would be better to just have a field like
supervisor_hrid link to a supervisor table, but it is more trouble than
it is worth in this case.)
hire_date
term_date
last_perf_rating_id
tbl_depts
dept_id
dept_name
tbl_job_title
job_title_id
job_title
tbl_locations
location_id
location_name
tbl_perf_ratings
last_perf_rating_id
perf_rating
(**end of the really basic stuff**)
tbl_questions
question_id
question_num (to allow sorting of the order of the questions)
question_type (numeric code to identify response data type - e.g.
yes/no, text)
question_text
tbl_answers
answer_id
hrid
question_id
answer (note: yes/no data type)
tbl_other_answers
other_answer_id
hrid
question_id
other_answer (note: text data type - 255 char limit)
tbl_boolean_answers (note: not sure about this one)
boolean_answer_id
hrid
question_id
boolean_answer (e.g. excellent, good, fair, poor) (should I allow 4
yes/no answers here, or just have a drop down menu? which would be
easier to report on?)
Do I have the answer tables set up logically? I know that this might
not be the easiest question on this newsgroup to answer, but I really
appreciate your help. Thanks so much!!
I'm trying to design my first somewhat complex database, and can use
some help. Basically, it's an employee Exit Interview database, and
was wondering if someone could review the table design that I'm trying
to put together.
The gist of my difficulties is that I have three different types of
answers (yes/no, text, and likert-type scales . . . eg excellent, good,
fair, poor . . . i know that these are essentially multiple yes/no data
type reponses . . . ), I'm not sure if I'm handling the answer types
and table designs correctly. Does anyone have any suggestions? I have
looked at the "At Your Survey" database that was created by an Access
MVP, but want to actually create my own application as a learning
process.
As a reference, I've reviewed a number of postings about survey
database designs, including the threads and info found in the following
links:
http://www.dbforums.com/showthread.php?t=1081046&highlight=survey+database
http://www.databaseanswers.org/data_models/index.htm
http://groups.google.com/group/comp....5bd716d4fb08c7
I've also put a basic outline of the table design that I've come up
with in an excel spreadsheet, and it's available here:
http://www.j1m.net/lj/db/draft_of_table_design.xls , but if you won't
want to download the file, here is what I've come up with so far:
(**the beginning stuff is pretty basic . . . **)
tbl_staff_info
auto_number
hrid (the employee's ID number)
f_name
l_name
dept_id
jobtitle_id
location_id
suprvsr_name (I know that it would be better to just have a field like
supervisor_hrid link to a supervisor table, but it is more trouble than
it is worth in this case.)
hire_date
term_date
last_perf_rating_id
tbl_depts
dept_id
dept_name
tbl_job_title
job_title_id
job_title
tbl_locations
location_id
location_name
tbl_perf_ratings
last_perf_rating_id
perf_rating
(**end of the really basic stuff**)
tbl_questions
question_id
question_num (to allow sorting of the order of the questions)
question_type (numeric code to identify response data type - e.g.
yes/no, text)
question_text
tbl_answers
answer_id
hrid
question_id
answer (note: yes/no data type)
tbl_other_answers
other_answer_id
hrid
question_id
other_answer (note: text data type - 255 char limit)
tbl_boolean_answers (note: not sure about this one)
boolean_answer_id
hrid
question_id
boolean_answer (e.g. excellent, good, fair, poor) (should I allow 4
yes/no answers here, or just have a drop down menu? which would be
easier to report on?)
Do I have the answer tables set up logically? I know that this might
not be the easiest question on this newsgroup to answer, but I really
appreciate your help. Thanks so much!!