CREATE TABLE [dbo].[HARALLCHILDRENPATH]
(
[ITEMOBJID] [int] NOT NULL,
[CHILDITEMID] [int] NOT NULL,
[VERSIONOBJID] [int] NOT NULL,
[CHILDVERSIONOBJID] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[HARALLCHILDRENPATH] ADD CONSTRAINT [HARALLCHILDRENPATH_PK] PRIMARY KEY CLUSTERED ([VERSIONOBJID], [CHILDVERSIONOBJID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [HARALLCHILDRENPATH_CHILDITEM] ON [dbo].[HARALLCHILDRENPATH] ([CHILDITEMID], [CHILDVERSIONOBJID], [ITEMOBJID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [HARALLCHILDRENPATH_CHILDV_IDX] ON [dbo].[HARALLCHILDRENPATH] ([CHILDVERSIONOBJID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [HARALLCHILDRENPATH_ITEMCHILD] ON [dbo].[HARALLCHILDRENPATH] ([ITEMOBJID], [CHILDITEMID], [VERSIONOBJID], [CHILDVERSIONOBJID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [HARALLCHILDRENPATH_VERID_IDX] ON [dbo].[HARALLCHILDRENPATH] ([VERSIONOBJID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[HARALLCHILDRENPATH] ADD CONSTRAINT [HARALLCHILDRENPATH_VERID_FK] FOREIGN KEY ([VERSIONOBJID]) REFERENCES [dbo].[HARVERSIONS] ([VERSIONOBJID]) ON DELETE CASCADE
GO
EXEC sp_addextendedproperty N'Caption', N'The HARALLCHILDRENPATH table stores all the paths under an item to facilitate recursive searches.', 'SCHEMA', N'dbo', 'TABLE', N'HARALLCHILDRENPATH', NULL, NULL
GO
EXEC sp_addextendedproperty N'Caption', N'Object ID of subitem path', 'SCHEMA', N'dbo', 'TABLE', N'HARALLCHILDRENPATH', 'COLUMN', N'CHILDITEMID'
GO
EXEC sp_addextendedproperty N'Caption', N'Object ID of subitem version', 'SCHEMA', N'dbo', 'TABLE', N'HARALLCHILDRENPATH', 'COLUMN', N'CHILDVERSIONOBJID'
GO
EXEC sp_addextendedproperty N'Caption', N'Object ID of item path', 'SCHEMA', N'dbo', 'TABLE', N'HARALLCHILDRENPATH', 'COLUMN', N'ITEMOBJID'
GO
EXEC sp_addextendedproperty N'Caption', N'Object ID of version', 'SCHEMA', N'dbo', 'TABLE', N'HARALLCHILDRENPATH', 'COLUMN', N'VERSIONOBJID'
GO