A visual designer for SQLite. Allows the end user to layout an interface and add data to the database. This is made for beginners and for creating new databases from scratch. Currently for macOS and Windows.

https://github.com/emily-elizabeth/Database-Designer

This is an obligatory public service warning regarding floating point numbers,  'monetary values' and SQLite:

This post considers the intersection of 'monetary values' and SQLite.

In general, since you mentioned 'monetary values', rounding (particularly banker's rounding), and calculations should not be subject to IEEE 754 (or any other floating point standard) approximation.  There are exceptions. For example a unit price might be a floating point value, but it's result on an invoice should be exact, aka not floating point.  If it were floating point, when you apply calculations (for example sales tax) you would be magnifying the IEEE 754 approximation of the 'monetary value' (which could be an incorrect result).

The above is especially important in double entry accounting (where one tends to see 'monetary values' a bit) because each journal entry needs to balance out, meaning the exact total amounts from one set of accounts into the exact same total amounts to another set of accounts (or most simply, one account to another).

Some 'interesting' accounting software use floating point, and end up with unbalanced transactions.  To solve this they end up with 'suspense accounts' and a  slew of other 'interesting' techniques.  I use 'interesting' in lieu of 'highly questionable' to be charitable.

'monetary values' are well suited for fixed point, or as with SQLite, integers.  Usually the less 'interesting' accounting software will allow configuration of the units, which for the US dollar would be 'cents'/'penny' (.00).  One can still have floating point for unit pricing (for example, a widget might be $.0001 per qty) but the 'monetary value' is generally best kept in the smallest unit when expressed as currency.  (note, you can think of a unit price as a fractional expression, while a currency expression would be the actual 'monetary value').

Thus, if you want to handle 'monetary values' in your designer well, consider the ability to have currency definitions (for example, USD, $, 100 and CAD, C$, 100) and the ability to store monetary values in integers (as well as floats for unit prices as noted above).

Furthermore, the fairly recent addition of STRICT tables can help with keeping 'monetary values' exact (by specifying INTEGER in a STRICT table.).  Prior to STRICT, one could store the value to the penny, but if it was stored as a float one would introduce IEEE 754 floating point approximations.  STRICT ensures that your 'monetary values' are stored as INTEGERS and are always exact to the unit of currency.

Summary: SQLite can store and handle financial calculations well, but SQLite does not have the fixed point that some other SQL engines have (for example, no DECIMAL(5,2)), and as a result requires careful thought for app builders in that area.

This is the end of the obligatory public service announcement/warning (first and last sentence is dry humor, to balance the rest that were dry accounting).

You are quite right, SQLite does allow fixed precision calculations via an extension.  It would result in stored values as strings, which then comes full circle to their OP on whether it's just strings or integers. :)

In general, I was trying (and apparently failed miserably as usual) to warn against simply using floating point for 'monetary values'.

I like the decimal extension (and have used it), but much prefer INTEGERS (and like STRICT tables) for money and accounting.  And with their app being a db designer, I would presume not having a non-core extension is preferable.  Also, as hinted at with the USD/CAD examples, multiple currencies can be a monster with strings (the CHECK function would have to apply the currency unit complexities rather than store the base currency unit).  So I don't think strings will get them where then need to go (their github indicates they are considering GBP, etc) as easily as just using INTEGERs in the base unit of currency.  As a fun Harry Potter example, 522 in base unit of Knuts would be 1 Galleon, 1 Sickle.

Perhaps "SQLite sorta, kinda partially has a form of fixed point, but it's likely not a good fit" would have been more accurate. :)

The main point was not to stuff 'monetary values' into floating point and expect precise financial calculations.  Hopefully that message remains clear enough for them.

Yes, monetary calculations.  But not well suited for storing monetary values/currency (as in accounting), as a few have pointed out.

A couple of points about this:

1) Ellerman 1985  here https://ellerman.org/wp-content/uploads/2012/12/DEB-Math-Mag.CV_.pdf has a fairly good explanation of the math of accounting.  There are a few notes that may lead one to think 'aha floating point is OK!', but that's not exactly what the note 7 says.  One way to read that (in my nobody important view) is that one CAN do it, just as there is evidence that bones were used 33000 years ago.

2) In SQL/SQLite, we use integers for things like row counts, or selecting a page from the b-tree.  We COULD use floats, IF we managed the point, but it adds a level of complexity and potential errors for the gain of using more CPU cycles (which if we are trying to heat our homes might be a good trade off I suppose).  We COULD use fixed point as well, but we still have extra complexity for no particular gain.  In both cases we could display the integer as fp, (db page 63.2376 and rounding instead of the integer value has some advantage) but most folks would likely say, why not just use the integer value and be done with it?.  We use integers for things like unique primary keys, db page numbers, and row counts because using floats introduces unnecessary complexity.

3) Here is what CorporateFinanceInstitute has to say about integers and monetary values, and SQL: https://corporatefinanceinstitute.com/resources/data-science/sql-data-types/ .

