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