Database Schema

Configuration Schema

GO
CREATE SCHEMA [Configuration]
GO

Section Table

The section table allows settings to be grouped and categorised.

GO
CREATE TABLE [Configuration].[Section](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[ApplicationName] [nvarchar](50) NOT NULL,
	[Aspect] [nvarchar](50) NULL,
	[Discriminator] [nvarchar](450) NULL,
	[SectionName] [nvarchar](50) NOT NULL,
	[Modified] [datetime2](7) NULL,
	[ModifiedUser] [nvarchar](50) NULL,
	[Timestamp] [timestamp] NOT NULL,
    CONSTRAINT [PK_Section] PRIMARY KEY CLUSTERED 
    (
	    [Id] ASC
    ),
)
GO

Setting Table

The settings table contains key\value for settings. Settings are grouped by SectionId.

GO
CREATE TABLE [Configuration].[Setting](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[DefaultValue] [nvarchar](max) NULL,
	[Key] [nvarchar](50) NOT NULL,
	[Modified] [datetime2](7) NULL,
	[ModifiedUser] [nvarchar](50) NULL,
	[SectionId] [int] NOT NULL,
	[Timestamp] [timestamp] NOT NULL,
	[ValueType] [nvarchar](max) NULL,
	[Json] [nvarchar](max) NULL,
    CONSTRAINT [PK_Setting] PRIMARY KEY CLUSTERED 
    (
	    [Id] ASC
    )
) 
GO

Indexes

GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_Setting_SectionId_Key] ON [Configuration].[Setting]
(
	[SectionId] ASC,
	[Key] ASC
)
WHERE ([SectionId] IS NOT NULL AND [Key] IS NOT NULL)
GO

Constraints

GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_Section_ApplicationName_Aspect_SectionName_Discriminator] ON [Configuration].[Section]
(
	[ApplicationName] ASC,
	[Aspect] ASC,
	[SectionName] ASC,
	[Discriminator] ASC
)
WHERE ([ApplicationName] IS NOT NULL AND [Aspect] IS NOT NULL AND [SectionName] IS NOT NULL AND [Discriminator] IS NULL)
GO
ALTER TABLE [Configuration].[Section] ADD  CONSTRAINT [DF_Section_Modified]  DEFAULT (getdate()) FOR [Modified]
GO
ALTER TABLE [Configuration].[Setting] ADD  CONSTRAINT [DF_Setting_Modified]  DEFAULT (getdate()) FOR [Modified]
GO
ALTER TABLE [Configuration].[Setting]  WITH CHECK ADD  CONSTRAINT [FK_Setting_Section_SectionId] FOREIGN KEY([SectionId])
REFERENCES [Configuration].[Section] ([Id])
ON DELETE CASCADE
GO
ALTER TABLE [Configuration].[Setting] CHECK CONSTRAINT [FK_Setting_Section_SectionId]
GO

Diagram