Re: Large number of rows in table
Posted by: Bill Karwin
Date: June 28, 2006 02:45PM

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.

Options: ReplyQuote


Subject
Written By
Posted
Re: Large number of rows in table
June 28, 2006 02:45PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.