Convention for Database Tables

static char *base36enc(long unsigned int value) {
	char base36[36] = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ";
	/* log(2**64) / log(36) = 12.38 => max 13 char + '\0' */
	char buffer[14];
	unsigned int offset = sizeof(buffer);

	buffer[--offset] = '\0';
	do {
		buffer[--offset] = base36[value % 36];
	} while (value /= 36);

	return strdup(&buffer[offset]); // warning: this must be free-d by the user
}

static long unsigned int base36dec(const char *text) {
	return strtoul(text, NULL, 36);
}

Types of Table

Order	Code	Types	Purpose	Tables	Views
1	I	Identity	Identity Tables	M00TI00X00 ~ MZZTIZZX00	M00VI00X00 ~ MZZVIZZXZZ
2	M	Metadata	Metadata Tables	M00TM00X00 ~ MZZTMZZX00	M00VM00X00 ~ MZZVMZZXZZ
3	C	Collection	Collection Tables	M00TS00X00 ~ MZZTCZZX00	M00VS00X00 ~ MZZVCZZXZZ
4	E	Entries	Entries Tables	M00TE00X00 ~ MZZTEZZX00	M00VE00X00 ~ MZZVEZZXZZ
5	J	Join	Join Tables n - n	M00JX00X00 ~ MZZJXZZXZZ	Z=[0-Z]{1},X=[ACEILMV]{1}
6	A	Audit	Audit Tables	M00TA00X00 ~ MZZTAZZX00	M00VA00X00 ~ MZZVAZZXZZ
7	L	Local	Local Tables	M00TL00X00 ~ MZZTLZZX00	M00VL00X00 ~ MZZVLZZXZZ
8	V	Versions	Versions Tables	M00TV00X00 ~ MZZTVZZX00	M00VV00X00 ~ MZZVVZZXZZ

Nomenclature

Tables Template [M]{1}────────────────M ─┐ │ [0-9A-Z]{2}───────────00─┼─M00T─┐ │ │ [J|T|V]{1}────────────T ─┘ │ │ [A|C|E|I|L|M|V]{1}────M ─┐ │ ├─M00 ─┼─M00TM00X00 [0-9A-Z]{2}───────────00─┘ │ │ [X]{1}────────────────X ─┐ │ ├─X00 ─┘ [0-9A-Z]{2}───────────00─┘

Versions Tables [M]{1}───────M ─┐ │ [0-9A-Z]{2}──00─┼─M00T─┐ │ │ [J|T|V]{1}───T ─┘ │ │ [V]{1}───────V ─┐ │ ├─V00 ─┼─M00TV00X00 [0-9A-Z]{2}──00─┘ │ │ [X]{1}───────X ─┐ │ ├─X00 ─┘ [0]{2}───────00─┘

---

Audit Tables [M]{1}───────M ─┐ │ [0-9A-Z]{2}──00─┼─M00T─┐ │ │ [J|T|V]{1}───T ─┘ │ │ [A]{1}───────A ─┐ │ ├─A00 ─┼─M00TA00X00 [0-9A-Z]{2}──00─┘ │ │ [X]{1}───────X ─┐ │ ├─X00 ─┘ [0]{2}───────00─┘

Collection Tables [M]{1}───────M ─┐ │ [0-9A-Z]{2}──00─┼─M00T─┐ │ │ [J|T|V]{1}───T ─┘ │ │ [C]{1}───────C ─┐ │ ├─C00 ─┼─M00TC00X00 [0-9A-Z]{2}──00─┘ │ │ [X]{1}───────X ─┐ │ ├─X00 ─┘ [0]{2}───────00─┘

Entries Tables [M]{1}───────M ─┐ │ [0-9A-Z]{2}──00─┼─M00T─┐ │ │ [J|T|V]{1}───T ─┘ │ │ [E]{1}───────E ─┐ │ ├─E00 ─┼─M00TE00X00 [0-9A-Z]{2}──00─┘ │ │ [X]{1}───────X ─┐ │ ├─X00 ─┘ [0]{2}───────00─┘

