You are a database architect tasked with designing a database structure based on the following module, function, user stories, and screen layout:
Module: {module_name}
Function: {function_name}
User Stories:
{user_stories}
Screen Layout:
{ascii_markdown}
Please analyze the screen layout and user stories to generate SQL table definitions. For each table, provide the complete SQL script including:
1. CREATE TABLE statement with all columns
2. Primary key constraint
3. Foreign key constraints
4. Indexes
5. Required audit fields
Your response should use the following XML tag structure for each table:
CREATE TABLE [dbo].[Users] (
[UserID] UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWSEQUENTIALID(),
[Username] NVARCHAR(100) NOT NULL,
[Email] NVARCHAR(255) NOT NULL,
[PasswordHash] NVARCHAR(MAX) NOT NULL,
[OrganizationID] UNIQUEIDENTIFIER NOT NULL,
[BranchID] UNIQUEIDENTIFIER NOT NULL,
[CreatedBy] UNIQUEIDENTIFIER NOT NULL,
[CreatedAt] DATETIME2(7) NOT NULL DEFAULT GETUTCDATE(),
[UpdatedBy] UNIQUEIDENTIFIER NULL,
[UpdatedAt] DATETIME2(7) NULL,
[IsActive] BIT NOT NULL DEFAULT 1,
[IsDeleted] BIT NOT NULL DEFAULT 0,
CONSTRAINT [FK_Users_Organizations] FOREIGN KEY ([OrganizationID])
REFERENCES [dbo].[Organizations] ([OrganizationID]),
CONSTRAINT [FK_Users_Branches] FOREIGN KEY ([BranchID])
REFERENCES [dbo].[Branches] ([BranchID])
);
CREATE UNIQUE NONCLUSTERED INDEX [IX_Users_Username]
ON [dbo].[Users] ([Username])
WHERE [IsDeleted] = 0;
CREATE NONCLUSTERED INDEX [IX_Users_Email]
ON [dbo].[Users] ([Email])
WHERE [IsDeleted] = 0;
CREATE NONCLUSTERED INDEX [IX_Users_OrgBranch]
ON [dbo].[Users] ([OrganizationID], [BranchID])
INCLUDE ([Username], [Email], [IsActive]);
CREATE TABLE [dbo].[UserProfiles] (
[UserProfileID] UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWSEQUENTIALID(),
[UserID] UNIQUEIDENTIFIER NOT NULL,
[FirstName] NVARCHAR(100) NOT NULL,
[LastName] NVARCHAR(100) NOT NULL,
[PhoneNumber] NVARCHAR(20) NULL,
[Address] NVARCHAR(500) NULL,
[OrganizationID] UNIQUEIDENTIFIER NOT NULL,
[BranchID] UNIQUEIDENTIFIER NOT NULL,
[CreatedBy] UNIQUEIDENTIFIER NOT NULL,
[CreatedAt] DATETIME2(7) NOT NULL DEFAULT GETUTCDATE(),
[UpdatedBy] UNIQUEIDENTIFIER NULL,
[UpdatedAt] DATETIME2(7) NULL,
[IsActive] BIT NOT NULL DEFAULT 1,
[IsDeleted] BIT NOT NULL DEFAULT 0,
CONSTRAINT [FK_UserProfiles_Users] FOREIGN KEY ([UserID])
REFERENCES [dbo].[Users] ([UserID]),
CONSTRAINT [FK_UserProfiles_Organizations] FOREIGN KEY ([OrganizationID])
REFERENCES [dbo].[Organizations] ([OrganizationID]),
CONSTRAINT [FK_UserProfiles_Branches] FOREIGN KEY ([BranchID])
REFERENCES [dbo].[Branches] ([BranchID])
);
CREATE NONCLUSTERED INDEX [IX_UserProfiles_Names]
ON [dbo].[UserProfiles] ([FirstName], [LastName])
WHERE [IsDeleted] = 0;
CREATE NONCLUSTERED INDEX [IX_UserProfiles_OrgBranch]
ON [dbo].[UserProfiles] ([OrganizationID], [BranchID])
INCLUDE ([FirstName], [LastName], [IsActive]);
Requirements:
1. Required Audit Fields for EVERY table:
- [OrganizationID] UNIQUEIDENTIFIER NOT NULL
- [BranchID] UNIQUEIDENTIFIER NOT NULL
- [CreatedBy] UNIQUEIDENTIFIER NOT NULL
- [CreatedAt] DATETIME2(7) NOT NULL DEFAULT GETUTCDATE()
- [UpdatedBy] UNIQUEIDENTIFIER NULL
- [UpdatedAt] DATETIME2(7) NULL
- [IsActive] BIT NOT NULL DEFAULT 1
- [IsDeleted] BIT NOT NULL DEFAULT 0
2. Primary Keys:
- Use UNIQUEIDENTIFIER with NEWSEQUENTIALID() default
- Name as TableNameID
- Always clustered primary key
3. Foreign Keys:
- Include OrganizationID and BranchID references
- Name as FK_TableName_ReferencedTable
- Include all necessary relationships
4. Indexes:
- Create for all foreign keys
- Add filtered indexes where appropriate
- Include covering indexes for common queries
- Name as IX_TableName_Columns
5. Naming Conventions:
- Use PascalCase for table names
- Use PascalCase for column names
- Use underscores in constraint names
- Prefix indexes with IX_
- Prefix foreign keys with FK_
6. Data Types:
- Use UNIQUEIDENTIFIER for IDs
- Use NVARCHAR for text
- Use DATETIME2(7) for dates
- Use appropriate types for other data
7. Multi-tenancy:
- Include OrganizationID and BranchID
- Add appropriate foreign keys
- Create filtered indexes
IMPORTANT:
- Include all audit fields
- Add appropriate indexes
- Use consistent naming
- Follow SQL Server conventions
- Include all constraints
- Handle relationships properly
- Consider performance
- Match screen requirements
- Support user stories
Your response should contain multiple blocks, each with its complete SQL script. The SQL should be valid T-SQL that can be executed directly in SQL Server.