4) Note that one can have exactness in an accounting system without even using negative numbers (see T-accounts).  In fact because we are counting in accounting, not making up the numbers (well, unless it's an Enron or Madoff) integers represent the whole numbers of currency exactly.  Because it's exact, the precision of integers over floats is roughly infinity-1e30 (in other words that 'precision' is more a measurement of incorrectness).

5) a)  A clue to this exactness is how we 'read' monetary values.  $19.56 is 'read' as "19 dollars AND 56 cents".  Alternatively we can write as "$19 dollars and 56/100".  This is exact.  One can not write a check for 56/27ths (well you can, but it won't be cashed for that, if at all).  This is because of currency's exactness (as another poster pointed out, and I thought made clear with the harry potter money example).  How we display money is a convenience of $19.56.  It's NOT a floating point number, and it's arguable (strongly so) not fixed point either (though fixed point at least works via the exactness).
5) b)  We can cut about 50% off a dollar, and it's still worth exactly 1 dollar (see https://www.frbservices.org/resources/financial-services/cash/exception-processing/mutilated-currency-coin.html#:~:text=Under%20regulations%20issued%20by%20the,United%20States%20currency%20is%20present.).  We can take a part of another dollar unto it, and it's still worth exactly 1 dollar (there are possible fines, but we are simplifying here for our point). Coins are similar in that they can be missing a bit and are  are always worth exactly the denomination.  Accounting systems should reflect this fact.  Accounting systems that store a penny as 0.00999999977648258209228515625 (IEEE 754 .01) are saying that the account has less than a penny.  That is simply incorrect.  What should be stored is either "0.01" (fixed point), or 1 (in pennies). See my SQLite example below for an exact accounting, without storing any number at all.


6) So, in monetary acccounting systems we don't need floats, nor do we even need negative numbers.  In fact we don't even need to record large sums at all.  Below I've created an example db, with transactions that is an overly simplified model of money.  It is exact to the penny, and doesn't even store integers, fixed point, or floats.  (note, similarly, one could store serial numbers of the paper money, mint info on coins to track physical monetary values).:

-- This can be pasted in the SQLite fiddle, then run the db creation, create your reserve, execute the various transactions as desired (using as fancy calulations as desired to determine the 'pennies' to transfer, and report where the amounts are (again, do as much math and statictics as one wishes).


-- this is a small SQLite model that shows money moving around.  It does not attempt to mimic T-accounts (although it might be interesting to make this example do just that).  This simply shows monetary values, not double entry accounting (although it's similar to this SQL exercise).  Some concepts about money:  1) It's created in the US by the Treasury/Fed Reserve.  No other party 'creates' (or 'destoys') the USD (well there is counterfeit money, but we presume we are talking about 'real' money). All other activities with that 'money' are movements of the money.  2) We can employ whatever devices we wish to calculate how much of that money to move, and to which accounts to allocate it, but we always end up in a transaction which moves the pile of pennies.  3) we can not transfer something which we do not have 4) this pile of pennies is always an integer, between 0 and the total number of pennies in the economy.  5) There are no negative pennies.  6) the monetary values are the counts of the pennies, which are whole numbers. 7) Because money moves around in whole units, it shares core characteristics with set theory (and relational database theory).

-- scratch our model money system
drop table reserve;
drop table bank;
drop table you;
drop table otherparty;

-- create our model money system
create table reserve (penny text);
create table bank(penny text);
create table you(penny text);
create table otherparty (penny text);


-- Treasury/Fed Reserve 'prints' money
insert into reserve values ('cent'), ('cent'), ('cent'), ('cent');  -- our reserve balance


-- We then move money around

-- Fed loans money to bank (for the grand total of three cents in circulation)
begin transaction;
insert into bank select penny from reserve limit 1;
delete from reserve where ROWID in ( select ROWID from reserve limit 1);
insert into bank select penny from reserve limit 1;
delete from reserve where ROWID in ( select ROWID from reserve limit 1);
insert into bank select penny from reserve limit 1;
delete from reserve where ROWID in ( select ROWID from reserve limit 1);
commit;

-- Bank gives you a loan (for the grand total of two cents)
begin transaction;
insert into you select penny from bank limit 1;
delete from bank where ROWID in ( select ROWID from bank limit 1);
insert into you select penny from bank limit 1;
delete from bank where ROWID in ( select ROWID from bank limit 1);
commit;

-- you pay second party
begin transaction;
insert into otherparty select penny from you limit 1;
delete from you where ROWID in ( select ROWID from you limit 1);
commit;


-- We can then look at the amounts and location of the money

-- Total money in system (rough equivalent of M1/M2 we are missing some of the accounts necessary to mimic those Federal Reserve metrics in this example).


-- amount of money in reserve (not in ciculation, with velocity of zero)
select count(*) as reserve from reserve;

-- amount of money you have
select count(*) as you from you;

-- amount of money bank has
select count(*) as bank from bank;

-- amount of money the otherparty has
select count(*) as otherparty from otherparty;

----------- end of demo script

Summary:  Floating point is fine for figuring out what the monetary value should be.  When we store that monetary value for accounting however, it must be exact. For that, either fixed point (resulting in not handling all currencies - there are two I believe that are not decimal based at all).  And in order to do rounding one must handle the base unit anyway, so after all that 1e30 stuff one ends up having to round it to base currency anyway (and since there are different rounding rules, you have to carry the rounding rule with the floating point monetary value so it will be rounded properly before it's used.).

For SQLite, integer representation of monetary values expressed in base units of the currency is recommended (by me aka a nobody), since fixed point in SQLite is an extension and thus not quite the same as other SQL implementations in that regard.

In accounting, we move money around, we don't create it out of a mathematical formula.  Hopefully the SQLite fiddle script can enlighten some of the readers to what money is, and perhaps encourage making software handling it be more careful in storing those monetary values.

So, to modify your last sentence for monetary values in accounting:
If one cannot handle accounting for monetary values with integers or fixed point, then they have less than infinity-1e30 hope in floating point.  There is no need for a point in accounting.

Due to time demands, I will have to leave this here.  Hope some find it useful.

Appreciate this post.

I create tables with money with a special character in the column name like a $.
When looping through a recordset if a column name contains '$' a money function is called.
Much easier than writing sql to format the money columns.
Is there a good way to know which columns in a recordset are money without my kludge?