USE GKHELPDESK;
GO

SET NOCOUNT ON;

-- Create Technician and Site Tables
--how would i represent multiple phone numbers for a technician and site manager in a flexible table   
CREATE TABLE dbo.Technician
   (
   TechId NVARCHAR(8) PRIMARY KEY NOT NULL
   ,TechFirstName NVARCHAR (50) NOT NULL
   ,TechLastName NVARCHAR (50) NOT NULL
   ,TechTitle NVARCHAR (50) NOT NULL
   ,TechModifiedDate DATE NOT NULL 
   );
/* Phone type column will have a foreign key relationship to a type table to indicate the contact type. Such as a technician, site manager or future categories
*/
CREATE TABLE dbo.PhoneNumbers
	(
	PhoneID int Primary Key NOT NULL
	,PhoneCountryCode NVARCHAR(3) NULL --Covers country codes if not in U.S.
	,PhonePrefix1 NVARCHAR(4) NOT NULL --Covers NPA and City/Area Codes
	,PhonePrefix2 NVARCHAR(4) NOT NULL --Covers Switch Prefix
	,PhoneNumber NVARCHAR(15) NOT NULL --Actual line number or remaining numbers
	,PhoneExtension NVARCHAR(10) NULL --Phone extension to a PBX if applicable
	,PhoneContactType int NOT NULL --ie, site manager, technician, future types
	,PhoneContactID NVARCHAR (8) NOT NULL -- will be the parent to the id of the respective contact
	,
	);
--Phone type table for the PhoneNumbers table FK relationship to determine the user type, ie tech
CREATE TABLE dbo.PhoneType
	(
	PhoneTypeID int Primary Key Not NUll
	,PhoneTypeDescription NVARCHAR (30) Not Null --ie, Technician, Site, Future Uses...
	);
--Phone contact table to relate the phone number to the contact
CREATE TABLE dbo.PhoneContact
	(
	PhoneContactId NVARCHAR (8) PRIMARY KEY NOT NULL
	,PhoneId int NOT NULL
	);
CREATE TABLE dbo.TechnicianContact
	(
	TechID NVARCHAR (8) PRIMARY KEY NOT NULL
	,TechAddressLine1 NVARCHAR (60)NOT NULL
	,TechAddressLine2 NVARCHAR (60)NULL
	,TechCity NVARCHAR (30)NOT NULL
	,TechStateProvinceID INT NOT NULL
	,TechContactModifiedDate Date NOT NULL
	,TechPhoneID int NOT NULL
	);
CREATE TABLE dbo.Site
   (
   SiteID NVARCHAR (8) PRIMARY KEY NOT NULL
   ,SiteName NVARCHAR (25) NOT NULL
   ,SiteAddressLine1 NVARCHAR (60) NOT NULL
   ,SiteAddressLine2 NVARCHAR (60) NOT NULL
   ,SiteCity NVARCHAR (30) NOT NULL
   ,SiteStateProvinceID INT NOT NULL
   ,SiteModifiedDate DATE NOT NULL
   ,SiteManagerName NVARCHAR(60) NOT NULL
   ,SitePhoneNumber int not null
   );
GO

We walk in the dark places no others will enter
We stand on the bridge and no one may pass