Listing 2: Creating Thin Tables
USE MyDatabase
GO
/* --------------------------------------------------------------------------- 
  Table dbo.ProcessStatus (reference/lookup table)
  --------------------------------------------------------------------------- */
CREATE TABLE dbo.ProcessStatus (
  ProcessStatusCode   char(4)     NOT NULL , /* pkey */
  ProcessStatusDescr   varchar(50)  NULL
) ON MyDatabase_data
GO

/* --------------------------------------------------------------------------- 
  Table dbo.ProcessStep (reference/lookup table)
  --------------------------------------------------------------------------- */
CREATE TABLE dbo.ProcessStep (
  ProcessStepCode   char(4)      NOT NULL , /* pkey */
  ProcessStepDescr   varchar(50)  NULL
) ON MyDatabase_data
GO

/* --------------------------------------------------------------------------- 
  Table dbo.ProcessOrder
  Total row length assuming 40 percent of varchar space used,
   72 bytes, 111 recs/page
  --------------------------------------------------------------------------- */
CREATE TABLE dbo.ProcessOrder (
  ProcOrderID         int IDENTITY(1, 1)  NOT NULL ,  /* pkey */
  SaleID              int              NOT NULL , /* fkey links back to dbo.Sale */
  ProcessStatusCode   char(4)           NOT NULL , /* fkey links back to dbo.ProcessStatus*/
BEGIN CALLOUT A
  ItemWoodType   varchar(30)  NOT NULL ,
  ItemFinishColor   varchar(30)  NOT NULL ,
  ItemSize         varchar(30)  NULL ,
END CALLOUT A
  ShipOption       varchar(20)  NULL
) ON MyDatabase_data
GO

/* --------------------------------------------------------------------------- 
  Table dbo.ProcessOrderStep
  Total row length assuming 40 percent of varchar space used,
   236 bytes, 34 recs/page
  --------------------------------------------------------------------------- */
CREATE TABLE dbo.ProcessOrderStep (
  ProcOrderStepID   int IDENTITY(1, 1)  NOT NULL ,  /* pkey */
  ProcOrderID       int              NOT NULL , /* fkey links back to dbo.ProcessOrder*/
  ProcessStepCode   char(4)          NOT NULL , /* fkey links back to dbo.ProcessStep */
  ProcessStepDate   datetime         NULL ,
  ProcessStepConfirmationNbr  varchar(50)  NULL ,
  ProcessStepNotes   varchar(500)     NULL
) ON MyDatabase_data
GO

----------------------------------------------------------------
-- Create primary key constraints.
----------------------------------------------------------------
ALTER TABLE dbo.ProcessStatus WITH NOCHECK
ADD CONSTRAINT PK_ProcessStatus PRIMARY KEY CLUSTERED (ProcessStatusCode)
ON MyDatabase_data
GO

ALTER TABLE dbo.ProcessStep WITH NOCHECK
ADD CONSTRAINT PK_ProcessStep PRIMARY KEY CLUSTERED (ProcessStepCode)
ON MyDatabase_data
GO

ALTER TABLE dbo.ProcessOrder WITH NOCHECK
ADD CONSTRAINT PK_ProcessOrder PRIMARY KEY NONCLUSTERED (ProcOrderID)
ON MyDatabase_data
GO

ALTER TABLE dbo.ProcessOrderStep WITH NOCHECK
ADD CONSTRAINT PK_ProcessOrderStep PRIMARY KEY NONCLUSTERED 
(ProcOrderStepID)
ON MyDatabase_data
GO

----------------------------------------------------------------
-- Create foreign key constraints.
----------------------------------------------------------------
ALTER TABLE dbo.ProcessOrder
    ADD CONSTRAINT FK_ProcessOrder2Sale FOREIGN KEY (SaleID)
   REFERENCES dbo.Sale (SaleID)
GO
ALTER TABLE dbo.ProcessOrder
    ADD CONSTRAINT FK_ProcessOrder2ProcessStatus FOREIGN KEY (ProcessStatusCode)
   REFERENCES dbo.ProcessStatus (ProcessStatusCode)
GO

ALTER TABLE dbo.ProcessOrderStep
    ADD CONSTRAINT FK_ProcessOrderStep2ProcessOrder FOREIGN KEY (ProcOrderID)
   REFERENCES dbo.ProcessOrder (ProcOrderID)
GO
ALTER TABLE dbo.ProcessOrderStep
    ADD CONSTRAINT FK_ProcessOrderStep2ProcessStep FOREIGN KEY (ProcessStepCode)
   REFERENCES dbo.ProcessStep (ProcessStepCode)
GO

----------------------------------------------------------------
-- Create clustered indexes.
----------------------------------------------------------------
CREATE CLUSTERED INDEX idc_ProcessOrder
  ON dbo.ProcessOrder (SaleID, ProcOrderID)
WITH PAD_INDEX, FILLFACTOR = 90
ON MyDatabase_data
GO

CREATE CLUSTERED INDEX idc_ProcessOrderStep
  ON dbo.ProcessOrderStep (ProcOrderID, ProcOrderStepID)
WITH PAD_INDEX, FILLFACTOR = 90
ON MyDatabase_data
GO

----------------------------------------------------------------
-- Create nonclustered indexes for foreign key constraints.
----------------------------------------------------------------
CREATE INDEX idxfk_ProcessOrder2ProcessStatus
  ON dbo.ProcessOrder (ProcessStatusCode)
WITH PAD_INDEX, FILLFACTOR = 90
ON MyDatabase_index
GO

CREATE INDEX idxfk_ProcessOrderStep2ProcessStep
  ON dbo.ProcessOrderStep (ProcessStepCode)
WITH PAD_INDEX, FILLFACTOR = 90
ON MyDatabase_index
GO