I expect no problems in actually scanning the submission to extract the data but I have some questions on the design of the Tables that I will need to define to store the data.
I have done some research and have come up with a tentative design that I want to post here for critiquing. Here is what I have come up with.
Each form will get assigned a sequence number (SubmitterID) that will identify the form submission in all the tables (thus linking them).
There are a number of TextAreas where the user is requested to enter free form replies to questions. I am thinking that these should go into a separate table as Text fields of the correct size with SubmitterID as the Primary Key. That keeps the data away from the main table and thus only accessed when needed/requested as well as not bloating the size of the main table or slowing its retrieval/processing.
There are a number of blocks of CheckBoxes on the form. While I could, in theory, use a SET column type to store them, I get the impression that a better way is to create a Many-to-One table for each block with the total contents of each row being the CheckBoxID and SubmitterID (in that order) as the Primary Key. A Index for SubmitterID would also be defined. This way I can do a WHERE on either Column and get Index Usage as opposed to needing to do a row-by-row lookup. I have the impression that doing a WHERE over a SET Column (especially when I'm looking for more than one value) is not a good or efficient idea. The CheckBoxID would map to a 3rd table to get the actual CheckBoxName.
There are also two Select Tables (one for US States and one for Countries). While the States are passing the USPS 2-Letter State Codes, the Countries are passing the full Country Name as their OPTION VALUE=. I want to make a State Table and a Country Table using respectively the 2-Letter Code and a sequential reference number (which I will revise the Country OPTION tags to use as their VALUE) as the Primary Key with the State or Country Name as the other column.
To create the reference key and populate the OPTION pages with it I plan to take the current HTML for these tags and read them into a Text Editor where I will then alter them into MySQL Insert Commands to populate the Table. Then using a one-shot Quick&Dirty PHP page, I will read the table and recreate the Option Statements which would the be Cut&Pasted into the original HTML code replacing the old versions of the tags. The states go though the same "Turn into INSERT Commands" but there is no need for post processing or HTML Tag replacement.
Of course all the Table Pointers would be defined as Foreign Keys to insure Referential Integrity.
Am I making any mistakes in my design or am I on the correct track. I welcome any critique of my design or advice on how to improve it.
Thank you.
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
