I am having a hard time seeing the proper solution. Maybe someone on the forum has been down this road.

I am interested in setting up a CMS system that can invoke specific entities based on the type of content I am dealing with. For example if I am working with text content I would like to use a CMSTextEntity, if it needs binary content I would like to use a CMSBinaryEntity. Basically an entity per type of content that I manage.

My table structure at the moment consists of a ContentItem table (1) and a ContentDetail table (M) . The Detail table is giving me headaches. At the moment it contains a number of fields corresponding to the various types of content I work with. There is a field called BinaryValue which is datatype binary, a field called TextValue which is of course a string datatype. A field called IntegerValue which - you guessed it - is of a datatype integer. My DB allows for XML datatypes so I have a corresponding XMLValue field in the table.

I am stuck on what steps I need to take to break apart the ContentDetail into - I guess - a number of smaller tables ( one per datatype ) and relate those back to the SuperType of ContentDetail. Do I use the same PK value between Super Type and Sub Type? Do I need to add a distinguishing characteristic to the super type that allows the logic to pick an appropriate subtype for a given row. Ie. something like a "ContentDetailType" field that allows the logic to go to the TextValue SubType for one row and to the Binary sub type for a different row? A traffic cop for lack of better words.

If you had to deal with a similar situation I would love to hear how you approached this.

Thanks! Thomas

You need to choose between the two different approches to modelling entity inheritance - at the moment you seem stuck between the two.

TablePerEntity is where you have a seperate sub-table for each different sub-type - as you correctly say these are related to the parent table by the PKs

TablePerEntityHierarchy is where you have one big table with a column for each datatype and a discriminator value to tell you which is the appropriate sub type.

Which one you choose is up to you, and will obviously depend on your data and system design etc. TablePerEntity always seems a much better seperation of concerns to me, but that's only my opinion.

There is quite a good explanation of this in the documentation. http://www.llblgen.com/documentation/2.6/Concepts/concepts_inheritance.htm

Matt

Thanks Matt. I took your advise and also found some addl good reading material ( on MSDN of all places ). Since I am dealing with a typical hierarchy of item within item within whatever I will end up using TablePerEntityHierarchy. Thanks for the quick reply !

No problem, happy to help.

Matt