CREATE TABLE [dbo].[Segment]
(
[SegmentID] [int] NOT NULL IDENTITY(1, 1),
[OrderFID] [int] NOT NULL,
[SegmentNumber] [int] NOT NULL,
[OriginOrderAddressFID] [int] NULL,
[OriginXtraStopAddressFID] [int] NULL,
[DestinationOrderAddressFID] [int] NULL,
[DestinationXtraStopAddressFID] [int] NULL,
[EstimatedWeight] [int] NULL,
[BilledWeight] [int] NULL,
[EstimatedCubicFeet] [float] NULL,
[BilledCubicFeet] [float] NULL,
[EstimatedMiles] [int] NULL,
[BilledMiles] [int] NULL,
[StartPackDate] [datetime] NULL,
[EndPackDate] [datetime] NULL,
[StartLoadDate] [datetime] NULL,
[EndLoadDate] [datetime] NULL,
[StartLoadTime] [datetime] NULL,
[EndLoadTime] [datetime] NULL,
[StartDeliveryDate] [datetime] NULL,
[EndDeliveryDate] [datetime] NULL,
[StartDeliveryTime] [datetime] NULL,
[EndDeliveryTime] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Segment] ADD CONSTRAINT [CK_Segment_Addresses] CHECK ((([OriginOrderAddressFID] IS NOT NULL AND [OriginXtraStopAddressFID] IS NULL OR [OriginOrderAddressFID] IS NULL AND [OriginXtraStopAddressFID] IS NOT NULL) AND ([DestinationOrderAddressFID] IS NOT NULL AND [DestinationXtraStopAddressFID] IS NULL OR [DestinationOrderAddressFID] IS NULL AND [DestinationXtraStopAddressFID] IS NOT NULL)))
GO
ALTER TABLE [dbo].[Segment] ADD CONSTRAINT [PK_Segment] PRIMARY KEY NONCLUSTERED  ([SegmentID]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Segment_OrderFID] ON [dbo].[Segment] ([OrderFID]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Segment] ADD CONSTRAINT [FK_Segment_DestinationOrderAddressFID] FOREIGN KEY ([DestinationOrderAddressFID]) REFERENCES [dbo].[OrderAddress] ([OrderAddressID])
GO
ALTER TABLE [dbo].[Segment] ADD CONSTRAINT [FK_Segment_DestinationXtraStopAddressFID] FOREIGN KEY ([DestinationXtraStopAddressFID]) REFERENCES [dbo].[XtraStopAddress] ([XtraStopAddressID])
GO
ALTER TABLE [dbo].[Segment] ADD CONSTRAINT [FK_Segment_OrderFID] FOREIGN KEY ([OrderFID]) REFERENCES [dbo].[Orders] ([PriKey])
GO
ALTER TABLE [dbo].[Segment] ADD CONSTRAINT [FK_Segment_OriginOrderAddressFID] FOREIGN KEY ([OriginOrderAddressFID]) REFERENCES [dbo].[OrderAddress] ([OrderAddressID])
GO
ALTER TABLE [dbo].[Segment] ADD CONSTRAINT [FK_Segment_OriginXtraStopAddressFID] FOREIGN KEY ([OriginXtraStopAddressFID]) REFERENCES [dbo].[XtraStopAddress] ([XtraStopAddressID])
GO
GRANT SELECT ON  [dbo].[Segment] TO [MssExec]
GRANT INSERT ON  [dbo].[Segment] TO [MssExec]
GRANT DELETE ON  [dbo].[Segment] TO [MssExec]
GRANT UPDATE ON  [dbo].[Segment] TO [MssExec]
GO