| Primary Key | Field Type |
|---|---|
 ID |
uniqueidentifier |
| Field | Field Type | Null | Notes | Description |
|---|---|---|---|---|
 NAME |
nvarchar(100) | Default = '' | A name for the segment. | |
| DESCRIPTION |
nvarchar(255) | Default = '' | A description of the segment. | |
| CODE |
nvarchar(10) | Default = '' | A code identifying the segment. | |
| DATEREFRESHED |
datetime |  |
The date the segment was last refreshed. | |
| DATEADDED |
datetime | Default = getdate() | Indicates the date this record was added. | |
| DATECHANGED |
datetime | Default = getdate() | Indicates the date this record was last changed. | |
| TS |
timestamp | Timestamp. | ||
 TSLONG |
bigint (Computed) | |
CONVERT(bigint, TS) | Numeric representation of the timestamp. |
| SEGMENTTYPECODE |
tinyint | Default = 1 | The type of the segment. | |
| SEGMENTTYPE |
nvarchar(18) (Computed) | |
CASE [SEGMENTTYPECODE] WHEN 1 THEN N'Constituent' WHEN 2 THEN N'List' WHEN 3 THEN N'Revenue' WHEN 4 THEN N'Membership' WHEN 5 THEN N'Sponsorship' WHEN 6 THEN N'Media outlet' WHEN 7 THEN N'Time slot' WHEN 8 THEN N'Marketing location' WHEN 9 THEN N'White mail' END | Provides a translation for the 'SEGMENTTYPECODE' field. |
| SEGMENTFAMILYTYPECODE |
tinyint (Computed) | |
convert(tinyint, case when [SEGMENTTYPECODE] in (6, 7, 8) then 2 when [SEGMENTTYPECODE] = 9 then 3 else 1 end) | Indicates whether the segment is direct marketing or public media. |
| SEGMENTFAMILYTYPE |
varchar(23) (Computed) | case when [SEGMENTTYPECODE] in (6, 7, 8) then 'Public media effort' when [SEGMENTTYPECODE] = 9 then 'White mail' else 'Direct marketing effort' end | Indicates whether the segment is direct marketing or public media. | |
| ISSYSTEM |
bit | Default = 0 | Flag used to hide segments meant for system use only. | |
| ISHISTORICAL |
bit | Default = 0 | ||
| HISTORICALQUANTITY |
int | Default = 0 |
| Foreign Key | Field Type | Null | Notes | Description |
|---|---|---|---|---|
 QUERYVIEWCATALOGID |
uniqueidentifier | QUERYVIEWCATALOG.ID | The donor query view for the record source on which the segment is based. | |
| IDSETREGISTERID |
uniqueidentifier | |
IDSETREGISTER.ID | The ID set for the segment. |
| ADDEDBYID |
uniqueidentifier | CHANGEAGENT.ID | FK to CHANGEAGENT. | |
| CHANGEDBYID |
uniqueidentifier | CHANGEAGENT.ID | FK to CHANGEAGENT. | |
| SEGMENTCATEGORYCODEID |
uniqueidentifier | |
MKTSEGMENTCATEGORYCODE.ID | A category for grouping segments together. |
| CURRENTSEGMENTLISTID |
uniqueidentifier | |
MKTSEGMENTLIST.ID | The current record for a list segment. |
| PARTDEFINITIONVALUESID |
uniqueidentifier | |
MKTSOURCECODEPARTDEFINITIONVALUES.ID | FK to MKTSOURCECODEPARTDEFINITIONVALUES |
| BASECURRENCYID |
uniqueidentifier | |
CURRENCY.ID | The base currency associated with this segment. |
| SITEID |
uniqueidentifier | |
SITE.ID |
| Index Name | Field(s) | Unique | Primary | Clustered |
|---|---|---|---|---|
 IX_MKTSEGMENT_BASECURRENCYID |
BASECURRENCYID | |||
 IX_MKTSEGMENT_DATEADDED |
DATEADDED | |
||
| IX_MKTSEGMENT_DATECHANGED |
DATECHANGED | |||
| IX_MKTSEGMENT_PARTDEFINITIONVALUESID |
PARTDEFINITIONVALUESID | |||
| IX_MKTSEGMENT_QUERYVIEWCATALOGID |
QUERYVIEWCATALOGID | |||
| IX_MKTSEGMENT_SEGMENTCATEGORYCODEID |
SEGMENTCATEGORYCODEID | |||
| PK_MKTSEGMENT |
ID | |
|
|
| UC_MKTSEGMENT_NAME |
NAME | |
||
| UIX_MKTSEGMENT_CURRENTSEGMENTLISTID |
CURRENTSEGMENTLISTID | |
||
| UIX_MKTSEGMENT_ID |
ID | |
||
| UIX_MKTSEGMENT_IDSETREGISTERID |
IDSETREGISTERID | |
| Trigger Name | Description |
|---|---|
 TR_MKTSEGMENT_IU_BASECURRENCYID |
|
| TR_MKTSEGMENT_AUDIT_UPDATE |
|
| TR_MKTSEGMENT_AUDIT_DELETE |
| Referenced by | Field |
|---|---|
 APPEALMAILINGSETUP |
SEGMENTID |
| APPEALMAILINGSETUPLETTER |
MAILSEGMENTID |
| APPEALMAILINGSETUPLETTER |
EMAILSEGMENTID |
| BATCHDIRECTMARKETINGEFFORTSEGMENT |
MKTSEGMENTID |
| COMMUNICATIONLETTER |
MAILSEGMENTID |
| COMMUNICATIONLETTER |
EMAILSEGMENTID |
| MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE |
SEGMENTID |
| MKTGROUPSEGMENTS |
SEGMENTID |
| MKTMEMBERSHIPMAILINGTEMPLATERULE |
SEGMENTID |
| MKTSEGMENTATIONSEGMENT |
SEGMENTID |
| MKTSEGMENTLIST |
SEGMENTID |
| MKTSEGMENTLIST |
PARENTSEGMENTID |
| MKTSEGMENTPASSIVE |
ID |
| MKTSEGMENTPASSIVE |
PARENTMEDIAOUTLETSEGMENTID |
| MKTSEGMENTREFRESHPROCESS |
SEGMENTID |
| MKTSEGMENTSELECTION |
SEGMENTID |
| MKTSEGMENTWHITEMAIL |
ID |
| MKTSPONSORSHIPMAILINGTEMPLATEACTIVATED |
MKTSEGMENTID |
| MKTSPONSORSHIPMAILINGTEMPLATERULE |
SEGMENTID |
| REVENUELETTERMARKETING |
MKTSEGMENTID |
| REVENUERECEIPTMARKETING |
MKTSEGMENTID |
Entity-Relationship diagram of this table (MKTSPONSORSHIPMAILINGTEMPLATEACTIVATED-SITE)