|
|
-- ITD Transport Tracking - SQL Server schema
|
|
|
-- Таблици: карти (QR), цикли с 5 поста (дата-час за всеки пост) + служебно затваряне
|
|
|
|
|
|
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'ITD_Cards')
|
|
|
BEGIN
|
|
|
CREATE TABLE dbo.ITD_Cards (
|
|
|
Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
|
|
|
Code NVARCHAR(128) NOT NULL UNIQUE,
|
|
|
DisplayText NVARCHAR(256) NOT NULL,
|
|
|
IsActive BIT NOT NULL DEFAULT 1,
|
|
|
CreatedAt DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
|
|
|
UpdatedAt DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
|
|
|
);
|
|
|
CREATE INDEX IX_ITD_Cards_Code ON dbo.ITD_Cards(Code);
|
|
|
CREATE INDEX IX_ITD_Cards_IsActive ON dbo.ITD_Cards(IsActive);
|
|
|
END
|
|
|
GO
|
|
|
|
|
|
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'ITD_Cycles')
|
|
|
BEGIN
|
|
|
CREATE TABLE dbo.ITD_Cycles (
|
|
|
Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
|
|
|
CardId INT NOT NULL,
|
|
|
CycleNo INT NOT NULL,
|
|
|
Post1At DATETIME2 NOT NULL,
|
|
|
Post2At DATETIME2 NULL,
|
|
|
Post3At DATETIME2 NULL,
|
|
|
Post4At DATETIME2 NULL,
|
|
|
Post5At DATETIME2 NULL,
|
|
|
ServiceClosed BIT NOT NULL DEFAULT 0,
|
|
|
ServiceClosedAt DATETIME2 NULL,
|
|
|
CONSTRAINT FK_ITD_Cycles_Cards FOREIGN KEY (CardId) REFERENCES dbo.ITD_Cards(Id)
|
|
|
);
|
|
|
CREATE INDEX IX_ITD_Cycles_CardId ON dbo.ITD_Cycles(CardId);
|
|
|
CREATE INDEX IX_ITD_Cycles_Post1At ON dbo.ITD_Cycles(Post1At);
|
|
|
END
|
|
|
GO
|