Storing a great many fields in a database

[Dr. Hawkins]

On Saturday, July 14, 2012, Peter Haworth wrote:
>
>
> Are you saying that you want to store the default, override, and actual
> values in the table row of the database? If so, that deosn't feel right to
> me.  I'd split that into a default table, an override table, and actual
> value table, each connected by a foreign key to the primary key of the
> table that idntifies the debtor, (name/address, SSN, etc).
>

I'm actually outing each debtor in a separate database.  There is really no
cross-over from case to case, and one getting corrupted (especially by a
coding error!) doesn't kill the other client "files" .  I'm also not
comfortable from mixing data for different attorneys in the same database.

Also, as far as performance, there will be thousands (tens of thousands?)
of debtors on the server, each with a few hundred records.  won't
performance be significantly better if they're in their own databases?


 if I'm generally working with the triplet of fields as a set, what would
be the value of putting them in separate tables?  I always need to know if
they exist, and I can pull all three in a single query.

Another approach might be to try to group the fields into categories that
> logically belomng together somehow and have a separate table for each
> category, once again linked by foreign key to the m ain debtor table.
>  Don't know if that's possible.
>

Logic and US bankruptcy law face little in common :)


More seriously, to the extent that they "group" with any rhyme or reason,
it's currency values with the occasional Boolean, integer, and string.

THe more I think about it, the more I think it will be an issue of storing
strings in their own table;the others can transparently cohabit in a
currency table.


If you're saying that datatypes vary by row in the same table, that's a
> definite no no in any database design, more so in anything other than
> SQLite since they all have strict typing rules and you will get errors
> returned if you try to insert a column with a datatype that is different
> than defined in the schema.  You'll need to design your tables so that any
> given column in a table will always have the same datatype.  If I
> understand your email correctly, sounds like there should at least be
> separate asset and debt table
>

 they're in a separate table, yes.  It gets multiple rows, one for each
asset/debt.

Thanks



-- 
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