Table driven mail merge
Date: Sun, 6 Aug 2006 10:55:44 +0100
Message-ID: <44d5bca5$0$640$5a6aecb4_at_news.aaisp.net.uk>
The client runs a training organisation. They have a large amount of corresondence to send. Contents of the correspondence vary, and increasingly so, according to type of course, type of buyer, type of job student has etc.
Plan
All text for all correspondence will be stored in tables. This bit will be:
Letters:
LetterID LetterType
1 Welcome Letter 2 Finish Letter
LetterSection:
LetterSectionID LetterSectionName
1 OpeningParagraph 2 SigningOff
and then a junction table:
LetterSectionText:
LetterSectionID LetterID DefaultTextID
1 1 1 2 1 2
then:
Text (a table)
TextID Text
1 "hi there" 2 "bye for now"
So we can get defaults into letters. Text can include "" for when there won't ever be a default.
Now the bit that's getting me.
For each LetterSectionID LetterID record in the junction table I only want it to vary acording to one other table.
So eg:
CourseLetterSectionText (table)
LetterTypeID LetterSectionID TextID CourseID
1 1 1 1 LetterSectionID + LetterID would be the PK here.
But there would have to be another table JobsLetterSectionTable, that varied according to Job ID. In fact each of the base table would have to have a baseTableNameLetterSectionText table, if the contents of that base table can alter the contents of some correspondence.
That worries me a little. That something might be wrong in the design. Each table having a 'pair'. But it doesn't worry me too much.
What is more important, data integrity-wise is that each LetterSectionID LetterID pair (each discrete letter section) should only have it's contents decided by ONE OTHER table. So it looks like I need a constraint. I haven't tried to write that yet, but I'm expecting something that selects from each the baseTableNameLetterSectionText table, grouped by letter section, then unioned together, and there shouldn't be more than one. Which might work, problem is, each time a table is added, the constraint will need altering. Again, not a major problem, but makes me think the approach is wrong.
Long question, sorry.
Anybody got any suggestions?
After my sig there's a script to create this in SQL Server 2000, if anybody wants to try and look at it in EM. No data yet.
Yours, Emily
Set up script:
*********************Script starts
CREATE DATABASE [MailMergeTest] ON (NAME = N'MailMergeTest_Data', FILENAME = N'C:\Program
Files\Microsoft SQL Server\MSSQL\data\MailMergeTest_Data.MDF' , SIZE = 1, FILEGROWTH = 10%) LOG ON (NAME = N'MailMergeTest_Log', FILENAME = N'C:\Program Files\Microsoft SQL
Server\MSSQL\data\MailMergeTest_Log.LDF' , SIZE = 1, FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
GO
exec sp_dboption N'MailMergeTest', N'autoclose', N'false' GO
exec sp_dboption N'MailMergeTest', N'bulkcopy', N'false' GO
exec sp_dboption N'MailMergeTest', N'trunc. log', N'false' GO
exec sp_dboption N'MailMergeTest', N'torn page detection', N'true' GO
exec sp_dboption N'MailMergeTest', N'read only', N'false' GO
exec sp_dboption N'MailMergeTest', N'dbo use', N'false' GO
exec sp_dboption N'MailMergeTest', N'single', N'false' GO
exec sp_dboption N'MailMergeTest', N'autoshrink', N'false' GO
exec sp_dboption N'MailMergeTest', N'ANSI null default', N'false' GO
exec sp_dboption N'MailMergeTest', N'recursive triggers', N'false' GO
exec sp_dboption N'MailMergeTest', N'ANSI nulls', N'false' GO
exec sp_dboption N'MailMergeTest', N'concat null yields null', N'false' GO
exec sp_dboption N'MailMergeTest', N'cursor close on commit', N'false' GO
exec sp_dboption N'MailMergeTest', N'default to local cursor', N'false' GO
exec sp_dboption N'MailMergeTest', N'quoted identifier', N'false' GO
exec sp_dboption N'MailMergeTest', N'ANSI warnings', N'false' GO
exec sp_dboption N'MailMergeTest', N'auto create statistics', N'true' GO
exec sp_dboption N'MailMergeTest', N'auto update statistics', N'true' GO
if( (_at__at_microsoftversion / power(2, 24) = 8) and (@@microsoftversion & 0xffff
>= 724) )
exec sp_dboption N'MailMergeTest', N'db chaining', N'false'
GO
CREATE TABLE [CourseLetterSectionText] (
[LetterTypeID] [int] NOT NULL , [CourseTypeID] [int] NOT NULL , [LetterSectionTypeID] [int] NOT NULL , [TextID] [int] NULL ,
CONSTRAINT [PK_CourseLetterSectionText] PRIMARY KEY CLUSTERED (
[LetterTypeID],
[CourseTypeID],
[LetterSectionTypeID]
) ON [PRIMARY] ,
CONSTRAINT [FK_CourseLetterSectionText_CourseTypes] FOREIGN KEY (
[CourseTypeID]
) REFERENCES [CourseTypes] (
[CourseTypeID]
),
CONSTRAINT [FK_CourseLetterSectionText_LetterSectionText] FOREIGN KEY (
[LetterTypeID],
[LetterSectionTypeID]
) REFERENCES [LetterSectionText] (
[LetterTypeID],
[LetterSectionTypeID]
),
CONSTRAINT [FK_CourseLetterSectionText_Text] FOREIGN KEY (
[TextID]
) REFERENCES [Text] (
[TextID]
)
) ON [PRIMARY] GO
CREATE TABLE [CourseTypes] (
[CourseTypeID] [int] IDENTITY (1, 1) NOT NULL ,
[CourseTypeName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_CourseTypes] PRIMARY KEY CLUSTERED
(
[CourseTypeID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [JobLetterSectionText] (
[JobTypeID] [int] NOT NULL , [LetterTypeID] [int] NOT NULL , [LetterSectionTypeID] [int] NOT NULL , [TextID] [int] NULL ,
CONSTRAINT [PK_JobLetterSectionText] PRIMARY KEY CLUSTERED (
[JobTypeID],
[LetterTypeID],
[LetterSectionTypeID]
) ON [PRIMARY] ,
CONSTRAINT [FK_JobLetterSectionText_JobTypes] FOREIGN KEY (
[JobTypeID]
) REFERENCES [JobTypes] (
[JobTypeID]
),
CONSTRAINT [FK_JobLetterSectionText_LetterSectionText] FOREIGN KEY (
[LetterTypeID],
[LetterSectionTypeID]
) REFERENCES [LetterSectionText] (
[LetterTypeID],
[LetterSectionTypeID]
),
CONSTRAINT [FK_JobLetterSectionText_Text] FOREIGN KEY (
[TextID]
) REFERENCES [Text] (
[TextID]
)
) ON [PRIMARY]
GO
CREATE TABLE [JobTypes] (
[JobTypeID] [int] IDENTITY (1, 1) NOT NULL ,
[JobTypeName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_JobTypes] PRIMARY KEY CLUSTERED
(
[JobTypeID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [LetterSectionText] (
[LetterTypeID] [int] NOT NULL , [LetterSectionTypeID] [int] NOT NULL , [TextID] [int] NULL ,
CONSTRAINT [PK_LetterSectionText] PRIMARY KEY CLUSTERED (
[LetterTypeID],
[LetterSectionTypeID]
) ON [PRIMARY] ,
CONSTRAINT [FK_LetterSectionText_LetterSectionTypes] FOREIGN KEY (
[LetterSectionTypeID]
) REFERENCES [LetterSectionTypes] (
[LetterSectionTypeID]
),
CONSTRAINT [FK_LetterSectionText_LetterTypes] FOREIGN KEY (
[LetterTypeID]
) REFERENCES [LetterTypes] (
[LetterTypeID]
),
CONSTRAINT [FK_LetterSectionText_Text] FOREIGN KEY (
[TextID]
) REFERENCES [Text] (
[TextID]
)
) ON [PRIMARY]
GO
CREATE TABLE [LetterSectionTypes] (
[LetterSectionTypeID] [int] IDENTITY (1, 1) NOT NULL ,
[LetterSectionName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
CONSTRAINT [PK_LetterSectionTypes] PRIMARY KEY CLUSTERED
(
[LetterSectionTypeID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [LetterTypes] (
[LetterTypeID] [int] IDENTITY (1, 1) NOT NULL ,
[LetterTypeName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_LetterTypes] PRIMARY KEY CLUSTERED
(
[LetterTypeID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [Text] (
[TextID] [int] IDENTITY (1, 1) NOT NULL ,
[Text] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_Text] PRIMARY KEY CLUSTERED
(
[TextID]
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
****************************script endsReceived on Sun Aug 06 2006 - 11:55:44 CEST