USE [AWLT_3NF]
GO

-- ===================================================================================
-- Mark potential satellite tables
-- ===================================================================================
SELECT	 ca.[CustomerID] 
		,a.[AddressLine1]
		,a.[AddressLine2]
		,a.[City]
		,a.[StateProvince]
		,a.[CountryRegion]
		,a.[PostalCode]
		,a.[rowguid]
		,a.[ModifiedDate]
INTO	[SalesLT].[ShippingAddress]
FROM	[SalesLT].[CustomerAddress] ca
INNER JOIN [SalesLT].[Address] a
	ON	ca.[AddressID] = a.[AddressID]
WHERE	ca.[AddressType] = 'Shipping'
GO

ALTER TABLE [SalesLT].[ShippingAddress] ADD  CONSTRAINT [PK_ShippingAddress_CustomerID] PRIMARY KEY CLUSTERED ([CustomerID] ASC)
GO

 
ALTER TABLE [SalesLT].[ShippingAddress]  WITH CHECK ADD  CONSTRAINT [FK_ShippingAddress_Customer_CustomerID] FOREIGN KEY([CustomerID])
REFERENCES [SalesLT].[Customer] ([CustomerID])
GO

ALTER TABLE [SalesLT].[ShippingAddress] CHECK CONSTRAINT [FK_ShippingAddress_Customer_CustomerID]
GO

-- ===================================================================================
-- Mark as peg leg Links
-- ===================================================================================
SELECT	 p.[ProductID] 
		,p.[ProductNumber] AS [ProductSku]
INTO	[SalesLT].[ProductSku]
FROM	[SalesLT].[Product] p

GO

ALTER TABLE [SalesLT].[ProductSku] ADD  CONSTRAINT [PK_ProductSku_ProductID] PRIMARY KEY CLUSTERED ([ProductID] ASC, [ProductSku] ASC)
GO

ALTER TABLE [SalesLT].[ProductSku]  WITH CHECK ADD  CONSTRAINT [FK_ProductSku_Product_ProductID] FOREIGN KEY([ProductID])
REFERENCES [SalesLT].[Product] ([ProductID])
GO

ALTER TABLE [SalesLT].[Product] CHECK CONSTRAINT [FK_Product_ProductCategory_ProductCategoryID]
GO

DROP VIEW [SalesLT].[vGetAllCategories]
GO

ALTER TABLE [SalesLT].[ProductCategory] DROP CONSTRAINT [FK_ProductCategory_ProductCategory_ParentProductCategoryID_ProductCategoryID]
GO

ALTER TABLE [SalesLT].[ProductCategory] DROP COLUMN [ParentProductCategoryID]
GO
