Best design for millions and millions of records 1

[redlam]

hi there.
i am converting a large table from visfox to sql2000. this table stores values from a complicated data entry form. in visfox, it has a memo field that stores an array that represents all of the form's required entries and the user's value for these fields. the visfox sample data looks like this:

form org type year state values
52 1 C 2000 CA Memo
52 1 S 2000 CA Memo
52 1 A 2000 CA Memo

the 'Values' field is the array of all the user's entries on this form. if this were converted to a normalized table in sql2000, there would be over 100 million records. can anyone suggest another solution for this? in the past, i have seen programmers store data like this in a delimited text field - if possible, i want to avoid this option.
thank you very much for your help.

 

[redlam]

i should have mentioned that this fields on this form change frequently. it is a state mandated form and unfortunately we are at the mercy of their wimsical modifications. this is the reason for not simply using multiple hard coded columns for the form values.
sorry for leaving that part out...

 

[foxdev]

Redlam, what is max(len(values)) ?

If small enough, you could use a varchar column; if not (shudder) you can use a text column, but it increases the complexity.

If it weren't for the structure changing constantly, I'd recommend a normalized table; I'm not sure it still isn't the best solution, but you've obviously already thought about that. Robert Bradley

http://www.lowcostprints.com

http://www.foxdev.com

 

[redlam]

hi foxdev.
thanks for jumping in...
this table is really ugly. its actually storing not just one form, but many different forms for each state - i was trying not to scare everyone off with the first posting. here's the dirty deal -- each of these forms could have up to 124 required fields and each of our clients could be required to submit 6 different forms for each state. the state has the option of changing, removing and adding required fields and forms whenever they feel like it and we are committed to keeping up with their changes (grandiose delusion on our part). any ideas for me? (other than running fast in the other direction) X-)
thanks again.

 

[redlam]

addendum to above:
each of our clients could be submitting forms for up to 100 companies. in summary -
one client could be required to submit 6 forms for each state for each company that they are responsible for...

 

[foxdev]

Hmmm...I'm thinking of pivoting the table(s) - rather than having multiple (124, say) columns, have multiple rows.

Say you have a table FL123Resp for Florida's form 123 responses, plus a parent record FL123Main for the submission:

FL123MAIN
SubmissionID
FormID (FK to Form question template table)
DateSubmitted
CompanyID
(etc)

FL123RESP
SubmissionID
ItemID (optional FK to Question master table)
Response

This way, as new questions are added (or removed), you are simply adding rows, not columns, to the Response table. You simply change the Form Question Template table, and each form submitted will cause the currently active questions to be created as rows in the response table.

Robert Bradley

http://www.lowcostprints.com

http://www.foxdev.com

 

[redlam]

thanks - i think your solution is a good start. breaking out the tables by state is a good idea because we're not likely to acquire many new states or get rid of any old ones with much frequency at all. i'm trying to avoid building new tables every time a state adds a form tho so i may attempt to keep the tables a bit more generic. for example:

Forms - master forms table
Form_Ques - questions table with fk to master form table
FL_Resp - responses to all florida form questions with fk to master questions table

thank you very much for your idea - i'll credit you when we make our first million.

 

[foxdev]

I feel your pain when working with state forms. I used to have to write programs to support UCR (Uniform Crime Reporting). How every state can have a different format for a Federal program illustrates that nothing is simple in government. Robert Bradley

http://www.lowcostprints.com

http://www.foxdev.com