Modeling Entities with Things in Common: Super-type/Sub-type?

Question

I will be using LLBLGen to generate a model, however I don't want to solve my design issues by just using whatever built-in inheritance tools it has. I want the model to make sense regardless of the OR/M tool.

So, I have several different kinds of entities that can have addresses and each entity can have multiple address (primary, mailing, etc).

Option 1, is using a Super-type/Sub-type (If you know the exact name of this sort of scheme, that would be helpful)

Tables: EntityType [EntityTypeID, EntityTypeName] (Say, Company=1, Employee=2, AnotherEntity=3)

Entity: [EntityID, EntityTypeID, OriginID] : EntityTypeID=>EntityType

Company: [CompanyID, CompanyName, ...]

Employee: [EmployeeID, FirstName, ...]

AddressType: [AddressTypeID, AddressTypeName] (Say, Primary=1, Mailing=2, etc)

Address: [AddressID, AddressTypeID, EntityID, StreetAddress1, ...] : AddressTypeID=>AddressType, EntityID=>Entity

The way this would work is that before any Company or Employee is created, an Entity record must be created with EntityType filled in appropriately. Then the Company or Employee is created and the Entity.OriginID is set to the ID of the Company or Employee. Now, Address records would belong to Entity records that will be "tied" to concrete entites view the EntityTypeID and the OriginID.

I'm a little worried about all the joins this would require. And even more so, I am a little concerned that this will be clunky in my ORM tool (LLBLGen).

Option 2, this is sort of take on option 1. I think is probably worse, but including it just the same:

Tables:

EntityType: [EntityTypeID, EntityTypeName] (Say, Company=1, Employee=2, AnotherEntity=3)

Entity: [EntityID, EntityTypeID] : EntityTypeID=>EntityType

Company: [CompanyID, EntityID, CompanyName, ...] : EntityID=>Entity

Employee: [EmployeeID, EntityID, FirstName, ...] : EntityID=>Entity

AddressType: [AddressTypeID, AddressTypeName] (Say, Primary=1, Mailing=2, etc)

Address: [AddressID, AddressTypeID, EntityID, StreetAddress1, ...] AddressTypeID =>AddressType, EntityID=>Entity

This would work similar to option 1, in that for a Company or Employee, an Entity record would be created first, setting the EntityType appropriately. Then, a Company or Employee record would be created, and the previously created Entity record's EntityID would be set on the Company or Employee record itself. The address would be tied to the entity itself. However, it seems strange because this would treat Address records like Company and Employee records, i.e. they hold an EntityID but that reference means something different that the EntityID references in the Company and Employee tables.

This seems to be a little worse. It has almost the same problems as the first but it also sort of makes the schema less intuitive, I think.

In both cases, you'd need to use some Unique constraints, such as in the Company table (EntityID should be unique in the Company table). However, you'd need to do some other programmatic check to prevent a Company and Employee record from pointing to the same entity record. I don't know if a tool like LLBLGen can do any "magic" with Option 1 by intelligently dealing with the EntityTypeID values.

So, option 3 is simple:

Tables:

Company: [CompanyID, CompanyName, ...]

Employee [EmployeeID, FirstName, ...]

AddressType: [AddressTypeID, AddressTypeName] (Say, Primary=1, Mailing=2, etc)

CompanyAddress: [CompanyAddressID, AddressTypeID, CompanyID, StreetAddress1, ...] : AddressTypeID=>AddressType, CompanyID=>Company

EmployeeAddress: [EmployeeAddressID, AddressTypeID, EmployeeID, StreetAddress1, ...] : AddressTypeID=>AddressType, EmployeeID=>Employee

This makes joins much easier to deal with. However, I don't think this is really workable. I have many entities. Not only do I have many entities but I have many entities that can have address. Also, this "address" stuff is just one example, I have many other sorts of things that are similar to addresses. I don't think creating a table for everyone of these will scale very in terms of development. Also, I'm sure I could get my ORM to let me use code to treat all of the different addresses as the same base "Address" type but, again, I don't want to rely on tricks the ORM can do.

So, is this Super-type/Sub-type thing a good idea (I suspect not)? If it is, is there a better way of going about it. If not, what are better alternatives.

Answer 1

This question is a bit wordy. You should probably try and trim it down to bare essentials. I'm more familiar with JPA than the tool you're using (in the ORM space) but JPA has this concept and it's called a mapped superclass (inheritance strategy). It's entirely valid and well worth considering.

In one system I designed lots of entities (Company, Person, Partnership, Trust, etc) had common entities (eg Legal Name) and related entities (eg Address, Contact Details) so it was natural to have a common Party supertype, which is somewhat similar to where you're going.

And yes, you do have a type column (in the supertype). In JPA terms that's called the discriminator column.

Answer 2

This topic has been written about extensively on the web. Look up "generalization specialization data modeling" or "generalization specialization database design".

In your case, Company and Employee are specialized entities that both have addresses.

In some other case, a truck and an auto might be specialized vehicles. In yet another case, Students and alumni might be specialized community members.

I could summarize here, but you're better off ging to the sources.