My app has Distribution Lists (list of contacts) which contains entries that can be one of three types: 1) A record from my Persons table, 2) a record from my Organizations table, or 3) another record from the Distribution_Lists table.
I have this implemented in SQL as a Distribution_List_Entries table that looks like this:
/****** Object: Table [dbo].[Distribution_List_Entries] Script Date: 04/12/2006 14:19:31 ******/
CREATE TABLE [dbo].[Distribution_List_Entries](
[Distribution_List_Entry_ID] [int] IDENTITY(1,1) NOT NULL,
[Distribution_List] [int] NOT NULL,
[Person_ID] [int] NULL,
[Destribution_List_ID] [int] NULL,
[Organization_ID] [int] NULL,
CONSTRAINT [PK_PersonDistJoin] PRIMARY KEY CLUSTERED
(
[Distribution_List_Entry_ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Distribution_List_Entries] WITH CHECK ADD CONSTRAINT [FK_Distribution_List_Entries_Distribution_Lists] FOREIGN KEY([Distribution_List])
REFERENCES [dbo].[Distribution_Lists] ([Distribution_List_ID])
GO
ALTER TABLE [dbo].[Distribution_List_Entries] WITH CHECK ADD CONSTRAINT [FK_Distribution_List_Entries_Distribution_Lists1] FOREIGN KEY([Destribution_List_ID])
REFERENCES [dbo].[Distribution_Lists] ([Distribution_List_ID])
GO
ALTER TABLE [dbo].[Distribution_List_Entries] WITH CHECK ADD CONSTRAINT [FK_Distribution_List_Entries_Organizations] FOREIGN KEY([Organization_ID])
REFERENCES [dbo].[Organizations] ([Organization_ID])
GO
ALTER TABLE [dbo].[Distribution_List_Entries] WITH CHECK ADD CONSTRAINT [FK_Distribution_List_Entries_Persons] FOREIGN KEY([Person_ID])
REFERENCES [dbo].[Persons] ([Person_ID])
The way it would work is that the Distribution List would consist ultimately of Persons but the Persons can be listed explicitly or can be listed by virtue of them being a member of an Organization or being a member of another existing Distribution List.
At first sight these related tables might be set up as subtypes of Distribution_List_Entry but I don't think that will work because they aren't really subtypes (e.g. in other places in the application a Persons table entry should NOT be thought of as a subtype of a DistributionListEntry. It is just a Person - that's it). Besides what if I decide to make a subtype from Person or Organization (might happen) - I don't think I can have an entity that is derived from two parents (since .NET CLR doesn't support multiple inheritence).
So how do I implement this in the LLBLGen Designer? Really what I want is for the three entry types to be CONTAINED by a DistributionListEntry and not be a SUBTYPE of a DistributionListEntry.
Or is there no implementation options here - do I just add my own custom code to test which of the three DistributionListEntry fields have a non-null value and follow it to get the entry (either a Person, an Organization or another DistributionList record)?
