Posted on Mar 16, 2017
What would be a good solution for transferring a feedback/contact form into a database entry system for better reporting?
2.56K
2
4
0
0
0
Right now I'm recreating a Contact Form 7 Feedback form as a mySQL database table.
Any thoughts or is an Entity Relationship Diagram needed also?
Any thoughts or is an Entity Relationship Diagram needed also?
Edited >1 y ago
Posted >1 y ago
Responses: 1
1LT William Clardy, I am looking for suggestions on structuring the table AND handling responses. Here's the contact form. I can get show you what I have for a Create Table in a few hours.
**Feedback form removed**
**Feedback form removed**
(0)
(0)
1LT William Clardy
How's this for a rough, mostly normalized start?
CREATE TABLE ff.instructors(
instructor_id int NOT NULL UNIQUE ,
name_first varchar( 128 ) NOT NULL DEFAULT '' ,
name_last varchar( 128 ) NOT NULL DEFAULT '' ,
active bit NOT NULL DEFAULT 1 )
CREATE TABLE ff.event_locations(
loc_id int NOT NULL UNIQUE ,
loc_description varchar( 128 ) NOT NULL )
INSERT INTO ff.event_locations
VALUES
( 0 , 'Campus' ) ,
( 1 , 'Online' )
CREATE TABLE ff.events(
event_id int NOT NULL UNIQUE ,
event_description varchar( 120 ) NOT NULL ,
instructor_id int NOT NULL ,
loc_id int NOT NULL DEFAULT 0 ,
event_date date NOT NULL ,
FOREIGN KEY ( instructor_id ) REFERENCES ff.instructors ( instructor_id ) ,
FOREIGN KEY ( loc_id ) REFERENCES ff.event_locations ( loc_id ) )
CREATE TABLE ff.course_categories(
cc_id int NOT NULL UNIQUE ,
cc_description varchar( 128 ) NOT NULL )
INSERT INTO ff.course_categories
VALUES
( 1 , 'Blackboard' ) ,
( 2 , 'Groups' ) ,
( 3 , 'Assessment' ) ,
( 4 , 'Student Engagement Using Tech' ) ,
( 5 , 'Rubris' ) ,
( 6 , 'Course Design' ) ,
( 7 , 'Different Teaching Methodologies' ) ,
( 8 , 'Critical Thinking' ) ,
( 9 , 'Reading Strategies' ) ,
( 10 , 'Classroom Management' ) ,
( 11 , 'Managing Cultural Differences in the classroom' )
CREATE TABLE ff.faculty_feedback(
submission_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ,
event_id int NOT NULL UNIQUE ,
instructor_id int NOT NULL ,
content_value int NOT NULL DEFAULT 1 ,
more_involved bit NOT NULL DEFAULT 0 , -- 1 for interested, 0 for all else
name_last varchar( 128 ) NOT NULL DEFAULT '' ,
name_first varchar( 128 ) NOT NULL DEFAULT '' ,
email varchar( 192 ) NOT NULL DEFAULT '' ,
voice varchar( 10 ) NOT NULL DEFAULT ' [login to see] ' ,
preferred_time int NOT NULL DEFAULT 0 ,
-- 0 for no pref , 1 for AM , 2 for noon , 3 for afternoon , 4 for evening
prefered_method int NOT NULL DEFAULT 0 ,
-- 0 for no pref , 1 email , 2 for voice
other_coursses varchar( 192 ) NOT NULL DEFAULT '' ,
-- comma-delimited list
comments varchar( 2008 ) NOT NULL DEFAULT '' ,
FOREIGN KEY ( instructor_id ) REFERENCES ff.instructors ( instructor_id ) ,
FOREIGN KEY ( event_id , instructor_id ) REFERENCES ff.events ( event_id , instructor_id ) )
CREATE TABLE ff.instructors(
instructor_id int NOT NULL UNIQUE ,
name_first varchar( 128 ) NOT NULL DEFAULT '' ,
name_last varchar( 128 ) NOT NULL DEFAULT '' ,
active bit NOT NULL DEFAULT 1 )
CREATE TABLE ff.event_locations(
loc_id int NOT NULL UNIQUE ,
loc_description varchar( 128 ) NOT NULL )
INSERT INTO ff.event_locations
VALUES
( 0 , 'Campus' ) ,
( 1 , 'Online' )
CREATE TABLE ff.events(
event_id int NOT NULL UNIQUE ,
event_description varchar( 120 ) NOT NULL ,
instructor_id int NOT NULL ,
loc_id int NOT NULL DEFAULT 0 ,
event_date date NOT NULL ,
FOREIGN KEY ( instructor_id ) REFERENCES ff.instructors ( instructor_id ) ,
FOREIGN KEY ( loc_id ) REFERENCES ff.event_locations ( loc_id ) )
CREATE TABLE ff.course_categories(
cc_id int NOT NULL UNIQUE ,
cc_description varchar( 128 ) NOT NULL )
INSERT INTO ff.course_categories
VALUES
( 1 , 'Blackboard' ) ,
( 2 , 'Groups' ) ,
( 3 , 'Assessment' ) ,
( 4 , 'Student Engagement Using Tech' ) ,
( 5 , 'Rubris' ) ,
( 6 , 'Course Design' ) ,
( 7 , 'Different Teaching Methodologies' ) ,
( 8 , 'Critical Thinking' ) ,
( 9 , 'Reading Strategies' ) ,
( 10 , 'Classroom Management' ) ,
( 11 , 'Managing Cultural Differences in the classroom' )
CREATE TABLE ff.faculty_feedback(
submission_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ,
event_id int NOT NULL UNIQUE ,
instructor_id int NOT NULL ,
content_value int NOT NULL DEFAULT 1 ,
more_involved bit NOT NULL DEFAULT 0 , -- 1 for interested, 0 for all else
name_last varchar( 128 ) NOT NULL DEFAULT '' ,
name_first varchar( 128 ) NOT NULL DEFAULT '' ,
email varchar( 192 ) NOT NULL DEFAULT '' ,
voice varchar( 10 ) NOT NULL DEFAULT ' [login to see] ' ,
preferred_time int NOT NULL DEFAULT 0 ,
-- 0 for no pref , 1 for AM , 2 for noon , 3 for afternoon , 4 for evening
prefered_method int NOT NULL DEFAULT 0 ,
-- 0 for no pref , 1 email , 2 for voice
other_coursses varchar( 192 ) NOT NULL DEFAULT '' ,
-- comma-delimited list
comments varchar( 2008 ) NOT NULL DEFAULT '' ,
FOREIGN KEY ( instructor_id ) REFERENCES ff.instructors ( instructor_id ) ,
FOREIGN KEY ( event_id , instructor_id ) REFERENCES ff.events ( event_id , instructor_id ) )
(1)
(0)
SGT (Join to see)
1LT William Clardy - Sir, this looks awesome. I'm going to plug this into a mySQL studio and see how this plays out. I'll get back to you.
All I had was the following:
CREATE TABLE IF NOT EXISTS `fkms_feedback` (
`user_name` varchar(16) DEFAULT NULL,
`user_firstname` varchar(25) DEFAULT NULL,
`user_middlename` varchar(25) DEFAULT NULL,
`user_lastname` varchar(25) DEFAULT NULL,
`user_instructor` /*Jeff Fouts or Peggy Takach*/
`user_instruction_online` bit(1) NOT NULL DEFAULT b'0',
`user_request_contact` bit(1) NOT NULL DEFAULT b'0',
`user_email` varchar(50) DEFAULT NULL,
`user_phone` int(15) DEFAULT NULL,
`user_request_training_assessment` bit(1) NOT NULL DEFAULT b'0',
`user_request_training_blackboard` bit(1) NOT NULL DEFAULT b'0',
`user_request_training_classroom_management` bit(1) NOT NULL DEFAULT b'0',
`user_request_training_course_design` bit(1) NOT NULL DEFAULT b'0',
`user_request_training_cultural_differences` bit(1) NOT NULL DEFAULT b'0',
`user_request_training_critical_thinking` bit(1) NOT NULL DEFAULT b'0',
`user_request_training_groups` bit(1) NOT NULL DEFAULT b'0',
`user_request_training_reading_strategies` bit(1) NOT NULL DEFAULT b'0',
`user_request_training_rubris` bit(1) NOT NULL DEFAULT b'0',
`user_request_training_student_resources` bit(1) NOT NULL DEFAULT b'0',
`user_request_training_student_tech_engagement` bit(1) NOT NULL DEFAULT b'0',
`user_request_training_teaching_methodologies` bit(1) NOT NULL DEFAULT b'0',
`user_request_involvement` bit(1) NOT NULL DEFAULT b'0',
`user_class_value` int(1) /*1-5*/ NOT NULL,
`user_create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
All I had was the following:
CREATE TABLE IF NOT EXISTS `fkms_feedback` (
`user_name` varchar(16) DEFAULT NULL,
`user_firstname` varchar(25) DEFAULT NULL,
`user_middlename` varchar(25) DEFAULT NULL,
`user_lastname` varchar(25) DEFAULT NULL,
`user_instructor` /*Jeff Fouts or Peggy Takach*/
`user_instruction_online` bit(1) NOT NULL DEFAULT b'0',
`user_request_contact` bit(1) NOT NULL DEFAULT b'0',
`user_email` varchar(50) DEFAULT NULL,
`user_phone` int(15) DEFAULT NULL,
`user_request_training_assessment` bit(1) NOT NULL DEFAULT b'0',
`user_request_training_blackboard` bit(1) NOT NULL DEFAULT b'0',
`user_request_training_classroom_management` bit(1) NOT NULL DEFAULT b'0',
`user_request_training_course_design` bit(1) NOT NULL DEFAULT b'0',
`user_request_training_cultural_differences` bit(1) NOT NULL DEFAULT b'0',
`user_request_training_critical_thinking` bit(1) NOT NULL DEFAULT b'0',
`user_request_training_groups` bit(1) NOT NULL DEFAULT b'0',
`user_request_training_reading_strategies` bit(1) NOT NULL DEFAULT b'0',
`user_request_training_rubris` bit(1) NOT NULL DEFAULT b'0',
`user_request_training_student_resources` bit(1) NOT NULL DEFAULT b'0',
`user_request_training_student_tech_engagement` bit(1) NOT NULL DEFAULT b'0',
`user_request_training_teaching_methodologies` bit(1) NOT NULL DEFAULT b'0',
`user_request_involvement` bit(1) NOT NULL DEFAULT b'0',
`user_class_value` int(1) /*1-5*/ NOT NULL,
`user_create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
(0)
(0)
1LT William Clardy
SGT (Join to see), I look forward to hearing how much of it works for your needs.
Also, here are a few potentially significant additional items/suggestions about the data structure I offered:
1) The submitter (user) contact information remains denormalized, because normalizing incoming information, especially user-entered text, is a hard and perilous path to take.
2) The event/instructor information should be treated on the form as a unified item, either by tying the field values together with some spiffy JavaScript-driven interaction or by just using a single drop-down with the information coalesced into -- otherwise you're inviting referential integrity issues when users inevitably select invalid combination (e.g., student gives feedback on Professor Plumb teaching candlesticks in the lavatory the night after he was killed in the library with the pipewrench)
3) When populating the event options from the events table, you probably want to window the event dates, so you're not getting feedback on events which are the day after tomorrow or way back in yesteryear. You may also want to add a bit field to the events table to mark events which have been cancelled.
4) If you add a bit field to the course_categories table to mark whether or not the category should be displayed, you can use that table to define and redefine the available choices on the form without having to edit the form.
Also, here are a few potentially significant additional items/suggestions about the data structure I offered:
1) The submitter (user) contact information remains denormalized, because normalizing incoming information, especially user-entered text, is a hard and perilous path to take.
2) The event/instructor information should be treated on the form as a unified item, either by tying the field values together with some spiffy JavaScript-driven interaction or by just using a single drop-down with the information coalesced into -- otherwise you're inviting referential integrity issues when users inevitably select invalid combination (e.g., student gives feedback on Professor Plumb teaching candlesticks in the lavatory the night after he was killed in the library with the pipewrench)
3) When populating the event options from the events table, you probably want to window the event dates, so you're not getting feedback on events which are the day after tomorrow or way back in yesteryear. You may also want to add a bit field to the events table to mark events which have been cancelled.
4) If you add a bit field to the course_categories table to mark whether or not the category should be displayed, you can use that table to define and redefine the available choices on the form without having to edit the form.
(1)
(0)
Read This Next