If you have spent any time in the Training Partner database, you probably seen something like the following in the STUDENT_NO, ORG_NO and COURSE_NO fields:
- STUD2010112613253401751458
- ORGA2010112613292507201627
- COUR2010112613241906691320
These are the primary keys (or unique ids) for a student, organization and course respectively.
All primary keys within the Training Partner database have the same format. It is stored in a VARCHAR(30) even though it is always 26 characters long.
First 4 characters are characters representing what table the primary key comes from.
Below are some of the most common:
| Abbrev in Primary Key | Table |
| COUR | COURSES |
| FACI | FACILITIES |
| MATE | MATERIALS |
| INST | INSTRUCTORS |
| FEEI | FEEITEMS |
| PROG | PROGRAMS |
| SITE | SITES |
| ORGA | ORGANIZATIONS |
| STUD | STUDENTS |
| USER | USERS |
| GLAC | GLACCOUNTS |
| ENRS | ENROLLSTATUS |
| CSTA | CLASSSTATUS |
| CGRO | CGROUPS |
The above is far from a complete list.
The next 17 digits are the date and time down to the millisecond that the primary key was created.
yyyymmddhhmissmmm
You can generate this with the following SQL call:
SELECT REPLACE(CONVERT(VARCHAR(20),GETDATE(),102),'.','') + REPLACE(CONVERT(VARCHAR(20),GETDATE(),114),':','')
The last five digits are a random number to ensure uniqueness. It would be zero filled if it was less than 10000.
In summary:
4 characters: table abbreviation
17 characters: date/time to millisecond
5 characters: random 5 digit number