I am designing a database which will hold contact information for people. There are several different types of contact information - each with its own set of unique attributes (as well as a few attributes common to all other subtypes). For instance, "Email Contact", "Instant Messaging Contact", "Mailing Address"...

I read in the LLBLGen manual that the way to implement this in the DB is to flatten the hierarchy and simply put ALL the unique subtype attributes as columns in the supertype table and make then nullable.

Is this the only DB implementation that LLBLGen will support if I want to generate specialized "Contact" classes? Can't I create tables for each subtype and have a supertype table with a nullable key pointing to the appropriate subtype table row? If it is supported, are there reasons why I might still not want to do it this way and just stick to a flattened hierarchy?

Thanks.

Did some more reading (and re-reading) and have gleaned that flattening the table is NOT required. However, still unsure how to do it in the designer. I'll keep fiddling.

In the meantime, I'd be interested in hearing what pros/cons there are from peoples' experiences to implementing hierarchy in a flattened table versus separate tables. Note that my hierarchy (specialization) is limited to one deep (1 superclass and 1 level of about 6 specialized subclasses).

there are roughly 2 ways to model inheritance in a relational database: 1) per subtype a separate table, and the PK of every subtype points to its supertype through an FK. If setup in the db like that, LLBLGen Pro is able to find the hierarchies by itself. 2) all types in a hierarchy in a single table with a discriminator column.

all over variants are combinations.

LLBLGen Pro supports both. When to use which one? First, please check the section: Concepts -> Entity inheritance and relational models.

There, two examples are given for both hierarchy types. Option 1) is slower than option 2). This is because it requires joins between tables to retrieve data for a single entity, and also when a polymorphic fetch is performed, multiple joins are required. It has the advantage that you can model a relation on a subtype which is physically modelled as an FK from a subtype table to another table. This has the advantage that because it's in a particular subtype's table, you never will get data inserted which accidently relates a supertype to the same table (which can happen for example, if you have a bug in your code).

Option 2) does have this disadvantage, but if you define all relations on the supertype, it's not a problem (or if you have confidence enough in your team's code, it's ok too. LLBLGen Pro supports relations on subtypes in option 2) and makes sure they're saved for a subtype only, but you can always hack data into the datable of course through a stored proc or other ad-hoc query).

Option 2) is a little faster because it just has to fetch from 1 table. The price you have to pay for this is that you have a table with nullable fields for all the fields which are mapped in a subtype and not in the root of the hierarchy, because these fields are optional for some types and their data has to be stored in that table as well. This can lead to minor problems if the discriminator column's data is changed manually or through buggy code for example. LLBLGen Pro guards you for this, but nevertheless, it is possible to alter a '1' into a '2' in the discriminator column to change an entity's type, which can lead to problems if the contents of the fields for type '2' aren't filled and you expect them to be filled.

I mentioned doom scenario's here, but IMHO it's key to understand that even though you use an O/R mapper to work with the database, it can be the database is used by another application as well, which can change data which has effect on the working of your code.

Did some more reading (and re-reading) and have gleaned that flattening the table is NOT required. However, still unsure how to do it in the designer. I'll keep fiddling.

The flattening is done in the db model: you have a supertype/subtype hierarchy and you 'flatten' that hierarchy in such a way that all fields of both types become the fields of the table they're both mapped on.

In the designer you do the reverse thing: you have an entity mapped on a table, Foo, and you create a subtype for Foo. Then you specify which fields are in taht subtype and which are in the supertype. So basicly, recreating the hierarchy before it was 'flattened' into a single table

Thanks Otis. I understand now.