| Primary Key | Field Type |
|---|---|
 ID |
uniqueidentifier |
| Field | Field Type | Null | Notes | Description |
|---|---|---|---|---|
 CONSTITUENTID |
uniqueidentifier | |||
| KEYNAME |
nvarchar(100) | Default = '' | ||
| FIRSTNAME |
nvarchar(50) | Default = '' | ||
| NAMETYPECODE |
tinyint | Default = 0 | ||
 NAMETYPE |
nvarchar(16) (Computed) |  |
CASE [NAMETYPECODE] WHEN 0 THEN N'Constituent name' WHEN 1 THEN N'Maiden name' WHEN 2 THEN N'Nickname' WHEN 3 THEN N'Alias' END | |
| POSTCODE |
nvarchar(12) | Default = '' | ||
| STREETNUMBER |
nvarchar(12) | Default = '' | ||
| STREETNAME |
nvarchar(150) | Default = '' | ||
| ISORGANIZATION |
bit | Default = 0 | ||
| ISGROUP |
bit | Default = 0 | ||
| KEYNAME4 |
nvarchar(4) (Computed) | |
left(KEYNAME,4) | |
| KEYNAMESOUNDEX |
varchar(5) (Computed) | |
soundex(KEYNAME) | |
| STREETNAME4 |
nvarchar(4) (Computed) | |
left(STREETNAME,4) | |
| STREETNAMESOUNDEX |
varchar(5) (Computed) | |
soundex(STREETNAME) | |
| FIRSTNAME3 |
nvarchar(3) (Computed) | |
left(FIRSTNAME,3) | |
| POSTCODE3 |
nvarchar(3) (Computed) | |
left(POSTCODE,3) | |
| MIDDLENAME |
nvarchar(50) | Default = '' |
| Foreign Key | Field Type | Null | Notes | Description |
|---|---|---|---|---|
 ADDRESSID |
uniqueidentifier | |
ADDRESS.LOCALID | |
| COUNTRYID |
uniqueidentifier | |
COUNTRY.LOCALID | |
| ALIASID |
uniqueidentifier | |
ALIAS.ID | |
| TITLECODEID |
uniqueidentifier | |
TITLECODE.LOCALID | |
| SUFFIXCODEID |
uniqueidentifier | |
SUFFIXCODE.LOCALID |
| Index Name | Field(s) | Unique | Primary | Clustered |
|---|---|---|---|---|
 IX_SEARCHCONSTITUENT_ADDRESSID |
ADDRESSID | |||
| IX_SEARCHCONSTITUENT_ALIASID |
ALIASID | |||
| IX_SEARCHCONSTITUENT_CONSTITUENTID |
CONSTITUENTID | |||
| IX_SEARCHCONSTITUENT_KEYNAME_FIRSTNAME3_POSTCODE |
KEYNAME, FIRSTNAME3, POSTCODE | |||
| IX_SEARCHCONSTITUENT_POSTCODE_KEYNAME4 |
POSTCODE, KEYNAME4 | |||
| IX_SEARCHCONSTITUENT_POSTCODE_STREETNAMESOUNDEX_FIRSTNAME3 |
POSTCODE, STREETNAMESOUNDEX, FIRSTNAME3 | |||
| IX_SEARCHCONSTITUENT_POSTCODE3_KEYNAMESOUNDEX_STREETNAME4_STREETNUMBER |
POSTCODE3, KEYNAMESOUNDEX, STREETNAME4, STREETNUMBER | |||
| PK_SEARCHCONSTITUENT |
ID | |
|
|