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
