Re: Large number of rows in table
Well, the first thing I'll say is that your database design is bad and the rows should be divided up. ;-)
However, it should not require many tables; you can do it with *four* smaller tables. For example, here's how I'd do it:
CREATE TABLE form_master (
form_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
form_description TEXT
) ENGINE=InnoDB;
CREATE TABLE form_field (
field_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
form_id INT NOT NULL,
field_description TEXT,
FOREIGN KEY (form_id) REFERENCES form_master(form_id)
) ENGINE=InnoDB;
CREATE TABLE form_response (
response_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
form_id INT NOT NULL,
FOREIGN KEY (form_id) REFERENCES form_master(form_id)
) ENGINE=InnoDB;
CREATE TABLE form_response_value (
field_id INT NOT NULL,
response_id INT NOT NULL,
field_value varchar(100) NOT NULL DEFAULT '',
FOREIGN KEY (field_id) REFERENCES form_field(field_id),
FOREIGN KEY (response_id) REFERENCES form_response(response_id)
) ENGINE=InnoDB;
The design above can store many forms, each with a different number of fields. You don't need to create a table every time you create a new form.
It can also store multiple responses per form, and keep them associated.
The only thing it can't do that your design can do is to ensure that for a given response to the form, that a value is provided for every field in that form.
Regards,
Bill K.