2

I'm working on a datamodel that has a set of types that share some common fields such as Id, Name, Description. To be more concrete:

The Document class has a list of Attributes. These Attributes are in our domain, types like String, Integer, DateTime and more "complex" types such as Address and lists of String, Integer, Address, etc. Now, in my head I would model the Attribute classes so that I have an abstract base class (AttributeBase) that contains common attributes (Id, Name, Description) and then have the more specific attributes in the appropriate sub classes. E.g. StringAttribute (Value), IntegerAttribute(Value), AddressAttribute(Street, etc), StringListAttribute, IntegerListAttribute, AddressListAttribute. We are talking about 15-20 different sub classes. But how would you model those classes in the database? Would you choose TPH, TPT or TPC? I have read about the performance hit when choosing TPT and EF 4.1 and having one massiv table for TPH doesn't sound right to me even if the performance is better. We are talking about potentially 10000 - 1000000++ rows of data in the tables.

Do you have any first hand experience when it comes to these scenarios? I would really like to here from you on this subject.

OKB
  • 715
  • 3
  • 14
  • 30

1 Answers1

6

TPH doesn't sound good from DB perspective but it is actually a storage model used by many products using this type of metadata. Example is SharePoint (2007) which has all data in single table - it follows more complex model but the basis is the same. I don't like SharePoint and the way how it stores data but by thinking about the problem I didn't find better solution to model generic solution supporting huge amount of data.

TPT and TPC will looks more normalized in DB but their usage will result in slow application. Moreover normalizing IntAttribute to table with Id, Name and Description and second table with Id and integer Value is IMHO over normalized in most cases.

Btw. if you are doing anything with documents and you want to assign properties to documents you should definitely check Sharepoint because it allows that out-of-the box.

Ladislav Mrnka
  • 360,892
  • 59
  • 660
  • 670
  • Thanks ladislav! Have you had any performance issues regarding TPH in you solutions? – OKB Oct 12 '11 at 10:55
  • TPH has the smallest performance impact on queries when comparing to TPC and TPT. EF itself add large performance impact so thinking about TPH as performance issue is not needed once you are happy with EF. – Ladislav Mrnka Oct 12 '11 at 10:57