Below are the scripts to create the tables i have so far.
The narrative is an employee goes out to visit an HFM's
the DDHistory table is a record of visits.
Each HFMDetial has a location and strategy
the HFMs have one or more service_Providers for each and every servProvType.
At every visit a record is made of all the properties in the HFM table.
There are also 3 Fasb_levels and these levels can change at any visit and we need to have a record of the all the changes for each visit.
Also the employee who visits, writes or manages can change at any visit so we need a history of that also. the Service providers can also change at any visit so we need to have a record of changes and dates.

I am just trying to design the tables and relations now. All the data is in 1 table now with one record for each visit. The service providerTypes are the only table that has data at this time.

One concern of mine is keeping the select statements simple enough and mostly the UPDATE statements. I dont have much dedicated SQL time so this is the 1st design work I have done in 4 years!!!. Selects Im great at but the updates in a date model like this are intimidating


USE [Pdb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Service_Providers](
[ID] [int] NOT NULL,
[idHf] [int] NULL,
[ProviderType] [int] NULL,
[ProviderNo] [int] NULL,
[Date] [datetime] NULL
) ON [PRIMARY]

USE [Pdb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ServiceProviderType](
[id] [int] IDENTITY(1,1) NOT NULL,
[Type] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_ServiceProviderType] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

USE Pdb
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[HFLocation](
[id] [int] NOT NULL,
[idHFM] [int] NOT NULL,
[Location] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AdminLocation] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_HFLocation] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[HFLocation] WITH CHECK ADD CONSTRAINT [FK_HFLocation_HFMDetail] FOREIGN KEY([idHFM])
REFERENCES [dbo].[HFMDetail] ([Id])
GO
ALTER TABLE [dbo].[HFLocation] CHECK CONSTRAINT [FK_HFLocation_HFMDetail]


USE Pdb
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Strategy](
[StratNo] [int] NOT NULL,
[StrategyName] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Status] [bit] NULL,
CONSTRAINT [PK_Strategy] PRIMARY KEY CLUSTERED
(
[StratNo] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]



USE Pdb
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[HFMDetail](
[Id] [int] IDENTITY(1,1) NOT NULL,
[idStrat] [int] NOT NULL,
[idHf] [int] NOT NULL,
[PassFail] [bit] NOT NULL,
[Redeem] [int] NOT NULL,
[NewFund] [int] NOT NULL,
[TransRpt] [bit] NOT NULL,
[NavLite] [bit] NOT NULL,
[InHouseBD] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TradeWithBD] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SelfCustody] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[GateSusp2008] [bit] NOT NULL,
[ValComm] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BestExe] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NoMngdAccts] [int] NOT NULL,
[MinAmt] [decimal](20, 2) NULL,
[AuditorPrefAUP] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SAS70] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LuanchDate] [datetime] NULL,
CONSTRAINT [PK_HFMDetail] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[HFMDetail] WITH CHECK ADD CONSTRAINT [FK_HFMDetail_Strategy] FOREIGN KEY([idStrat])
REFERENCES [dbo].[Strategy] ([StratNo])
GO
ALTER TABLE [dbo].[HFMDetail] CHECK CONSTRAINT [FK_HFMDetail_Strategy]


USE Pdb
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DDHistory](
[id] [int] IDENTITY(1,1) NOT NULL,
[idHFM] [int] NULL,
[Documented] [int] NULL,
[VisitedBy] [int] NOT NULL,
[ToBeWrittenBy] [int] NOT NULL,
[Mngr] [int] NOT NULL,
[NoOpsStaff] [int] NULL,
[PctNavInSidePocket] [decimal](3, 3) NULL,
[LastReview] [datetime] NOT NULL,
CONSTRAINT [PK_DDHistory] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[DDHistory] WITH CHECK ADD CONSTRAINT [FK_DDHistory_EmplVisted] FOREIGN KEY([VisitedBy])
REFERENCES [dbo].[Employees] ([id])
GO
ALTER TABLE [dbo].[DDHistory] CHECK CONSTRAINT [FK_DDHistory_EmplVisted]
GO
ALTER TABLE [dbo].[DDHistory] WITH CHECK ADD CONSTRAINT [FK_DDHistory_EmpMngr] FOREIGN KEY([Mngr])
REFERENCES [dbo].[Employees] ([id])
GO
ALTER TABLE [dbo].[DDHistory] CHECK CONSTRAINT [FK_DDHistory_EmpMngr]
GO
ALTER TABLE [dbo].[DDHistory] WITH CHECK ADD CONSTRAINT [FK_DDHistory_EmpWritten] FOREIGN KEY([ToBeWrittenBy])
REFERENCES [dbo].[Employees] ([id])
GO
ALTER TABLE [dbo].[DDHistory] CHECK CONSTRAINT [FK_DDHistory_EmpWritten]
GO
ALTER TABLE [dbo].[DDHistory] WITH CHECK ADD CONSTRAINT [FK_DDHistory_HFMDetail] FOREIGN KEY([idHFM])
REFERENCES [dbo].[HFMDetail] ([Id])
GO
ALTER TABLE [dbo].[DDHistory] CHECK CONSTRAINT [FK_DDHistory_HFMDetail]


USE Pdb
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ServiceProviderType](
[id] [int] IDENTITY(1,1) NOT NULL,
[Type] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_ServiceProviderType] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF


USE Pdb
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[FASBlvl](
[idDDHist] [int] NOT NULL,
[Date] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[L1] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[L2] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[L3] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_FASBlvl_1] PRIMARY KEY CLUSTERED
(
[idDDHist] ASC,
[Date] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[FASBlvl] WITH CHECK ADD CONSTRAINT [FK_FASBlvl_DDHistory1] FOREIGN KEY([idDDHist])
REFERENCES [dbo].[DDHistory] ([id])
GO
ALTER TABLE [dbo].[FASBlvl] CHECK CONSTRAINT [FK_FASBlvl_DDHistory1]


USE Pdb
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Employees](
[id] [int] NOT NULL,
[Employee] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[FName] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LName] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF