Re: design advice needed

Ben Wallach wrote:
>
> 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

Go with approach 2. When it comes time to extract the data for statistical analysis you will be glad you did ;-)

I see that you are concerned with efficiency with this approach. Things won't be as bad as you think. I say this because whatever OS you are using will can do multi-block reads which will dramatically cut your physical I/O operations.

A question. Why is the lack of transactions in MySQL an issue? Unless you are doing something really off-the-wall all of the information for a form should be entered at one time and never modified once it is in the system. If you use the commit/rollback facilities of MySQL (and your operating system supports them) no one can see the data for a form until you issue a commit in your data entry program. As a result your data will always be consistent.

Jerry Received on Mon Apr 15 2002 - 06:02:16 CEST