design advice needed
From: Ben Wallach <wallach.ben_at_verizon.net>
Date: 14 Apr 2002 01:28:20 -0700
Message-ID: <466e8e7b.0204140028.732870b_at_posting.google.com>
I am in the process of designing a schema for a web site that accepts form entries. There are only a handful of forms, but a couple of them have close to 100 questions. I have two approaches I am considering, but I am not sure which is the better of the two for my situation. Much of the data is true/false (0 or 1 integer).
create a table for each form. These tables will contain many columns, but only one row per record. Efficient access (1-read and 1-write only), but harder maintenance as the table itself has to be modified when questions are added/deleted/changed...
create "form", "question", "answer"... tables with foriegn keys between them. All data for a given form entry is stored in the
"answer" table with a question_id-answer_id relationship. So we have a
"tall skinny" approach here. Multiple rows in the answer table hold
the data for a single form entry(close to 100 for a couple of the forms). With this approach it is easier to add/delete/modify questions, but it is less efficient as multiple reads and inserts are required to get a single form record. So if I was adding a newly filled out form by a user to the DB, I might have to perform up to 100 insert statements to get all the question/answer combos into the answer table. More sql gymnastics are necessary as well to get at the data and insert the data.
Date: 14 Apr 2002 01:28:20 -0700
Message-ID: <466e8e7b.0204140028.732870b_at_posting.google.com>
I am in the process of designing a schema for a web site that accepts form entries. There are only a handful of forms, but a couple of them have close to 100 questions. I have two approaches I am considering, but I am not sure which is the better of the two for my situation. Much of the data is true/false (0 or 1 integer).
approach 1:
create a table for each form. These tables will contain many columns, but only one row per record. Efficient access (1-read and 1-write only), but harder maintenance as the table itself has to be modified when questions are added/deleted/changed...
approach 2:
create "form", "question", "answer"... tables with foriegn keys between them. All data for a given form entry is stored in the
"answer" table with a question_id-answer_id relationship. So we have a
"tall skinny" approach here. Multiple rows in the answer table hold
the data for a single form entry(close to 100 for a couple of the forms). With this approach it is easier to add/delete/modify questions, but it is less efficient as multiple reads and inserts are required to get a single form record. So if I was adding a newly filled out form by a user to the DB, I might have to perform up to 100 insert statements to get all the question/answer combos into the answer table. More sql gymnastics are necessary as well to get at the data and insert the data.
Another issue here is that I am using MySql which does not support transactions as far as I know. I am leaning towards approach-1 right now.
Any thoughts/advice/feedback are most appreciated !
Thanks in advance
Ben Wallach
wallach.ben_at_verizon.net
Received on Sun Apr 14 2002 - 10:28:20 CEST