Showing posts with label SmallInt. Show all posts
Showing posts with label SmallInt. Show all posts

Tuesday, 3 November 2015

Unique IDs

We only need 128-bit UUID (Universally Unique IDs) generated randomly where there are multiple authorities generating IDs, into a space where the items identified are pooled.


e.g. All National Governing Bodies running their own fencer tracking software.

Randomly generated UUIDs are very slow to use in databases as primary keys, compared to incremented values.

Therefore, you want hybrid keys, with a prefix which increments with each new member of the db table, suffixed by a UUID.

e.g.  000,000,001 0bd6cf13-87c0-a844-b98a-af77d5138f12

To count the Fencers, requires a Class Instance variable, and for Fencers and School fencers to share a class, and not be sub-classed.

Or, to use a class variable, and for all people - fencers, parents, referees, judges, etc to share a common increment.

How many fencers and officials are there, world-wide, over the decades, including historic fencers.

10,000 historic fencers
20,000 FIE-rules fencers in the UK, each with an average lifespan as a fencer of 10 years.  (=> 2,000 new per annum)

Italy and France quintuple the popularity

China:

America, similar popularity

60,000,000 in UK
20,000 fencers

=>3000 people per fencer

1,000,000,000 people in China
If it's as popular as it is in the UK, then
350,000 fencers

If it's 10 times as popular in China, then 3.5 Million

If it's 100 times as popular in China, then 35 million

99,999,999 for the size of the single-authority index - one person in thirty in the world's most populous nation

so personIdNum + personUuid - 2 fields for two items.

NGB IdNum and FIE id num
SubNGB IdNum - e.g if Scottish Fencing ran their own
5 different IDs.  Say they have multiple NGBs?

Governing Body ID would be a Dictionary of Governing Body and ID num

Combined when receiving internationals

So - up to 8 chars of integer for the ID for Person
About 100 times less than that for competition - there are fewer competitions than fencers.
But we'll run at 8bytes/1 64-bit word for the Ids, with another 2 64-bit words for the UUIDs
So Event and Round and Pool and Bout will always be matched up to a competition
8bytes Competition 150 Events per competition (age groups, and gender and weapon), so say 9999 Events
Rounds Up to 9999 rounds (say there is a multi round competition that just keeps going around the world?)
Bouts Up to 9999 bouts in a Round (again some sort of long-lived global competition)

To make them unique when put together,
Competition ID + UUID + Competition + Event + Round + Pool + Bout
8 + 36 + 4 + 4 + 4 = 56 bytes of ID number

SmallInts are stored in 32bit in 32 bit systems, and 64 bit in 64 bit systems
so
4,294,967,276 up to 4.3 billion in a smallint on a 32 bit system
18,446,744,000,000,000,000 - up to 18.5 quintillion on a 64-bit system

So storing as an integer would be space efficient in memory.  Prob still efficient on disc

=> integer Id Nums all round, plus a UUID.