When I save a big number (length 19), generat...

(Jader James) #1

When I save a big number (length 19), generated randomly with formula, the simulator shows the correct number but when I will see the number in app

or database I see like this image…my column in MySQL is bigint unsigned, then it can store till 20 digits (18… …), but 19 digits is 100% in the allowed range…ok

In fact the Appsheet only use 17 digits and replace the two last numbers to zero, I already try to fill 1234567890123456789 (19 length) explicitly in initial value of the column and Appsheet again cut the last two digits and replace with zero, any limitation?

(Reza Raoofi) #2

What are you using such a huge random number for? Is it something needed for your business requirements?

(Jader James) #3

HI @RezaRaoofi how do you do? we are here again and again :smiley:

so i choice to use number (because speed in sql database) and i choice to use bigint to never coincide the same number between two user rows (never and never), i construct a formula that use 10 numbers (DATE based) = (YY + DDD + SSSSS)

YY = year = 19 DDD = today = 015 (max is 365) SSSSS = seconds of the day (0 to 86400)

+9 numbers using RANDBETWEEN

maybe i have being cautelous, but i prefer imagine that one row of one user never will coincide with other.

because if them catch the same number its will generate a lot of problem to generate other id without lost all data filled by user, agree?

(Jader James) #4

“The generated ID is not strictly unique. Instead, it is sufficiently random as to be effectively unique for all practical uses. The chance of a duplicate ID being generated is virtually zero: just 1 in over 28 trillion.”

Ok, but the bigger is my database over time the bigger is chance to have collision with old entries…

And imagine 1 million rows per year…yet uniqueID() is enough, and if was a collision? How mitigate…there are a lot of variables to think

(Reza Raoofi) #5

How do you store that combination of text digits (YYDDDSSS…) into database as number? Do you use NUMBER() function in AppSheet to convert it to number on the app side?

(Jader James) #6

@RezaRaoofi yes, number + concatenate… confess I am in doubt now about uniqueID() + char column or number(…) + Bigint… performance, storage, readable etc are variables

(Aleksi Alkio) #7

How about YEAR(TODAY())&UNIQUEID() ?

(Reza Raoofi) #8

@jader_james Well in most of those articles about better performance for integer vs text the reason is that integer uses smaller size than text; e.g. with an 8 byte size BIG INT you can get 19 digit integer while the same size UNIQUEID() text will get you only 8 character length random ID.

Like I said it depends on the concurrency rate you estimate, but regarding years worth of data those first 5 digits will take care of uniqueness down to a particular day; the rest depends on how many unique possibilities you want to consider for 100 online users per day which I do not think it would be concerning, because 1 million records/year would mean only 2740 new rows per day, I do not think you would need too many unique possibilities per day for extra digits in addition to the first 5 digits.

(Jader James) #9

@RezaRaoofi its nice argument with you to open the mind…

good, if i have 3000 events per day, so, with 86400 seconds per day (almost 90 000 seconds in one day)

then, i will have one collision per month (90 000 / 3000 = 30)

its for my ten first numbers (YYDDDSSSSS), and i will estimate the number of random values that i will need based in my app

(Jader James) #10

and the final calculus must be one bit more big, because the sleep hours (22 - 08 almost nobody will save data, only ones crime scene experts in homicide local) :smiley:

(Reza Raoofi) #11

Hi Jader! :slight_smile: My question was “what you are using it for?”; I didn’t ask why.

You seem to need it for Key column; am I correct?

(Jader James) #12

@RezaRaoofi sorry, english is not my native language…

yes, for key column in a environment that users can be offline for a long time

(Reza Raoofi) #13

How many concurrent users could possibly add rows that would lead to generating new keys in your database? You should factor that to the number of additional digits after date+second (assuming concurrent user means users inserting in the same second). I think 19 digit containing 5 digits for second, plus 9 digit random number is overkill!

(Jader James) #14

i understand what you are talking it all…in true, when i create this formula i was thinking in the best scenario, with 100-1000 users, and maybe 100 users online in the same moment and thinking about the app to be a game changer and to be used for 5 -10 years, and with it all yet never generate a collision between key columns, but i agree with you, 17 numbers can be enough!

(Reza Raoofi) #15

Even if 100 online users constantly add rows what are the chances to enter at the same 0.00001 second! Now adding 9 digit random number to the equation too! As for commulative data for years, the date part will automatically take care of that.

I would say even YYDDDSSSSS+3 random digit (i.e. 13) would be more than enough.

(Alper) #16

You may try something like dec2hex(yydddsssss) ? Though it will not be bigint

(Aleksi Alkio) #17

@jader_james Why don’t you use UNIQUEID() with 8 digits?

(Jader James) #18

That I talk in start seems like a bug in platform…but uniqueID() generate a string and I read a lot of best practices in SQL and decided to use numbers like key column.

And what the chance of collisions in uniqueID() for hundreds of user’s and for decades…