Created by: Lester Caine, Last modification: 20 Aug 2008 (11:10 UTC)
Field Type NULL Default Description
UCRN Numeric(18,0) not null   Unique client reference number
CLTYPE Smallint not null   Used to identify type of caller
TITLE Varchar(35)     Title
FORENAME Varchar(35)     Forename (Person Given Name)
SURNAME Varchar(35)     Surname (Person Family Name)
SUFFIX Varchar(35)     Suffix (Person Name Suffix)
ORGANISATION Varchar(100)      
DOB Date not null   Date of birth
EIGHTEENTH Date not null   Date of eligibility to go onto electoral roll
DOD Date     Date client died
LAST_UPDATE_DATE Date not null   Date of last update
ENTRY_DATE Date not null   Date record was created
NINO Varchar(9)     National Insurance number
SPECIALNEEDS Varchar(4)     Important special needs flags - these will be extended via CLIENT_XREF
NOTE Varchar(40)     Legacy caller text field - this will be moved to CLIENT_XREF
MEMO Text     Legacy caller notes field - this will be replaced by timestamped comments

Links to the client cross-reference table for additional information such as addresses.
With the lack of a standard document to use for field sizes, these are taken from the Person record on the government's data standard site. It may be necessary to expand these at some point, but the existing system only allows for 32 character forename and surname, so this should not be a problem initially.
CLTYPE is used to distinguish between a number of types of records. A UCRN of '0' indicates that there is no caller attached to a record, and the CLTYPE entry on this CLIENT record will be NULL as it can not be edited.  I and C are used to identify an individual or a corporate record, while V identifies a transient VISITOR entry. It would be anticipated that I and C records will have an associated local UPRN while V records are used where matching to local LLPG data is not necessary.
A new flag of X will be introduced, which will be used to flag duplicate client records. Once a duplication is identified, the associated ticket and cross-reference records are amended to point to the correct UCRN, and the title field of the record being deleted is changed to hold the new UCRN. This will allow an 'update' extract to be created to amend other systems. The old records will be retained until such time as a delete instruction is received from a master system, unless the CMS system is taking that roll, in which case the records will be deleted once any secondary systems have been updated.
Additional CLTYPE entries may be added to handle other special cases and allow filtering of data.