3

Scenario

I'm parsing emails and inserting them a database using an ORM (NHibernate to be exact). While my current approach does technically work I'm not very fond of it but can't of a better solution. The email contains 50~ fields and is sent from a third party and looks like this (obviously a very short dummy sample).

Field #1: Value 1       Field #2: Value 2
Field #3: Value 3       Field #4: Value 4       Field #5: Value 5

Problem

My problem is that with parsing this many fields the database table is an absolute monster. I can't create proper models employing any kind of relationships either AFAIK because each email sent is all static data and doesn't rely on any other sources.

The only idea I have is to find commonalities between each field and split them into more manageable chunks. Say 10~ fields per entity, so 5 entities total. However, I'm not terribly in love with that idea either seeing as all I'd be doing is create one-to-one relationships.

What is a good way of managing large number of properties that are out of your control?

Any thoughts?

gcso
  • 2,315
  • 3
  • 28
  • 50
  • 2
    What's the use-case for these data once they're in the database? i.e. what will your `selects` look like? – Michael Haren Mar 17 '11 at 02:40
  • 3
    Related: http://stackoverflow.com/questions/126271/key-value-pairs-in-relational-database – Michael Haren Mar 17 '11 at 02:41
  • Michael: Once the data is in the database it's available for the users to fetch using the client provided to them which would basically display every field on the screen. Not ideal, sure, but those are the end-user requirements. – gcso Mar 17 '11 at 03:01

3 Answers3

6

Create 2 tables: 1 for the main object, and the other for the fields. That way you can programatically access each field as necessary, and the object model doesn't look to nasty.

But this is just off the top of my head; you have a weird problem.

If the data is coming back in a file that you can parse easily, then you might be able to get away with creating a command line application that will produce scripts and c# that you can then execute and copy, paste into your program. I've done that when creating properties out of tables from html pages (Like this one I had to do recently)

5

If the 50 properties are actually unique and discrete pieces of data regarding this one entity, I don't see a problem with having those 50 properties (even though that sounds like a lot) on one object. For example, the Type class has a large number of boolean properties relating to it's data (IsPublic, etc).

Alternatives:

Well, one option that comes to mind immediately is using dynamic object and overriding TryGetMember to lookup the 'property' name as a key in a dictionary of key value pairs (where your real set up of 50 key value pairs exists). Of course, figuring out how to map that from your ORM into your entity is the other problem and you'd lose intellisense support.

However, just throwing the idea out there.

Tejs
  • 40,736
  • 10
  • 68
  • 86
2

Use a dictionary instead of separate fields. In the database, you just have a table for the field name and its value (and what object it belongs to).

mpen
  • 272,448
  • 266
  • 850
  • 1,236