Using LLBLGen Designer 5.6.1 and Microsoft SQL Server.

I do database-first development where I create the table and then reverse-engineer the table to an entity.

I have a table called TaxItem. I'm generating a hierarchy of classes off of that table. I have a field called Subtype that I use as the discriminator field.

The base class is TaxItem.

It has fields: TaxItemId, AssessmentId, Type, Description, Amount, UpdatedUserID, UpdatedTimeStamp, Subtype.

The subtypes are:

• FixedRateTaxItem
  It adds the field ValuationClassId which is a foreign key to the ValuationClass table. A FixedRateTaxItem should reference only one ValuationClass. A ValuationClass can be used on many FixedRateTaxItems.

• MillRatedTaxItem It adds the field DistrictId which is a foreign key to the District table. A MillRatedTaxItem should reference only one District. A District can be used on many MillRatedTaxItems.

• SpecialTaxItem It adds the field UserDefinedSpecialCodeId which is a foreign key to the UserDefinedSpecialCode table A SpecialTaxItem should reference only one UserDefinedSpecialCode. A UserDefinedSpecialCode can be used on many SpecialTaxItems.

• OtherChargeTaxItem It adds the fields OtherChargeTypeId which is a foreign key to the OtherChargeType table. A OtherChargeTaxItem should reference only one OtherChargeType. A OtherChargeType can be used on many OtherChargeTaxItems.

All these subtypes have a many-to-1 relationship with some primary key table. I'm wondering why the LLBLGen Designer shows these relationships as 1-to-1.

Here is the SQL for the TaxItem table that I generated from SQL Server Management Studio.

