CODEWITHSUNDEEP
c#databaseentity-frameworkinheritance
Sierramike
Sierramike

Reputation: 481

How to persist inherited objects to database ? (about DB architecture)

I have a conceptual problem. It's about the correct Database architecture for persisting inherited objects. I'd like to have the proper way to do it, not using EF Code First, because this is all conceived beforehand, and is not necessarily used with EF, it may be, but not sure, or not only. I need a proper way which still will be consistant with ORM approaches, like Entity Framework.

Let's simplify and say we have an object called "Transportation Vehicle" : TransVehicle, it has following properties :

Let's say now we have a "Car" inheriting from the TransVehicle, which adds following properties :

We also have a "Plane" which adds those following other properties :

So, I may have in my code a List which will contain Cars and Planes.

I suppose I could have a table "TransVehicle" with fields like "Id, Name, Color, Age", then a table "Cars" with fields like "Id, FuelType, WheelSize", and a table "Planes" with fields "Id, EngineQuantity, MaxTakeOffWeight". I could say :

  1. I read "Plane" rows and complete with informations coming from "TransVehicle" with the same ID.
  2. I read "TransVehicle" rows, and for each, see if I find a Planes record or a Cars record to instanciate the proper object.
  3. I read "TransVehicle" rows, and look an enum value (string, int ?) in a special field to have the object type, then depending on this type, get the informations from the "Plane" or "Car" table.

Which is good conceptual practice ? Have you other tips ? Which way will be easy to map in an ORM ?

Upvotes: 2

Views: 427

Answers (1)

Euphoric
Euphoric

Reputation: 12849

This is common problem of mapping an object hierarchy to relational model. You can read about it all over the web.

You basically have three options:

  • Hierarchy as single table - hierarchy is flattened into a table with discriminator column.
  • Table for each class - each class has its own table and you do join over all of them with complex queries to get the data. This is what you are doing.
  • Table for each concrete class - middle ground between the other two if you have abstract classes in the hierarchy

Most ORMs do allow you to pick which one fits the hierarchy best or even mix and match between them for maximum performance or storage savings.

Upvotes: 1

Related Questions