Long or Big Int For Numbers

Squiffy

Whilst I can enter large numbers onto my data entry form, and have rules perform calculations perfectly on them, as soon as the BO gets saved anything over 32 bit gets converted down (to 2147483647).

Is there anything I can do about that? I need to store long phone numbers (often 16 or 17 digits). I could store them as strings but there are going to be 10s of millions of them, and I would imagine searches would be faster as number fields.

Jaymer

if the next step up would be a 64 bit #, then thats 8 bytes.
vs a string phone # of 16 bytes.
its only by a factor of 2... 10 million bytes values or 5 million bytes. 50 million or 25 million.

is that really going to make a diff to a backend database doing a search?

if there was a cost per byte, and you were wasting 2x on each entry, then you'd pay double over a time period.
if you were serving a million sodas in your restaurant chain each year, and you could sell Pepsi at 50% cheaper than Coke, then thats money directly in your pocket (if you want the tradeoff in taste/quality). but in this case, whats the downside in leaving a phone # as a string?
Jaymer...

Squiffy

I suppose the acid test is to, well, test.

I was always under the impression that searching for numbers was faster due to the way text matching was done. Whilst that's not going to affect a user interface much, I will have an external server making many requests a second for this data, possibly through an AIM exposed API call.

I may however be harbouring an incorrect assumption, I suppose.

Squiffy

Arithmetic is another reason for wanting numbers. Whilst it looks like it can sort of perform arithmetic on text fields, it turns the result into scientific notation. For example 97550020000 + 1000 produces 9.75500200000999E14 (edit - because of course it is adding it to the end;how to cast?)

Whilst I'm sure I could process that string into a number-like format, I'd rather use numbers.

Also - how would I do "between" searches on strings? I am only guessing but that doesn't look like something well suited to strings.

Jaymer

there are 2 issues... one of database size/throughput/back end index searching, etc., and
front end handling via Aware.

RE #2: I'm not even sure how nicely AIM handles dynamic formatting based on a phone # format string... lately (in 2016) there been issues with formatting commas and decimals correctly under certain circumstances. then there is the issue of a dynamic field not updating based on a real-time field change in the UI.

so I'd suggest just playing with a strategy for how you think you're going to format a phone # in a variety of formats... and present that in the UI for a user to see. how are you storing that format string in the user's record?

Q: Do you really think you're going to need to search for a range of phone #s between 2 values?
For #s in the US, if the phone field was 10 characters, then its easy for any platform to search for a string between 8135550000 and 8135559999. Doesn't matter if its a string of Phone #s or account #s or last names. Is doesn't need to be stored as a NUMBER datatype.

You might consider removing country prefix to a separate field. And extension as a suffix field.
Jaymer...

Squiffy

Jaymer wrote
there are 2 issues... one of database size/throughput/back end index searching, etc., and
front end handling via Aware.

Agreed. It seems to work fine in the front end but goes bandy when saved, implying a contradiction in how long numbers are handled.

RE #2: I'm not even sure how nicely AIM handles dynamic formatting based on a phone # format string...

I don't want numbers in any special format. I want them in raw international format (eg 442038039999 for a UK 03 number), hence why I normally store these in a bigint in MySQL. Sometimes the numbers require prefixes and some countries handle DDI (or DID) as a postfix to the main dialled number (instead of the last N digits being the DDI). Getting rarer but it still happens.

Q: Do you really think you're going to need to search for a range of phone #s between 2 values?

Absolutely. In some instances the same number ranges can be supplied by many telecoms carriers, so if I have 20 million numbers supplied by 7 carriers that's either a 140 million record lookup or seven "between" startnum & endnum lookups. These look ups are real time on arrival of a phone call (there is a telephony backend as well as an administrative one).

...then there is the issue of a dynamic field not updating based on a real-time field change in the UI.

For me, number fields have been updating fine in the UI, so not seen that issue myself.

...easy for any platform to search for a string between 8135550000 and 8135559999. Doesn't matter if its a string ...

Never tried that before (never needed to), but I'll give it a go and see what happens.

You might consider removing country prefix to a separate field. And extension as a suffix field.

Yes, if I really can't store a bigint then I'll have to look at some other way. Might have a knock on effect to the telephony servers though, which was a pain I was trying to avoid. I'll have gone from replacing the UI to rebuilding most of the infrastructure code.

Squiffy

Here's a question -

am I safe to ALTER the BO table to change an int to a bigint?
I'd do this in the advanced section of the business space properties, where you can run a script after startup.

customaware

David,

I am not sure regarding the allowance of big numbers. It surprises me that it does not work so will take your word for it.

As an alternative option.... Is there any way you can implement what you want by using a String?

Squiffy

eagles9999 wrote
I am not sure regarding the allowance of big numbers. It surprises me that it does not work so will take your word for it.

It's fine in the UI, all arithmetic works as 64 bit numbers. But if you look at the table in the DB it's defined as an int so gets garbled on saving. I can only conclude a disparity between front and back end.

As an alternative option.... Is there any way you can implement what you want by using a String?

I'm exploring that now. My issue is I may need to add 10 or 10,000,000 to a start number to get the end of a range. I'm exploring a way to cast to a long to perform the arithmetic then back to a string for storage.

It is a bit of a pain though because I have external servers reading this data for live lookups and these will (may) have to change.

I'm opening another thread regarding altering the table, as that might help if I can do it.

BLOMASKY

my 0.02 cents worth

Store the phone # as a string, Yes, it might take more space, but you have a lot of options. (I say this from experience, I have a telemarketing application, while much smaller than yours, only a little over 1 million rows, I have an index built on the phone (duh) and can search for a single # or a range very fast (of course, if I try to return 10,000 matches, then we have network bandwidth issues...) I know you don't have to format them, but You can also have fields in your table with flags for country codes, or ??? that is easy to extract from a string. As far as appending to a string, that is easier and more exact than doing math with a LARGE #.

Bruce

Squiffy

Thanks all for your input.
Looking at alternatives to my original plan, see where it takes me.