Form with 20,500 Fields

I'm running SQL Server 2000. I have an interesting form I am creating for a client which has on it literally 20,500 fields that need to be stored in the system which I then have to create reports off of for statistics and trends.

I'm not sure how I should go about storing that large amount of information in SQL Server with the limitations of the size of a table. Would it be best to create 20 some tables to store it, or is there a better fashion to store it. 90% of the fields are numbers ranging from 0 to 100.

Thanks for any suggestions you can come up with!What the hell does the form do?

Does anyone enter 20k+ fields?

If not and you populate it, who's gonna read it?

Anyway, what language are you using?

Sounds like you are thinking that the form is like 1 row...Is there anything else about the data that looks like a Key?

Is it really n sets of info tied together with the pageid?

I guess it goes back to what the form contains...

WOW

Never seen this before...|||20,500 fields ...

Man... i would really go down to sleep filling such a big form :)|||Believe it or not we're not going to populate it. We're a mysteryshopping firm and we're going to be paying an auditor to collect sales trend information and they're going to be going online and submitting this information. Typically we're using VB/ASP for coding. There is a JobID that is used for a key and is referenced throughout the DB, and a StoreID that is going to be used to link to our properties that can't really break up the large amount of data I need to collect.

The form is created already, I just don't know the best way to try and store it since it is all pertaining to one area of information, sale prices and percentages.|||Got a data modeler on staff?

You located in the Northeast?

What type of information are they going to be enetering?

You'll also need to think about data retrieval and data mining probably...

Man that's a BIG denormalized form|||You seriously need to rethink your design.

Seriously.|||NW Ohio is where we are located. They open the form with a JobID that has been assigned to them. The form opens displaying the location they were to have gone to and collected information about throughout the week.

They enter in each rack type encountered in a store (rolling racks, clearence racks, round racks) things that would hold stock (be it clothing or clocks) this is a dropdown of all the different types of racks and is being stored as a number.

Each rack then has associated sale amounts stored in either an Original Price, and sale percentage, the markdown price, and sale percentage, or just the sale percentage itself. Then the density of each rack is stored as a number (1 - 10) and a description dropdown is available containing the types of items they will encounter (given values 1-37). This data comprises about 20,000 of the rows.

The other 500 are mostly yes/no questions pertaining to the sales.|||Somebody's actually gonna fill up the fields ... wow !!!|||OK...when you say 20,000 rows...there you go...

Each row seems to be made up of only a handful of elements...those elements seem to describe a "rack"

You'll need a table for your rack

I'm laughing when I'm typing this now...wow what a rack...

anyway, you'll have other ancillary tables that hold the rack type, store id, ect...

First thing to do is list all of the unique elemenst, then categorize them in to entities...they'll became your tables...

Make sense?|||Yes and no... I was hoping to get around having to create 10 tables to store and reference the data. It seems like it would be easier to have a table with these columns:

Job ID, Rack_Type1_1, Rack_Density1_1, Rack_Items1_1, Rack_Org_Price1_1, Rack_Markdown_Price1_1, Rack_Markdown_Percentage1_1, Rack_Descriptor1_1, Rack_Type1_2, Rack_Density1_2, Rack_Items1_2, Rack_Org_Price1_2, Rack_Markdown_Price1_2, Rack_Markdown_Percentage1_2, Rack_Descriptor1_2

and so on... until I got to Rack_Type5_500, Rack_Density5_500, Rack_Items5_500, Rack_Org_Price5_500, Rack_Markdown_Price5_500, Rack_Markdown_Percentage5_500, Rack_Descriptor5_500

assuming 5 possible stores in the mall selected out of 12, and up to 500 different sales for the racks in each store.

Even if I break it down into 5 tables holding individual store data, it's still too many columns. That would still be 4,000 columns.|||You know EXECL right?

Your'e thinking Horizontally...you need to think vertically...

CREATE TABLE [WhatARack!] (
StoreId int
, RackId int
, Rack_Price money
, Rack_Density int
. Rack_WhateverPropertyYouNeedToCapture varchar(10)
)

Where you say _1 that would be an INSERT and 1 row would go in..._500 would be an insert and that would be row 500

Is there a way to id the racks?

If not, maybe you can use IDENTITY...but I usually like to define natural keys (things that make a unique description about things)

