CREATE TABLE [dbo].[ATFieldDefinition] (
    [FieldID]      INT              NOT NULL IDENTITY(1,1),
    [ProcessID]    INT              NOT NULL,
    [Index]        INT              NULL,
    [FieldName]    NVARCHAR (64)    NOT NULL,
    [Alias]        NVARCHAR (64)    NULL,
    [DataType]     TINYINT          NOT NULL DEFAULT 5,
    [InitialValue] NVARCHAR (MAX)   NULL,
    [Locked]       BIT              NOT NULL DEFAULT 0,
    [Display]      BIT              CONSTRAINT [DF_ATFieldDefinition_Display] DEFAULT 1 NOT NULL,
    [ObjectId]     UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
    CONSTRAINT [PK_FieldDefinition] PRIMARY KEY NONCLUSTERED ([FieldID] ASC),
    CONSTRAINT [UQ_ATFieldDefinition_FieldName_ProcessID] UNIQUE ([FieldName], [ProcessID]),
    CONSTRAINT [ATFieldDefinition_ProcessDefinition] FOREIGN KEY ([ProcessID]) REFERENCES [dbo].[ATProcessDefinition] ([ProcessID]),
    CONSTRAINT CK_ATFieldDefinition_DataType_AllowedValues CHECK ([DataType] IN (1, /*Number*/ 5 /*String*/, 6 /*Date*/, 7 /*Option*/)),
    CONSTRAINT CK_ATFieldDefinition_Option_InitialValue CHECK ([DataType] <> 7 OR [InitialValue] IS NOT NULL)
);


GO
CREATE NONCLUSTERED INDEX [ATFieldDefinition_IX_ProcessId]
    ON [dbo].[ATFieldDefinition]([ProcessID] ASC);


GO
CREATE NONCLUSTERED INDEX [ATFieldDefinition_ObjectId]
    ON [dbo].[ATFieldDefinition]([ObjectId] ASC);

