2
\$\begingroup\$

This is intended for a web-based application that allows someone to register visitors, assign their hosts, and indicate the dates in which they will be visiting.

Imagine this scenario:

I want to register(Requester_Employee_Id) some vendors(Guest_Type), Mary and Bob (Visitor) for a visit. Their hosts will be Employee1 and Employee2 (Host). Mary and Bob will be visiting on 2/19 11am-4pm and 2/20 10am-2pm (Visit)

Each day when they arrive, I want to 'check them in' (Attendance)

DB Diagram

Attendance

CREATE TABLE [dbo].[Attendance](
    [Visitor_Id] [int] NOT NULL,
    [Visit_Id] [int] NOT NULL,
    [Checked_In_UTC] [datetime2](0) NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Attendance]  WITH CHECK ADD  CONSTRAINT [FK_Attendance_Visit] FOREIGN KEY([Visit_Id])
REFERENCES [dbo].[Visit] ([Id])
GO

ALTER TABLE [dbo].[Attendance] CHECK CONSTRAINT [FK_Attendance_Visit]
GO

ALTER TABLE [dbo].[Attendance]  WITH CHECK ADD  CONSTRAINT [FK_Attendance_Vistor] FOREIGN KEY([Visitor_Id])
REFERENCES [dbo].[Vistor] ([Id])
GO

ALTER TABLE [dbo].[Attendance] CHECK CONSTRAINT [FK_Attendance_Vistor]
GO

Guest_Type

CREATE TABLE [dbo].[Guest_Type](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](25) NULL,
    [Description] [nvarchar](200) NOT NULL,
 CONSTRAINT [PK_Guest_Type] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Host

CREATE TABLE [dbo].[Host](
    [Registration_Id] [int] NULL,
    [Employee_Id] [uniqueidentifier] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Host]  WITH CHECK ADD  CONSTRAINT [FK_Host_Registration] FOREIGN KEY([Registration_Id])
REFERENCES [dbo].[Registration] ([Id])
GO

ALTER TABLE [dbo].[Host] CHECK CONSTRAINT [FK_Host_Registration]
GO

Registration

CREATE TABLE [dbo].[Registration](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Guest_Type_Id] [int] NOT NULL,
    [Mode_of_Transportation] [nvarchar](200) NULL,
    [Requester_Employee_Id] [uniqueidentifier] NOT NULL,
    [Removal_Date_UTC] [datetime2](0) NULL,
 CONSTRAINT [PK_Registration] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Registration]  WITH CHECK ADD  CONSTRAINT [FK_Registration_Guest_Type] FOREIGN KEY([Guest_Type_Id])
REFERENCES [dbo].[Guest_Type] ([Id])
GO

ALTER TABLE [dbo].[Registration] CHECK CONSTRAINT [FK_Registration_Guest_Type]
GO

Visit

CREATE TABLE [dbo].[Visit](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Registration_Id] [int] NOT NULL,
    [Start_UTC] [datetime2](0) NOT NULL,
    [End_UTC] [datetime2](0) NOT NULL,
 CONSTRAINT [PK_Visit] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Visit]  WITH CHECK ADD  CONSTRAINT [FK_Visit_Registration] FOREIGN KEY([Registration_Id])
REFERENCES [dbo].[Registration] ([Id])
GO

ALTER TABLE [dbo].[Visit] CHECK CONSTRAINT [FK_Visit_Registration]
GO

Visitor

CREATE TABLE [dbo].[Vistor](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Registration_Id] [int] NOT NULL,
    [First_Name] [nvarchar](25) NOT NULL,
    [Last_Name] [nvarchar](25) NOT NULL,
    [Email] [nvarchar](50) NULL,
    [Notes] [nvarchar](200) NULL,
 CONSTRAINT [PK_Vistor] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Vistor]  WITH CHECK ADD  CONSTRAINT [FK_Vistor_Registration] FOREIGN KEY([Registration_Id])
REFERENCES [dbo].[Registration] ([Id])
GO

ALTER TABLE [dbo].[Vistor] CHECK CONSTRAINT [FK_Vistor_Registration]
GO

Now I've never designed a db from scratch before so I'd appreciate you're help.

Most of the datatypes I chose were based on other similar fields from other projects I've poked around in before but I'm open to all criticism. There are a few things I think I should do, but I'm not quite sure how to yet. For instance, the Host table will have unique value combinations. I think the db can enforce this?

For reference, entering a request kicks off a massive internal approval system which cannot be edited. For that reason, if any changes need to made to a registration, it will be deleted (Removal_Date_UTC) and a new request will be created.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.