Summary Table

Categories Total Count
PII 0
URL 0
DNS 0
EKL 0
IP 0
PORT 0
VsID 0
CF 0
AI 0
VPD 0
PL 0
Other 0

File Content

?--TODO: This table isn't normalized
CREATE TABLE dbo.FBCSUBStatuses
(
--authoritative source: A06.CDWWork.FBCS.UB92Status
--fields
FBCSUBStatusID bigint IDENTITY(1,1) NOT NULL,
FBCSUBClaimID bigint,
StationID smallint NOT NULL, --EPRS assigned (FK), relates to Stations
FBCSUserID int, --EPRS assigned (FK), relates to FBCSUsers
DateCreated datetime2(7), --dtstamp
Reason VARCHAR(25), --Reason, TODO: This field will need to be set to only take the LEFT 25 characters
[Status] varchar(100), --Status, TODO: This field will need to be set to only take the LEFT 100 characters
IsDistributed bit NULL, --distribution, Needs to be tri-state bit, because souurce has nulls


--primary key
CONSTRAINT PK_FBCSUBStatuses PRIMARY KEY CLUSTERED
(
FBCSUBStatusID ASC
)
)
ON CoreData
--when using clustered columnstore index, can't set data compression to PAGE
WITH (DATA_COMPRESSION = PAGE)
GO

/*
(<station__no, varchar(30),> --StationID
,<ub92id, bigint,> --FBCSUBStatusNumber
,<id, bigint,> --CDWUBStatusNumber
,<userid, bigint,> --FBCSUBUserID
,<dtstamp, datetime2(7),> --DTStamp
,<status, varchar(800),> --Status
,<reason, varchar(8000),> --Reason
,<distribution, int,> --IsDistributed
,<thread__id, numeric(18,0),>)
*/



--foreign keys

--indexes

--constraints

--Metadata descriptions for each field
EXECUTE sp_addextendedproperty
N'MS_Description', N'Primary Key, unique to EPRS; authoritative source table is A06.CDWWork.FBCS.ub92Statuses',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBStatuses',
N'COLUMN', N'FBCSUBStatusID'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to Stations table, source field: station__no + facility',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBStatuses',
N'COLUMN', N'StationID'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, relates to FBCS Users table, EPRS value, source field: verifiedby',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBStatuses',
N'COLUMN', N'FBCSUserID'
GO

--TODO: Missing some metadata dictionary entries