Assume a case where multiple entities all implement a shared interface. For convenience and normalization, the data associated with this interface is stored in a single, separate table.

Normally, if multiple parent tables were to stored records in this child table, we would use one mapping table per parent as an intermediary. But what if each record is unique, and not shared between the parent tables?

In this case we can use GUIDs to associate the co-located data with the correct parent.

create table Lesson (LessonGUID uniqueidentifier not null default newid(), title varchar(50), primary key (LessonGUID )) go

create table Course (CourseGUID uniqueidentifier not null default newid(), title varchar(50), primary key (CourseGUID )) go

/* ParentEntityGuid can store a GUID from either the Lesson or Course table. This creates an implied, but not explicit, foreign key relationship to two parent tables. As the GUIDs should be unique, the data can be co-located in the table without conflict. */ create table LearningObjective (LearningObjectiveGUID uniqueidentifier not null default newid(), ParentEntityGUID uniqueidentifier not null, ObjectiveText varchar(500) not null, primary key (LearningObjectiveGUID) ) go

Ignoring the question of whether this is good or bad database design, can LLBLGen handle this situation with the autogenerated entities or manual manipulation within the GUI?

Or do we have to use a stored procedure to manage the retrieval of Lesson or Course specific records from the LearningObjective table?

The question is not clear enough, at least for me, would you please re-phrase it.

Let me try again.

Here's an example from our data model (slightly simplified):

CREATE TABLE [dbo].[lmsContentBlock](
    [ContentGUID] [uniqueidentifier] NOT NULL,
    [ContentTypeId] [int] NOT NULL,
    [DisplayOrder] [int] NOT NULL,
 CONSTRAINT [PK_lmsContentBlock] PRIMARY KEY CLUSTERED 
(
    [ContentGUID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[lmsContentType](
    [ContentTypeId] [int] NOT NULL,
    [Name] [varchar](50) NOT NULL,
    [Description] [varchar](150) NULL,
 CONSTRAINT [PK_lmsContentType] PRIMARY KEY CLUSTERED 
(
    [ContentTypeId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[lmsQuestion](
    [QuestionGUID] [uniqueidentifier] NOT NULL,
    [Question] [text] not null,
    CONSTRAINT [lmsQuestion] PRIMARY KEY CLUSTERED 
    (
        [QuestionGUID] ASC
    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[lmsFileContent](
    [FileContentGUID] [uniqueidentifier] NOT NULL,
    [FileLocation] [varchar(1000)] not null,
    CONSTRAINT [lmsFileContent] PRIMARY KEY CLUSTERED 
    (
        [FileContentGUID] ASC
    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

We need to treat both Questions and Files (Video, Audio, etc) as blocks of content that can be embedded in any order and in any combination on a page.

To make use of this structure, we would reference an existing Question or File record by writing it's guid as the lmsContentBlock.ContentGUID value and assign it a ContentTypeId (Question = 1 or File = 2) and a DisplayOrder.

What we would like to do is use LLBLGen to load from the appropriate table (lmsQuestion or lmsFileContent) based on the ContentTypeId.

Is this possible without the use of a stored procedure?

This sounds like a classic case for the use of inheritance - you have two items (Questions and Files) that both need to appear as specialised cases of another item (ContentBlock).

Take a look at the documentation and see if this fits your situation.

Matt