Storing 15 forms of information - should I use 15 tables?

Question

Storing 15 forms of information - should I use 15 tables?

Brad Burns-435579

Valued Member

Points: 58
More actions
May 1, 2007 at 10:08 pm

#116580

Hi everyone,
In an area of a website that I'm building I have 15 different forms that a user can enter information into. With all of this information, a single document is generated on the fly, with some or all of the information entered into these forms.
Now, with this, I am wondering how to best store and retrieve this form information. If I created a separate database table to store each form, I would have to have a select (or multiple select) statement that pulls data from 15 tables at a time.
Is this the right way to go? Should I use one table that stores the form information as XML in a single field?
I'm trying to design the database for best performance as well as ease of maintainability from a programmer's perspective.
All advice is appreciated.
~Brad

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

jezemine SSCrazy Eights Points: 8059 More actions · Answer 1

can you describe what the data represents? is it an order form, a job application, or what?

Does each form represent the data for a distinct object? if so different tables for each might make sense. From what little information you have provided it's hard to say.

I am against the idea of storing everything as a single xml column, that's for sure.

---------------------------------------
elsasoft.org

Brad Burns-435579 Valued Member Points: 58 More actions · Answer 2

Each form has different information than the others, therefore there are no duplicate fields. Each form is a group of related fields, and the information from each form is specific to a section of a larger "document" that gets generated. There is nothing special about the form fields themselves, they are simple nVarChar(#) or integer type fields. Each form contains a total of between 10 to 30 fields.

jezemine SSCrazy Eights Points: 8059 More actions · Answer 3

I was looking for more specifics than that. Does each form describe a zoo animal? a flying machine? a psychiatric disorder?

Without more detail about what each form represents, I can't make an informed recommendation. My uninformed recommendation is to use one table for each form.

---------------------------------------
elsasoft.org

Brad Burns-435579 Valued Member Points: 58 More actions · Answer 4

Thanks, I'll probably end up using one table for each form.

Just a note, I don't see why more specifics would be needed, other than what I've given so far.

I've given:

- The number of forms;

- The range of fields on each form;

- The field types (nVarChar(n) and integer);

- The fact that there are no duplicate fields across forms;

- The end result of what this is being used for (generating a document);

I don't see what difference it makes if each form contains information for a zoo animal, a flying machine, or a psychiatric disorder.

I do appreciate the replies though.

~Brad

jezemine SSCrazy Eights Points: 8059 More actions · Answer 5

you don't see why more specifics are needed?

Imagine you had a contract to build a database for this system, and your spec from the client amounted to this thread. Would that be enough for you to design the db?

---------------------------------------
elsasoft.org

David Wall-401510 Mr or Mrs. 500 Points: 545 More actions · Answer 6

As much as I hate to say it - but this is were you need to do some systems analysis. Even if it's just running some DFD's and doing some normalisation... but if you're after performance and ease of use - you need to get the database somewhere close to a standard methodology.

This will also allow you to identify where data is supposed to fit together...

Dave

Brad Burns-435579 Valued Member Points: 58 More actions · Answer 7

Actually, we already have the DFD and schema built. We know how each block (or form) or information relates to each other, the user and the generated document. We also have our analysis documentation and business rules defined.

Just wanted to know if there was a more viable alternative.

Thanks for the posts.