Storing a great many fields in a database

[Peter Haworth]

I don't know if you are referring to SQLite or other SQL implementations,
but there is no varchar in sqlite.  You can define a column as varchar but
sqlite will give it a type of TEXT internally.

SQLite does have some advantages over other dbs in this area.  Since it
effectively ignores any length specification, e.g. VARCHAR(100), it only
stores the number of characters you give it during an INSERT or UPDATE
operation.  On the other hand VARCHAR(100) on most other SQL dbs will
allocate enough disk space to store 100 characters even if you only put 1
character in the column.  The "100" is a constraint that prevents more than
100 chars from being put into the column but doesn't affect the storage
space.

If you're only ever going to access this db with LC, I'd probably use
"true" and "false" as your boolean values.  If you plan on using other
tools to access it, go with 0 and 1.

As for integer arithmetic being "slow", I doubt you'd notice any difference
whatsoever unless you expect to process hundreds of thousands of rows in
one operation.  As you noted, using REAL for currency arithmetic will get
you into rounding issues but sounds like that's not a problem.  One
approach I've seen for currency values is to store them as integers
including the decimal places, e.g $100.00 would be stored as 10000, do the
arithemtic in that form, then insert the decimal point for display
purposes.


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



On Mon, Jul 16, 2012 at 8:31 AM, Dr. Hawkins <dochawk at gmail.com> wrote:

> OK, blobs are overkill, and then some.  It looks like varchar() is the
> way to go.
>
> It looks like a main table for the financials, with the booleans and
> integers stuffed there as well and then a varchar table for the
> strings. (storing true as 1 and false as 0?)
>
> I note documentation that refers to numeric/decimal as "very slow" as
> compared to floats (i'd need doubles, anyway).  Just how much is "very
> slow"; is it even relevant on a remote database?  An occasional penny
> rounding error isn't really critical for this type of work.
>