Identity Tables [M]{1}───────M ─┐ │ [0-9A-Z]{2}──00─┼─M00T─┐ │ │ [J|T|V]{1}───T ─┘ │ │ [I]{1}───────I ─┐ │ ├─I00 ─┼─M00TI00X00 [0-9A-Z]{2}──00─┘ │ │ [X]{1}───────X ─┐ │ ├─X00 ─┘ [0]{2}───────00─┘

Local Tables [M]{1}───────M ─┐ │ [0-9A-Z]{2}──00─┼─M00T─┐ │ │ [J|T|V]{1}───T ─┘ │ │ [L]{1}───────L ─┐ │ ├─L00 ─┼─M00TL00X00 [0-9A-Z]{2}──00─┘ │ │ [X]{1}───────X ─┐ │ ├─X00 ─┘ [0]{2}───────00─┘

Metadata Tables [M]{1}───────M ─┐ │ [0-9A-Z]{2}──00─┼─M00T─┐ │ │ [J|T|V]{1}───T ─┘ │ │ [M]{1}───────M ─┐ │ ├─M00 ─┼─M00TM00X00 [0-9A-Z]{2}──00─┘ │ │ [X]{1}───────X ─┐ │ ├─X00 ─┘ [0]{2}───────00─┘

---

Join Tables [M]{1}──────────────M ─┐ │ [0-9A-Z]{2}─────────00─┼─M00J─┐ │ │ [J|T|V]{1}──────────J ─┘ │ │ [A|C|E|I|L|M|V]{1}──E ─┐ │ ├─E00 ─┼─M00JE00E11 [0-9A-Z]{2}─────────00─┘ │ │ [A|C|E|I|L|M|V]{1}──E ─┐ │ ├─E11 ─┘ [0]{2}──────────────11─┘

Materialized View [M]{1}──────────────M ─┐ │ [0-9A-Z]{2}─────────00─┼─M00V─┐ │ │ [J|T|V]{1}──────────V ─┘ │ │ [A|C|E|I|L|M|V]{1}──E ─┐ │ ├─E00 ─┼─M00VE00X00 [0-9A-Z]{2}─────────00─┘ │ │ [X]{1}──────────────X ─┐ │ ├─X00 ─┘ [0-9A-Z]{2}─────────00─┘

Data Size Convention

;data size convention (where n is integer number)
STRING  : 1 + 2 + 4 + 08 + 16 + 32 + 64 + 128 + 256 + 2^n
NUMBER  : 3 + 6 + 9 + 12 + 15 + 18 + 21 + 024
DATE    : yyyy-MM-dd | yyyy-MM-dd'T'HH:mm:ss
BOOLEAN : 0 + 1

${\begin{array}{lcr}f(a)&=&a+2a+3a+4a+5a+6a+7a+8a+na\\f(3)&=&3+6+9+12+15+18+21+24+27\\\end{array}}$

---

${\begin{array}{lcr}f(a)&=&a^{0}+a^{1}+a^{2}+a^{3}+a^{4}+a^{5}+a^{6}+a^{7}+a^{8}+a^{n}\\f(2)&=&2^{0}+2^{1}+2^{2}+2^{3}+2^{4}+2^{5}+2^{6}+2^{7}+2^{8}+2^{n}\\f(2)&=&1+2+4+8+16+32+64+128+256+512\\\end{array}}$

Id Field Convention

JavaScript

var Chorke=Chorke||(function(me){ me = { $class : 'Chorke', $package : 'Default', }; return me; })(); /** * @public * @static */ ;Chorke.Base36||(function($, me, RADIX){ RADIX = 36; me = { $package: 'Chorke', $class : 'Chorke.Base36', Encode : function(number, base36){ base36 = (number).toString(RADIX) base36 = base36.toUpperCase() return base36 }, Decode : function(base36, number){ number = parseInt(base36, RADIX) return number } }; $.Base36 = me; })(Chorke);