Make sense?|||Originally posted by bornweb
NW Ohio is where we are located.

You blind dude...go hope in the car and help this guy/gal out...|||I think I know what you mean. Just seems kind of strange.

Guess i'm used to the normal one page forms they throw at me here where I create one small client_name table with 75 fields or so that I link to my Invoices, Clients, Contacts, Properties, Auditors table for reporting.

Now to have to create a few tables to replace the normal client_name table and having to link those into the other tables I listed above to store and view data, well that just seems evil!

But that's cool. Better than the other suggestion I got from a programmer here. ("Oh just make an array.")

Thanks again,
Nickolas Smith|||Originally posted by bornweb
I think I know what you mean. Just seems kind of strange.

Guess i'm used to the normal one page forms they throw at me here where I create one small client_name table with 75 fields or so that I link to my Invoices, Clients, Contacts, Properties, Auditors table for reporting.

Now to have to create a few tables to replace the normal client_name table and having to link those into the other tables I listed above to store and view data, well that just seems evil!

But that's cool. Better than the other suggestion I got from a programmer here. ("Oh just make an array.")

Thanks again,
Nickolas Smith

Dude,

What language is the fron end written in?

Are you using recordsets?|||One giant web application wrote in VB and Java. Using ASP and ADO.

If you're referring to just this one form it's basically an ASP page submitting to itself where i'm going to use VBS/ADO to insert the objects.|||All I can say, Damn! I'm glad I'll never have to deal with this database...

Model the thing like Brett has suggested otherwise it's going to hell in a hand cart real fast...|||The database isn't that bad and is split-up into many tables, the database itself has presently 13,423 tables in it storing data, and data in one table is rarely repeated in another.

Just never had an instance like this where I had so many fields relating to the same thing that wasn't easily decernable into seperate tables.|||Well you should definately split things so that rack is a seperate table and location...

not sure what else without re-reading...|||No offense to bornweb,
But I'm betting no one will use the form!. 20K fields????
Heck even a hundred is unimaginable.......

Again..just voicing out.....don't bother me...|||:) You'd be surprised what people with no other job than mysteryshopping will do for money, especially when they're paid by hour (including the time it takes to fill out the online form).

And I should note that not all 20k fields are required. It is possible that their would only be a hundred or so. But there is the possibility that much much more data can be collected.|||They may use the form, but experience shows that the more difficult, arduous, and confusing the data entry process is, the lower the quality of data you receive.

A from with 20,000+ fields? A database with 13,000+ tables? I went to your website (it looks cool) and got the impression that this database will be accessed not only by your mystery shoppers, but also by your customers for both canned and ad-hoc reporting. Problems in your database design are definitely going to affect both the performance and functional limitations of this client-facing database.

If this database is as critical to your business as it appears, I strongly encourage you to seek the assistance of an experience database designer before interface development locks you into a schema.|||Guys/Gals,

It's NOT 20K Columns...it's ROWS...

Just curious, how long have you been a developer...(and I'm really trying hard not to sound like an ass...I know, I know, somethings are really hard for me...)

Been a LONG time from doing front end work, but really, You should have 1 row, ...they do entry, bam, commit it....

And back to the part about retrieval...how can the edit the data when the f-ck up?

And you know they're gonna...|||REally. It sounds like someone needs to learn how to design dynamic forms. Either what Bret suggested, or, something like...

Click Add button adds an editable row with Save and Cancel buttons.

After save, existing rows are displayed with Edit button on each for correction.

Again, click Add button for new row of data.

You could even have a grid display the existing data and a more intuative data entry form layout for new/edit data.

Oh, and the "just make an array" guy should be canned. Proper object oriented design should meld well with database design.|||Originally posted by bpdWork
Oh, and the "just make an array" guy should be canned. Proper object oriented design should meld well with database design.

Actually, don't the 2 NOT blend...I don't mind the array thing (although with 20k, you should worry about memory..)

You'd just loop throu the INSERTS...

PLEASE use sprocs though...

on second thought a singleton INSERT is an INSERT and probably would see an perf boost...

Where's the web site?

Can I get a deal on a new ski jacket?

How about a Margaritta set?

Or forget the set, how about a deal on a 1.75 of to-kill-ya