Abstracting Differing Implementations With A Single Table

Question

The problem I am trying to solve is not an overly complicated one but one that I would like to try and solve more elegantly than I currently am.

Problem:

We do business with multiple companies. For the sake of argument lets say each company produces motor vehicles. Each company has a differing implementation (i.e. data that must be persisted into a database). When a customer orders a car, you have no way of knowing what type of car they might buy so it is desirable to have a single looking table called 'Vehicles' that establishes the relationship between the CustomerId, a unique VehicleId, internal to our database, globally unique and some sort of composite key which would be unique in one of the many CompanyX_Vehicle tables.

An example would be:

Top level lookup table:

VehicleId
CustomerId
CompanyId
CompanyVehicleId

CompanyAVehicle Table:

CompanyAVehicleId ------> Part of composite key
CompanyId         ------> Part of composite key
...... unique implementation and persistence requirements.

CompanyBVehicle Table:

CompanyBVehicleId ------> Part of composite key
CompanyId         ------> Part of composite key
...... unique implementation and persistence requirements.

I have to disable foreign key enforcement for obvious reasons however in code (in this case C#, EF), I can perform a single query and eagerly include the necessary data from the correct CompanyXVehicle table.

Alternatively, I can omit any kind of relationship and just perform two queries each and every time, one to get the company and companyvehicle ID's and then make a call into the necessary table.

However I have a feeling there is a better alternative to either of these solutions. Does anyone have a suggestion on how to tackle this particular problem?

Answer 1

I'll put an answer........so this can be closed out (eventually and if no one else puts a better answer).

While there are several ways to do this, I prefer the DRY method.

Which is :

Base Class/Table has the PK and all the scalars that are the same.

A different sub-class(table) for the different "types" of entities. This would have the scalars that are unique to the type.

Animal(Table)
AnimalSurrogateKey (int or guid)
Species (lookup table FK int)
Birthddate (datetime, null)


Dog(Table)
ParentAnimalSurrogateKey (int) PK,FK
BarkDecibels (int)

Snake(Table)
ParentAnimalSurrogateKey (int) PK,FK
ScaleCount (int)

Something like that.

ORMs can handle this. Hand/Manual ORM can handle it....

You can query for general information about the "Animals". Or you'll have multiple queries to get all the sub-type information.

If you needed to query about basic information about just Dogs, it would be

Select AnimalSurrogateKey , Species , Birthdate from dbo.Animal a where exists (select null from dbo.Dog d where d.ParentAnimalSurrogateKey = a.AnimalSurrogateKey )

..........

The key is to follow an established "pattern" for dealing with these scenarios. Most problems have already been thought out........and a future developer will thank you for mentioning in the comments/documentation "we implemented the blah blah blah pattern to implement".

APPEND:

(using info from http://www.sqlteam.com/article/implementing-table-inheritance-in-sql-server)

That is a great article going through the scenarios. Again, you'll have to judge if EF is "good enough" or not........if it isn't, then you can manually do your ORM ... and (to get around the multiple queries concern) ... maybe test a query like this .........

select p.PersonID , p.PersonTypeID , s.EnrollmentDate , t.HireDate , par.DifficultyScore
from dbo.People p 
left join dbo.Students s on p.PersonID = s.PersonID
left join dbo.Teachers t on p.PersonID = t.PersonID
left join dbo.Parents par on p.PersonID = par.PersonID

And then you can manually do your ORM to "switch/case" off of PersonTypeID, and create your subclasses using the data unique to each subclass (noting that the rows where the type is off, you will have null values.......ex: if your subtype is "Student", then par.DifficultyScore will be null for that row. )

At some point, you're gonna have to POC (proof of concept) your choice. You have a problem, there are a handful number of ways to deal with it....you have to test it. EF may be good enough..it may not be. Thus why I go POCO first...so I can go with ado.net/old-school/idatareaders if EF isn't performing well enough.

Good luck.