Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Join Date
    Jan 2004
    Location
    Findlay, OH
    Posts
    8

    Unhappy Unanswered: 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!

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    20,500 fields ...

    Man... i would really go down to sleep filling such a big form
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  4. #4
    Join Date
    Jan 2004
    Location
    Findlay, OH
    Posts
    8
    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.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You seriously need to rethink your design.

    Seriously.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Jan 2004
    Location
    Findlay, OH
    Posts
    8
    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.

  8. #8
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Somebody's actually gonna fill up the fields ... wow !!!
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Jan 2004
    Location
    Findlay, OH
    Posts
    8
    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.

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Jan 2004
    Location
    Findlay, OH
    Posts
    8

    Wink

    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

  14. #14
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  15. #15
    Join Date
    Jan 2004
    Location
    Findlay, OH
    Posts
    8
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •