Convention for Database Tables: Difference between revisions
Jump to navigation
Jump to search
(→Views) |
|||
(7 intermediate revisions by the same user not shown) | |||
Line 44: | Line 44: | ||
{| | {| | ||
| valign="top" | | | valign="top" | | ||
=== | ===Tables Template=== | ||
[M]{1}────────────────M ─┐ | [M]{1}────────────────M ─┐ | ||
│ | │ | ||
Line 60: | Line 60: | ||
| valign="top" | | | valign="top" | | ||
=== Versions Tables === | === Versions Tables === | ||
[M]{1}───────M ─┐ | [M]{1}───────M ─┐ | ||
Line 95: | Line 96: | ||
| valign="top" | | | valign="top" | | ||
=== | === Collection Tables === | ||
[M]{1}───────M ─┐ | [M]{1}───────M ─┐ | ||
│ | │ | ||
Line 102: | Line 103: | ||
[J|T|V]{1}───T ─┘ │ | [J|T|V]{1}───T ─┘ │ | ||
│ | │ | ||
[ | [C]{1}───────C ─┐ │ | ||
├─C00 ─┼─M00TC00X00 | |||
[0-9A-Z]{2}──00─┘ │ | [0-9A-Z]{2}──00─┘ │ | ||
│ | │ | ||
Line 111: | Line 112: | ||
| valign="top" | | | valign="top" | | ||
=== | === Entries Tables === | ||
[M]{1}───────M ─┐ | [M]{1}───────M ─┐ | ||
│ | │ | ||
Line 118: | Line 119: | ||
[J|T|V]{1}───T ─┘ │ | [J|T|V]{1}───T ─┘ │ | ||
│ | │ | ||
[ | [E]{1}───────E ─┐ │ | ||
├─E00 ─┼─M00TE00X00 | |||
[0-9A-Z]{2}──00─┘ │ | [0-9A-Z]{2}──00─┘ │ | ||
│ | │ | ||
Line 125: | Line 126: | ||
├─X00 ─┘ | ├─X00 ─┘ | ||
[0]{2}───────00─┘ | [0]{2}───────00─┘ | ||
|} | |} | ||
{| | {| | ||
| valign="top" | | | valign="top" | | ||
=== | === Identity Tables === | ||
[M]{1}───────M ─┐ | [M]{1}───────M ─┐ | ||
│ | │ | ||
Line 136: | Line 138: | ||
[J|T|V]{1}───T ─┘ │ | [J|T|V]{1}───T ─┘ │ | ||
│ | │ | ||
[ | [I]{1}───────I ─┐ │ | ||
├─I00 ─┼─M00TI00X00 | |||
[0-9A-Z]{2}──00─┘ │ | [0-9A-Z]{2}──00─┘ │ | ||
│ | │ | ||
Line 145: | Line 147: | ||
| valign="top" | | | valign="top" | | ||
=== | === Local Tables === | ||
[M]{1}───────M ─┐ | [M]{1}───────M ─┐ | ||
│ | │ | ||
Line 152: | Line 154: | ||
[J|T|V]{1}───T ─┘ │ | [J|T|V]{1}───T ─┘ │ | ||
│ | │ | ||
[ | [L]{1}───────L ─┐ │ | ||
├─L00 ─┼─M00TL00X00 | |||
[0-9A-Z]{2}──00─┘ │ | [0-9A-Z]{2}──00─┘ │ | ||
│ | │ | ||
Line 161: | Line 163: | ||
| valign="top" | | | valign="top" | | ||
=== | === Metadata Tables === | ||
[M]{1}───────M ─┐ | [M]{1}───────M ─┐ | ||
│ | │ | ||
Line 168: | Line 170: | ||
[J|T|V]{1}───T ─┘ │ | [J|T|V]{1}───T ─┘ │ | ||
│ | │ | ||
[ | [M]{1}───────M ─┐ │ | ||
├─M00 ─┼─M00TM00X00 | |||
[0-9A-Z]{2}──00─┘ │ | [0-9A-Z]{2}──00─┘ │ | ||
│ | │ | ||
Line 193: | Line 195: | ||
[A|C|E|I|L|M|V]{1}──E ─┐ │ | [A|C|E|I|L|M|V]{1}──E ─┐ │ | ||
├─E11 ─┘ | ├─E11 ─┘ | ||
[0]{2} | [0]{2}──────────────11─┘ | ||
| valign="top" | | | valign="top" | | ||
=== Materialized View === | === Materialized View === | ||
[M]{1}──────────────M ─┐ | [M]{1}──────────────M ─┐ | ||
Line 209: | Line 212: | ||
│ | │ | ||
[X]{1}──────────────X ─┐ │ | [X]{1}──────────────X ─┐ │ | ||
├─X00 ─┘ | |||
[0]{2} | [0-9A-Z]{2}─────────00─┘ | ||
|} | |} | ||
Line 409: | Line 412: | ||
| valign="top" | | | valign="top" | | ||
* [https://danielmiessler.com/study/difference-between-uri-url/ Difference Between a URI and a URL] | * [https://stackoverflow.com/questions/2568668/ Base 36 to Base 10 conversion using SQL] | ||
* [https://mariadb.com/kb/en/conv/ MariaDB <code>CONV(N,from_base,to_base)</code>] | |||
* [https://danielmiessler.com/study/difference-between-uri-url/ Difference Between a URI and a URL] | |||
* [https://gist.github.com/btbytes/7159902 Base36 Conversion in PostgreSQL] | |||
* [https://stackoverflow.com/questions/5997241/ PostgreSQL Base-10 to Base-36] | |||
* [[MapStruct]] | |||
* [[Liquibase]] | |||
* [[Lombok]] | |||
|} | |} |
Latest revision as of 11:07, 14 January 2022
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
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 | .
|