Training Partner–Database Primary Key Format

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

This entry was posted in SQL, Training Partner and tagged , , . Bookmark the permalink. Post a comment or leave a trackback: Trackback URL.

Post a Comment

Your email is never published nor shared. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*
*