/** * @public * @static */ ;Chorke.Severity||(function($, w, me){ me = { $package: 'Chorke', $class : 'Chorke.Severity', Info : function(message){ w.console&&w.console.info(message) } }; $.Severity = me; })(Chorke, window); /** * @anonymous * @static */ (function($){ $.Severity.Info($.Base36.Encode(123456789)); $.Severity.Info($.Base36.Decode("2R4Y")); })(Chorke); // Output // 21I3V9 // 128482

Java

public class Base36 {
    public static final int RADIX = 36;

    public static String encode(int integer) {
        String base36 = Integer.toString(integer, RADIX);
        return base36.toUpperCase();
    }

    public static int decode(String base36) {
        String lower = base36.toLowerCase();
        return Integer.parseInt(lower, RADIX);
    }
}

Size Index

Base36 Start	Base36 End	Decimal Start	Decimal End	No.of Elements	Usages as ID
0	z	0	35	36	type, site, status, group, category
10	zz	36	1,295	1,260	type, user, module, table, project
100	zzz	1,296	46,655	45,360	type, group, category, subcategory
1,000	z,zzz	46,656	1,679,615	1,632,960	user, category, subcategory
10,000	zz,zzz	1,679,616	60,466,175	58,786,560	category, subcategory, branch
100,000	zzz,zzz	60,466,176	2,176,782,335	2,116,316,160	user, admin, manager, executive
1,000,000	z,zzz,zzz	2,176,782,336	78,364,164,095	76,187,381,760	oauth, client, commit, invoice
10,000,000	zz,zzz,zzz	78,364,164,096	2,821,109,907,455	2,742,745,743,360	user, civic, staff, visa, passport
100,000,000	zzz,zzz,zzz	2,821,109,907,456	101,559,956,668,415	98,738,846,760,960	cheque, invoice, certificate
1,000,000,000	z,zzz,zzz,zzz	101,559,956,668,416	3,656,158,440,062,975	3,554,598,483,394,560	user, account, wallet, qrcode

Base Index

Index	Binary	Char		Index	Binary	Char		Index	Binary	Char		Index	Binary	Char
0	000000	0		16	010000	G		32	100000	W		48	110000	m
1	000001	1		17	010001	H		33	100001	X		49	110001	n
2	000010	2		18	010010	I		34	100010	Y		50	110010	o
3	000011	3		19	010011	J		35	100011	Z		51	110011	p
4	000100	4		20	010100	K		36	100100	a		52	110100	q
5	000101	5		21	010101	L		37	100101	b		53	110101	r
6	000110	6		22	010110	M		38	100110	c		54	110110	s
7	000111	7		23	010111	N		39	100111	d		55	110111	t
8	001000	8		24	011000	O		40	101000	e		56	111000	u
9	001001	9		25	011001	P		41	101001	f		57	111001	v
10	001010	A		26	011010	Q		42	101010	g		58	111010	w
11	001011	B		27	011011	R		43	101011	h		59	111011	x
12	001100	C		28	011100	S		44	101100	i		60	111100	y
13	001101	D		29	011101	T		45	101101	j		61	111101	z
14	001110	E		30	011110	U		46	101110	k		62	111110	_
15	001111	F		31	011111	V		47	101111	l		63	111111	.

References

Base36 Encoding and Decoding Oracle SQL Developer Luhn Algorithm Modulus 11 Check digit Base64 Base36 Base32 Locale JPA

Base 36 to Base 10 conversion using SQL MariaDB CONV(N,from_base,to_base) Difference Between a URI and a URL Base36 Conversion in PostgreSQL PostgreSQL Base-10 to Base-36 MapStruct Liquibase Lombok