Storing a great many fields in a database

[Dr. Hawkins]

Ack, iPad pre launched message . . .

On Sunday, July 15, 2012, Dr. Hawkins wrote:

>
>
> On Saturday, July 14, 2012, Mark Wieder wrote:
>>
>> Saturday, July 14, 2012, 3:16:25 PM, you wrote:
>>
>> > Or split it into two tables, and let my get/set functions figure out
>> > which to use, one for currency values, and the other for everything
>> > else?
>
>


> OMG. You have that many fields in *one* table? I think you need a
>
> serious database redesign. Or "design", since it sounds like it was
>> never really designed in the first place. Come up with a database
>> schema, split the data into component tables, and this will all become
>> much easier.
>>
>
It's really a situation of 400-500 variables to report, some of which are
dependent upon and calculated by others (and once calculated, need to be
kept available for further calculation, and for which the calculated value
may be overridden).

 Name address, phone,,last 4 of SS, prior filing, total wage income for six
prior months, total business income 6 mo, bus expenses 6 mo, bus net 6 mo,
real estate gross 6 months. . . . .  Employer, Employer address, monthly
health insurance from wages, monthly health insurance no wage, total health
ins. Monthly, ... Total insurance monthly . . .

there are that many "single use" data  (outside of the assets and debts) in
a bankruptcy petition.

Unfortunately, there is no general pattern, although for a significant
portion, they are repeated for the codebtor (wife) in a joint filing. a
strong majority are currency values, but the strings I need vary from 1
char to about 200 (for an address)


with SQLite, I have a row of keyword, value, override, and default.  I've
recently added a fifth column with values used to calculate the fpdefault
in certain cases, but I'd like to dump it. (well, store separately).
 this is going to quickly clobber space, though, if every number and
Boolean needs 3 200 char fields in storage (roughly a quarter megabyte for
the resultant file)

I'm toying with a number table and a string table; it's straightforward
enough for he put/get routines to use the custom property on the fields to
direct traffic.  Integers (always small), generally low single digits)
could  be store there trivially; that's just a display of the decimal
issue.  Booleans could be saved any number of ways (are non-zero vales
"true" for an if?).

That still leaves me with the ugly strings question, though.

the actual value is actually redundant; if there is an override, it's the
valu.  If not, the default, if any, is used. Most of the strings don't have
defaults, anyway; i could store the default s as separate rows.  I could
also put the  extra blocks for some of the defaults mentioned above in here.

it how to control the size of this table?  Or do I simply accept that I
allocate 20 chars even for one byte strings?




-- 
The Hawkins Law Firm
Richard E. Hawkins, Esq.
(702) 508-8462
HawkinsLawFirm at gmail.com
3025 S. Maryland Parkway
Suite A
Las Vegas, NV  89109