On 02/05/13 11:15, dice_in_trouble wrote:
Hi Cedris,

> Thank you for this wonderful post. Will try this now. Thank you again.
You are welcome.

I suggest you purchase a book on Firebird, as has been suggested, or at
the very least, a decent beginner's guide to database design which will
help you understand the subject.

I have plans to do a Firebird Database Design manual for the
documentation, but as with many things, time is not on my side! So,
don't wait for me!

One other thing, you might be wondering why I've put the COLLECTION
table's payment column as a bigint rather than a floating pint of some
kind? That was just for simplicity.

Currency is a difficult subject to get exact values in when using a
floating point data type (. It's best to use a fixed point data type of
some kind (decimal) or count in pennies (in my case) (bigint) rather
than in pounds and pennies.

However, the data type DECIMAL(18,4) - means 18 digits wide with 4 of
them appearing after the decimal - is useful for a CURRENCY type, so you
could create a domain.

CREATE DOMAIN CURRENCY AS DECIMAL(18,4);

In the UK, that's £99,999,999,999,999.9999 - which is about my hourly
rate! ;-) Seriously, it's a value between -2**63 and +2**63 -1.

Why 18,4? If you are dividing, for some reason, monetary values, the
extra precision will help avoid too many rounding errors.


DECIMALs are good for counting currencies, but beware if the currency
you are counting (or accumulating) could become very large - see above.
The Italian Lira (as it was) for example, could soon fill a CURRENCY
type. The Euro, might take a bit longer! And a DECIMAL is limited to 18
digits.

Have fun.

Cheers,
Norm.

--
Norman Dunbar
Dunbar IT Consultants Ltd

Registered address:
Thorpe House
61 Richardshaw Lane
Pudsey
West Yorkshire
United Kingdom
LS28 7EL

Company Number: 05132767