SEARCHCONSTITUENT
Primary Key
| Primary Key | Field Type |
|---|---|
| ID | uniqueidentifier |
Fields
| Field | Field Type | Null | Notes | Description |
|---|---|---|---|---|
| CONSTITUENTID | uniqueidentifier | |||
| ADDRESSID | uniqueidentifier | yes | ||
| KEYNAME | nvarchar(100) | Default = '' | ||
| FIRSTNAME | nvarchar(50) | Default = '' | ||
| NAMETYPECODE | tinyint | Default = 0 | ||
| NAMETYPE | nvarchar(16) (Computed) | yes | 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 | |
| COUNTRYID | uniqueidentifier | yes | ||
| POSTCODE | nvarchar(12) | Default = '' | ||
| STREETNUMBER | nvarchar(12) | Default = '' | ||
| STREETNAME | nvarchar(150) | Default = '' | ||
| ISORGANIZATION | bit | Default = 0 | ||
| ISGROUP | bit | Default = 0 | ||
| ALIASID | uniqueidentifier | yes | ||
| KEYNAME4 | nvarchar(4) (Computed) | yes | left(KEYNAME,4) | |
| KEYNAMESOUNDEX | varchar(5) (Computed) | yes | soundex(KEYNAME) | |
| STREETNAME4 | nvarchar(4) (Computed) | yes | left(STREETNAME,4) | |
| STREETNAMESOUNDEX | varchar(5) (Computed) | yes | soundex(STREETNAME) | |
| FIRSTNAME3 | nvarchar(3) (Computed) | yes | left(FIRSTNAME,3) | |
| POSTCODE3 | nvarchar(3) (Computed) | yes | left(POSTCODE,3) | |
| MIDDLENAME | nvarchar(50) | Default = '' | ||
| TITLECODEID | uniqueidentifier | yes | ||
| SUFFIXCODEID | uniqueidentifier | yes |
Indexes
| Index Name | Fields | 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 | yes | yes | yes |