Storing a great many fields in a database

[Peter Haworth]

On Mon, Jul 16, 2012 at 12:07 PM, Dr. Hawkins <dochawk at gmail.com> wrote:

> > 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.
>
> It's hard to put "true" into a numeric value :)   (thus the coding)
>

Once again, sql terminology problem.  BOOLEAN in sqlite translates to TEXT,
not a numeric value.  But now you've said you'll be using mySQL or
postgres, the choice is yours.

>
> > 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.
>
> It wasn't integer being slow, but rather the numeric/decimal type that
> is "very slow"er than float/integer for database access (it's coded as
> two bcd digits/byte, and needs to be undone)
>

Sounds reasonable. But again, unless you are processing large numbers of
ros, it's not worth worrying about.  Ther can't be THAT many people going
bankrupt in NV can there :-)

>
>
> > 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.
>
> Actually working with the pennies, desirable as it would be, creates
> new issues--displaying in livecode fields.  I think it will be simpler
> to store in pennies, and convert to dollars while working, and back on
> save.


Uhhh, I think that's what I said, or maybe not…. In any case, formatting an
integer to display with a decimal point in LC is trivial.