Posted by kyle.fitzgerald on 05/25/07 18:50

Ok here is the PageCategory, Documents, Pages, and XREF_DOC_PAGE
tables

I was setting it up so I could just query the XREF table and pass in
the pageID to give me all the files for that page but as I said before
not sure how to write that query to format it with the columns I want,
Is there a better way to set this up in the database ?


CREATE TABLE [dbo].[PageCategory] (
[PageCategoryID]int IDENTITY(1, 1) NOT NULL,
[PageCategory]nvarchar(250) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
[LastUpdatedID]int NULL,
[LastUpdateDate]datetime NULL,
PRIMARY KEY CLUSTERED ([PageCategoryID])
)
ON [PRIMARY]
GO

CREATE TABLE [dbo].[Documents] (
[DocID]int IDENTITY(1, 1) NOT NULL,
[DocTypeID]int NULL,
[Title]nvarchar(250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LastUpdateID]int NULL,
[LastUpdateDate]datetime NULL,
[Description]text COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DocName]text COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
PRIMARY KEY CLUSTERED ([DocID])
)
ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[Pages] (
[PageID]int IDENTITY(1, 1) NOT NULL,
[PageShortName]nvarchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
[PageName]nvarchar(250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PageTitle]nvarchar(250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PageType]nvarchar(250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
PRIMARY KEY CLUSTERED ([PageID])
)
ON [PRIMARY]
GO

CREATE TABLE [dbo].[XREF_Doc_Page] (
[XREF_Doc_Page_ID]int IDENTITY(1, 1) NOT NULL,
[DocID]int NULL,
[PageID]int NULL,
[PageCategoryID]int NULL,
[SortOrder]int NULL,
[LastUpdateID]int NULL,
[LastUpdateDate]datetime NULL,
PRIMARY KEY CLUSTERED ([XREF_Doc_Page_ID])
)
ON [PRIMARY]
GO

• Next in forum: Re: Synchronous Bulk-Copy into two table
• Prev in forum: uploading image files
• Thread view: Re: Need Help with a SQL Statement - Trying not to use a Cursor

[Reply to this message]