Hi

Just getting to know edb. Loving it so far.

I needed some clarification of using field types correctly.

1. VarChar or Char

They are both the same ? right ?

Not sure what the diffrences would be in usage/ sql/ searching etc.

Is there any merits for using one over the other.?

2. Decimal with a scale of 2

Is that sufficient / best practice to use for day to day money calculations and to *stop*
any rounding errors further down the line?

3. Using GUID

Is that automatically maintained  once defined in table structure ?

If not,  do I have to generate this, Is there a prebuilt function for this?.

Kind regards

Kamran

kamran

>I needed some clarification of using field types correctly.
>
>1. VarChar or Char
>
>They are both the same ? right ?

No - Char is right padded with spaces VarChar isn't, but it will store any spaces appended by the user

>Not sure what the differences would be in usage/ sql/ searching etc.

Table size - no, index size - not sure, searching speed - unnoticeable

>Is there any merits for using one over the other.?

I don't think there's much in it these days. Unless I have a single character column I always use VARCHAR. At one point the trailing spaces were significant in terms of sql filter conditions but (I think) Tim altered that. It can still be a problem in Delphi where the trailing spaces will prevent a math.

>2. Decimal with a scale of 2
>
>Is that sufficient / best practice to use for day to day money calculations and to *stop*
>any rounding errors further down the line?

Should do

>3. Using GUID
>
>Is that automatically maintained once defined in table structure ?

If you set the column to GUID and generated and use CURRENT_GUID as the generation expression it will

Roy Lambert

<< Not sure what the diffrences would be in usage/ sql/ searching etc. >>

Don't use CHAR unless you understand the differences in how it works.  For 99.9% of the cases out there, VARCHAR is what you want.

<< Is that sufficient / best practice to use for day to day money calculations and to *stop* any rounding errors further down the line? >>

Yes.

<< 3. Using GUID

Is that automatically maintained  once defined in table structure ? >>

What do you mean by "automatically-maintained" ?

Tim Young
Elevate Software
www.elevatesoft.com

Kamran

>>2. Decimal with a scale of 2
>>
>>Is that sufficient / best practice to use for day to day money calculations and to *stop*
>>any rounding errors further down the line?

>Should do

Only thing I would add to Roy's points are:

1. I have some customers who like to record the cost of products and ingredients to a currency accuracy of more than 2 decimal places ...

2. You should be careful of how different systems manage rounding.

In rare case some systems round down and others round up in situations such as 1.005 becoming either 1.01 or 1.00. For example UK tax office wages has different norms to "normal" maths ... so you need to cope with this.

For the above reasons I tend to stick with 4 decimal places for all "Currency" fields (defined DECIMAL(19,4) ) just for safety.

Adam Brett wrote:

> 1. I have some customers who like to record the cost of products and
> ingredients to a currency accuracy of more than 2 decimal places ...

The system I am working on for a client uses 5 deciman places, because
at scale you can end up "losing" thousands of pounds. If you buy 10,000
of something at 1.02 instead of 1.015, that's real money and can mean
winning or losing business or profit. So this is something to consider,
even if you reject it and stick with 2 decimal places.

--

Matthew Jones

Hi

Thank you to everyone. I get it now.

Regards

Kamran

Hey

Almost everything has been already said, I would only like to add 2 details about the DECIMAL type:

1.
Regardless of the precision you specify, EDB always uses an implementation-defined precision of 19 digits with a maximum of 4 decimal places.
That means that if you need 5 decimal places, as Matthew illustrated, you would have to use the DOUBLE PRECISION or FLOAT type instead, but thats not an exact type but a floating point type that can cause rounding and representation errors.

2.
When the values are money amounts, I always use DECIMAL(19,2) for totals (ie, Total value of an invoice) but DECIMAL(19, 4) for other values that are not totals, like unit prices of items for example.
The reason is the same that Matthew has already referred.

--
Fernando Dias
[Team Elevate]

Hi Fernando

Thanks for clarifying further.

Cheers

Kamran

Fernando Dias wrote:

Hey

Almost everything has been already said, I would only like to add 2 details about the DECIMAL type:

1.
Regardless of the precision you specify, EDB always uses an implementation-defined precision of 19 digits with a maximum of 4 decimal places.
That means that if you need 5 decimal places, as Matthew illustrated, you would have to use the DOUBLE PRECISION or FLOAT type instead, but thats not an exact type but a floating point type that can cause rounding and representation errors.

2.
When the values are money amounts, I always use DECIMAL(19,2) for totals (ie, Total value of an invoice) but DECIMAL(19, 4) for other values that are not totals, like unit prices of items for example.
The reason is the same that Matthew has already referred.

--
Fernando Dias
[Team Elevate]