Table-CLIENT
| 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.