Please review Database scripts

• Ed7

  SSCrazy Eights

  Points: 8519

  More actions

  September 15, 2009 at 1:43 pm

  #204683

  Hello Everyone,

  I planned to convert Microsoft Excel spreadsheet (Ticketing Systems) into C# application and utilize MS SQL Server 2008.

  Enclosed is my database scripts:

  -- Create Operators Table

  CREATE TABLE Operator (

  OperatorID CHAR(10) NOT NULL PRIMARY KEY CLUSTERED,

  OpsFirstName CHAR(35) NOT NULL,

  OpsMiddleName CHAR(2) SPARSE NULL,

  OpsLastName CHAR(35) NOT NULL,

  CompanyName CHAR(75) SPARSE NULL,

  OfficePhone VARCHAR(20) SPARSE NULL,

  MobilePhone VARCHAR (20)SPARSE NULL );

  GO

  --Create Caller Table

  CREATE TABLE Caller (

  CallerID CHAR(10) NOT NULL PRIMARY KEY CLUSTERED,

  SiteID CHAR(10) NOT NULL,

  CallerFirstName CHAR(35) NOT NULL,

  CallerMiddleName CHAR(2) sparse NULL,

  CallerLastName CHAR(35) NOT NULL,

  OfficePhone VARCHAR(20) SPARSE NULL,

  MobilePhone VARCHAR(20) sparse NULL);

  GO

  --Create Site Table

  CREATE TABLE Site (

  SiteID CHAR(10) NOT NULL PRIMARY KEY CLUSTERED,

  SiteName CHAR (100) NOT NULL,

  TicketID VARCHAR(10) NOT NULL);

  GO

  -- Create Crew Table

  CREATE TABLE crew (

  CrewID CHAR(10) NOT NULL PRIMARY KEY CLUSTERED,

  FirstName CHAR(35) NOT NULL,

  MiddleName CHAR(2) SPARSE NULL,

  LastName CHAR(35) NOT NULL,

  OfficePhone VARCHAR(20) SPARSE NULL,

  MobilePhone VARCHAR(20) SPARSE NULL)

  GO

  -- Create Turbine Table

  CREATE TABLE Turbine (

  TurbineID VARCHAR(10) NOT NULL PRIMARY KEY CLUSTERED,

  TurbineQuantity SMALLINT NOT NULL,

  TurbineStatus CHAR(100) NOT NULL,

  ClearanceNumberStopTime DATETIME2 NOT NULL,

  ClearanceNumberStartTime DATETIME2 NOT NULL,

  ClearanceNumberReleaseNotes VARCHAR(5) NOT NULL);

  GO

  -- Create LockOutTagOut

  CREATE TABLE LockOutTagOut (

  TicketID VARCHAR(10) NOT NULL PRIMARY KEY CLUSTERED,

  FaultDescription NVARCHAR(255) SPARSE NULL,

  CallerDate DATETIME2 NOT NULL,

  OperatorID CHAR(10) NOT NULL,

  CrewID CHAR(10) NOT NULL,

  CallerID CHAR(10) NOT NULL,

  TurbineID VARCHAR(10) NOT NULL);

  GO

  ALTER TABLE Caller

  ADD CONSTRAINT fk_Site FOREIGN KEY (SiteID)

  REFERENCES Site(SiteID);

  GO

  ALTER TABLE Site

  ADD CONSTRAINT fk_LockOutTagOut FOREIGN KEY (TicketID)

  REFERENCES LockOutTagOut(TicketID);

  GO

  ALTER TABLE LockOutTagOut

  ADD CONSTRAINT fk_Operator FOREIGN KEY (OperatorID)

  REFERENCES Operator (OperatorID);

  GO

  ALTER TABLE LockOutTagOut

  ADD CONSTRAINT fk_Caller FOREIGN KEY (CallerID)

  REFERENCES CALLER (CallerID);

  GO

  ALTER TABLE LockOutTagOut

  ADD CONSTRAINT fk_Crew FOREIGN KEY (CrewID)

  REFERENCES Crew (CrewID);

  GO

  ALTER TABLE LockOutTagOut

  ADD CONSTRAINT fk_Turbine FOREIGN KEY (TurbineID)

  REFERENCES Turbine(TurbineID);

  GO

  Can someone please review and provide me advice?

  Notes: Business issues on Caller tables. The Caller will resign/change jobs every week.

  How to resolve this issues and can anyone advice on this?

  Thanks in advance.

  Edwin

• LutzM

  SSC Guru

  Points: 107049

  More actions

  September 15, 2009 at 3:45 pm

  #1054148

  duplicate post.

  discussion already started here .

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]