Skip to content

Instantly share code, notes, and snippets.

@dataneek
Created November 23, 2015 22:36
Show Gist options
  • Save dataneek/583526abb588124cbfdf to your computer and use it in GitHub Desktop.
Save dataneek/583526abb588124cbfdf to your computer and use it in GitHub Desktop.
Baseline schema to create AuthenticationService
CREATE TABLE [Account].[Client] (
[ClientId] BIGINT IDENTITY (1, 1) NOT NULL,
[ClientKey] UNIQUEIDENTIFIER NOT NULL,
[CommonName] NVARCHAR (50) NOT NULL,
[TeamName] NVARCHAR (50) NOT NULL,
[EmailDomainName] NVARCHAR (50) NULL,
[IsArchived] BIT CONSTRAINT [DF_Account.Client_IsArchived] DEFAULT ((0)) NOT NULL,
[Created] DATETIMEOFFSET (7) CONSTRAINT [DF_Account.Client_Created] DEFAULT (sysdatetimeoffset()) NOT NULL,
[LastUpdated] DATETIMEOFFSET (7) NULL,
[Watermark] ROWVERSION NOT NULL,
CONSTRAINT [PK_Account.Client] PRIMARY KEY CLUSTERED ([ClientId] ASC)
);
CREATE TABLE [Account].[LocalAccount] (
[LocalAccountId] BIGINT IDENTITY (1, 1) NOT NULL,
[LocalAccountKey] UNIQUEIDENTIFIER NOT NULL,
[ClientId] BIGINT NULL,
[UserName] NVARCHAR (255) NOT NULL,
[FirstName] NVARCHAR (50) NULL,
[LastName] NVARCHAR (50) NULL,
[Email] NVARCHAR (255) NULL,
[EmailConfirmed] BIT CONSTRAINT [DF_Account.LocalAccount_EmailConfirmed] DEFAULT ((0)) NOT NULL,
[HashedPassword] NVARCHAR (100) NULL,
[SecurityStamp] NVARCHAR (100) NULL,
[PhoneNumber] NVARCHAR (25) NULL,
[PhoneNumberConfirmed] BIT CONSTRAINT [DF_Account.LocalAccount_PhoneNumberConfirmed] DEFAULT ((0)) NOT NULL,
[IsTwoFactorEnabled] BIT CONSTRAINT [DF_Account.LocalAccount_IsTwoFactorEnabled] DEFAULT ((0)) NOT NULL,
[HashedTwoFactorAuthCode] NVARCHAR (100) NULL,
[DateTwoFactorAuthCodeIssued] DATETIME NULL,
[LockoutEnd] DATETIMEOFFSET (7) NULL,
[LockoutEnabled] BIT CONSTRAINT [DF_Account.LocalAccount_LockoutEnabled] DEFAULT ((0)) NOT NULL,
[AccessFailedCount] INT NOT NULL,
[IsTeamLeader] BIT NOT NULL,
[IsArchived] BIT CONSTRAINT [DF_Account.LocalAccount_IsArchived] DEFAULT ((0)) NOT NULL,
[Created] DATETIMEOFFSET (7) CONSTRAINT [DF_Account.LocalAccount_Created] DEFAULT (sysdatetimeoffset()) NOT NULL,
[LastUpdated] DATETIMEOFFSET (7) NULL,
[Watermark] ROWVERSION NOT NULL,
CONSTRAINT [PK_Account.LocalAccount] PRIMARY KEY CLUSTERED ([LocalAccountId] ASC),
CONSTRAINT [FK_Account.LocalAccount_Client] FOREIGN KEY ([ClientId]) REFERENCES [Account].[Client] ([ClientId])
);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment