I think I get what you are trying to do and it sounds good (though I am still a beginner). The only thing I can think that might cause a problem is since you have more then a couple of tables you MAY have to write joins for all of them . Only you would know if you can do that and/or be comfortable doing it.
To get a stronger idea though of your design I would need to see either a logical ER diagram or all of the creates (I am very visual so I like pictures). *shrugs* whether or not you want to pass those along is up to you. Respectfully, Ligaya Turmelle ""Robert A. Rosenberg"" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > I am converting a form that was originally designed to be Email > Submitted into a Submit-to-PHP-Page Form (which will then insert the > data into a MySQL Database). > > 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]
