Storing a great many fields in a database

[Dr. Hawkins]

On Mon, Jul 16, 2012 at 9:50 AM, Peter Haworth <pete at lcsql.com> wrote:
> 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.


I'm using sqlite at the moment, but need to move to mysql or
postgresql to put htis on servers.

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

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


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

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