CREATE TABLE [dbo].[TaxItem](
    [TaxItemID] [int] IDENTITY(1,1) NOT NULL,
    [AssessmentID] [int] NOT NULL,
    [Type] [varchar](3) NOT NULL,
    [Description] [varchar](50) NOT NULL,
    [Amount] [numeric](10, 2) NOT NULL,
    [UpdatedUserID] [varchar](10) NOT NULL,
    [UpdatedTimeStamp] [varchar](14) NOT NULL,
    [Subtype] [varchar](18) NOT NULL,
    [DistrictID] [int] NULL,
    [UserDefinedSpecialCodeID] [int] NULL,
    [ValuationClassID] [int] NULL,
    [OtherChargeTypeID] [int] NULL,
 CONSTRAINT [PK_TaxItem] PRIMARY KEY CLUSTERED 
(
    [TaxItemID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[TaxItem] ADD  CONSTRAINT [DF_TaxItem_Amount]  DEFAULT ((0)) FOR [Amount]
GO

ALTER TABLE [dbo].[TaxItem] ADD  CONSTRAINT [DF_TaxItem_UpdatedUserID]  DEFAULT ('') FOR [UpdatedUserID]
GO

ALTER TABLE [dbo].[TaxItem] ADD  CONSTRAINT [DF_TaxItem_UpdatedTimeStamp]  DEFAULT ('01000101000000') FOR [UpdatedTimeStamp]
GO

ALTER TABLE [dbo].[TaxItem] ADD  CONSTRAINT [DF_TaxItem_Subtype]  DEFAULT ('None') FOR [Subtype]
GO

ALTER TABLE [dbo].[TaxItem]  WITH CHECK ADD  CONSTRAINT [FK_TaxItem_Assessment] FOREIGN KEY([AssessmentID])
REFERENCES [dbo].[Assessment] ([AssessmentID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[TaxItem] CHECK CONSTRAINT [FK_TaxItem_Assessment]
GO

ALTER TABLE [dbo].[TaxItem]  WITH CHECK ADD  CONSTRAINT [FK_TaxItem_District] FOREIGN KEY([DistrictID])
REFERENCES [dbo].[District] ([DistrictID])
GO

ALTER TABLE [dbo].[TaxItem] CHECK CONSTRAINT [FK_TaxItem_District]
GO

ALTER TABLE [dbo].[TaxItem]  WITH CHECK ADD  CONSTRAINT [FK_TaxItem_OtherChargeType] FOREIGN KEY([OtherChargeTypeID])
REFERENCES [dbo].[OtherChargeType] ([ID])
GO

ALTER TABLE [dbo].[TaxItem] CHECK CONSTRAINT [FK_TaxItem_OtherChargeType]
GO

ALTER TABLE [dbo].[TaxItem]  WITH CHECK ADD  CONSTRAINT [FK_TaxItem_UserDefinedSpecialCode] FOREIGN KEY([UserDefinedSpecialCodeID])
REFERENCES [dbo].[UserDefinedSpecialCode] ([UserDefinedSpecialCodeID])
GO

ALTER TABLE [dbo].[TaxItem] CHECK CONSTRAINT [FK_TaxItem_UserDefinedSpecialCode]
GO

ALTER TABLE [dbo].[TaxItem]  WITH CHECK ADD  CONSTRAINT [FK_TaxItem_ValuationClass] FOREIGN KEY([ValuationClassID])
REFERENCES [dbo].[ValuationClass] ([ValuationClassID])
GO

ALTER TABLE [dbo].[TaxItem] CHECK CONSTRAINT [FK_TaxItem_ValuationClass]
GO

ALTER TABLE [dbo].[TaxItem]  WITH CHECK ADD  CONSTRAINT [CK_TaxItem_DistrictIDRequiredForMillRatedTaxItem] CHECK  ((NOT ([DistrictID] IS NULL AND [Subtype]='MillRatedTaxItem')))
GO

ALTER TABLE [dbo].[TaxItem] CHECK CONSTRAINT [CK_TaxItem_DistrictIDRequiredForMillRatedTaxItem]
GO

ALTER TABLE [dbo].[TaxItem]  WITH CHECK ADD  CONSTRAINT [CK_TaxItem_OtherChargeTypeIDRequiredForOtherChargeTaxItem] CHECK  ((NOT ([OtherChargeTypeID] IS NULL AND [SubType]='OtherChargeTaxItem')))
GO

ALTER TABLE [dbo].[TaxItem] CHECK CONSTRAINT [CK_TaxItem_OtherChargeTypeIDRequiredForOtherChargeTaxItem]
GO

ALTER TABLE [dbo].[TaxItem]  WITH CHECK ADD  CONSTRAINT [CK_TaxItem_Subtype] CHECK  (([Subtype]='None' OR [Subtype]='MillRatedTaxItem' OR [Subtype]='SpecialTaxItem' OR [Subtype]='FixedRateTaxItem' OR [Subtype]='OtherChargeTaxItem'))
GO

ALTER TABLE [dbo].[TaxItem] CHECK CONSTRAINT [CK_TaxItem_Subtype]
GO

ALTER TABLE [dbo].[TaxItem]  WITH NOCHECK ADD  CONSTRAINT [CK_TaxItem_Type] CHECK  (([Type]='GPT' OR [Type]='GPC' OR [Type]='LC' OR [Type]='SA' OR [Type]='SC' OR [Type]='DC' OR [Type]='OT' OR [Type]='WTL' OR [Type]='PFC' OR [Type]='MFL' OR [Type]='OTH' OR [Type]='FDC'))
GO

ALTER TABLE [dbo].[TaxItem] CHECK CONSTRAINT [CK_TaxItem_Type]
GO

ALTER TABLE [dbo].[TaxItem]  WITH CHECK ADD  CONSTRAINT [CK_TaxItem_UserDefinedSpecialCodeIDRequiredForSpecialTaxItem] CHECK  ((NOT ([UserDefinedSpecialCodeID] IS NULL AND [Subtype]='SpecialTaxItem')))
GO

ALTER TABLE [dbo].[TaxItem] CHECK CONSTRAINT [CK_TaxItem_UserDefinedSpecialCodeIDRequiredForSpecialTaxItem]
GO

ALTER TABLE [dbo].[TaxItem]  WITH CHECK ADD  CONSTRAINT [CK_TaxItem_ValuationClassIDRequiredForFixedRateTaxItem] CHECK  ((NOT ([ValuationClassID] IS NULL AND [Subtype]='FixedRateTaxItem')))
GO

ALTER TABLE [dbo].[TaxItem] CHECK CONSTRAINT [CK_TaxItem_ValuationClassIDRequiredForFixedRateTaxItem]
GO

Such relation is defined manually in the Designer, is this what you did?

I've created the tables in the database, and I did the reverse engineering.

Then I had to remove the relation from the TaxItem to each of the related entities, and remove the subType-fields.

Created the TargetPerHierarchy Inheritance Hierarchy.

Created a new SubType.

Defined the Discriminator field, and the values.

Then I had to add the subType-Fields to each of the subType entities.

Then I defined the model-only relations between the subtypes and related entities, and that's where I can define the type of the relation at both ends.

To be honest, I don't remember if I did the exact same steps you did, since I did it years ago.

You said:

Such relation is defined manually in the Designer, is this what you did?

and:

Then I defined the model-only relations between the subtypes and related entities, and that's where I can define the type of the relation at both ends.

For non-subtype entities, LLBLGen automatically determines the cardinality of the relationships between the entities. I just assumed it would do the same for subtypes too. But from your response, it appears I have to manually define the cardinality.

clint wrote:

All these subtypes have a many-to-1 relationship with some primary key table.

Your generated script doesn't have these referenced primary tables nor whether the TaxItem has extra constraints like unique index, etc. Could you attach your LLBLGen project and DDL script? (or something similar that reproduces the scenario)

yes in TargetPerEntityHierarchy inheritance scenarios you have to specify the cardinality yourself as the fk fields are 'nullable' because of the TPEH inheritance on the same table, but that isn't sufficient for determining whether the fk side is optional or not.

OK. Thanks everyone.