This topic isn't strictly LLBLGen related however we are using the product so perhaps it can help us here to solve this problem.

Currently we are adding support for discussions / conversations related to multiple document types e.g.

• Purchase Orders
• Supplier Invoices
• Remittance Advice

The schema is a bit similar although very cut down from the HnD design. We came up with the following design

POHead - Purchase orders POHeadThreadLink - Link table to link threads with purchase order identifiers. POHeadThread - Threads for single purchase orders POHeadThreadMessage - Messages for purchase order threads.

Unfortunately for each document type we have to create 3 additional tables.

It would be great if we could simply create a generic Thread and Message table and perhaps only specific link tables for each document type e.g. SIHeadThreadLink and RAdviceThreadLink however I can't see how to do this and maintain referential integrity.

Can anyone think of any better approaches here as I'm sure we can do better?

We don't do message threads for our documents, but we did have to create a way to route these documents through approval chains. We have multiple document types which all use the same routing tables to track where they are in their approval path. We named our stuff very similar to yours in that a document travels along a thread and makes stops along the way called thread_steps. So, each document table contains a column for thread_id which tells us A) the document has been routed and B) what thread it is attached to. This is a 1 : 1 relationship though, not like your 1 : N situation. BUT, here's where we're similar. We wanted a way to show all the documents in one place, similar to Outlook. So, we needed a way to pull up documents regardless of type. Here's a bit of our data model:

create table doc_type (
type char(6) primary key,
abbr varchar(20) not null,
descr varchar(50) not null)

insert into doc_type values ('po','PO','Purchase Order')
insert into doc_type values ('inv','Invoice','Invoice')

create table document (
id int identity primary key,
doc_type char(6) not null references doc_type (type),
title varchar(1000))

create table document_id (
id int identity primary key,
doc_id int not null references document (id),
ref_id int)

The document_id table relates this ambiguous document record to the specific doc_type I'm routing. The ref_id column is not foreign keyed to anything, BUT, that isn't necessarily a bad thing in this case. Now, I've got a purchase_order table and an invoice table. When I create a new document, I do the following:

• Add new record to purchase_order table
• Add a new record to the document table of doc_type = 'po'
• Add a new record to the document_id table with the just added doc_id and just added po_id

...Now, when I want a list of all documents, irrespective of type, I pull all rows from the document table. I can know which kind of document it is and look in the document_id table at the ref_id column to find out which document it is related to. For your scenario, I might do this:

create table document_id (
id int identity primary key,
doc_id int references document (id)
message_thread_id int references thread (id)
ref_id int)

...of course this is all just a first though as I'm reading your request, so your mileage may vary.