Storing a great many fields in a database

[Peter Haworth]

It's a little hard to decipher exactly waht your're trying to achieve here
but here's a few thoughts..

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

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.

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


Pete
lcSQL Software <http://www.lcsql.com>



On Sat, Jul 14, 2012 at 3:16 PM, Dr. Hawkins <dochawk at gmail.com> wrote:

> Currently I'm using the included SQLite while I'm figuring the whole
> program out, but to stay ahead of the competition, I need to be able
> to store in the cloud (particularly, for attorneys to use "virtual
> assistants" in other parts of the countries, and to access files by
> iPad in court).
>
> Anyway, SQLite cheerfully ignores the data type entirely.
>
> I assume that I'll stay compatible with both mySQL and postgreSQL, but
> that's where the issue is.
>
> I have a great many fields that provide the description of the debtor
> (it's a bankruptcy program).  At the moment, there are 276 of them
> (and will probably be about 400 when complete).  Most of these are
> money values (decimal(12,2)).  A handful are boolean, and the rest
> text ranging from 1 to 200 characters.
>
> I need to be able to access them by name, so currently there is a
> keyword for each, and three different values for each one(a default,
> an override, and the actual value).
>
> I've been happily assigning data types in a custom property field, and
> even fixed them up today.  And then it occurred to me that I don't get
> to specify a different data type by row . . . (I have debt and asset
> information with a great many per debt, so that goes in a different
> table).
>
> Given that the "norm" will probably be a remote rather than local
> database server, what is the best way for me to structure the table?
> My 200 character upper limit seems to suggest that blobs would be
> overkill, but allocating three 200 character strings per entry seems
> like serious overkill--or is it?
>
> I could have 400 columns, I suppose, with three rows (for each of
> those values)--but isn't this going to slow down the server?
>
> 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?
>
> Or will the minimum row size mean that at 200 characters each, it just
> doesn't matter anyway?
>
>
> --
> 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
>
> _______________________________________________
> use-livecode mailing list
> use-livecode at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>