1

Is it acceptable to model a very complex object graph using XML in the database, but leave the rest of a system in relational tables?

I'd like to gauge opinion on this as I've hit a bit of a conundrum.

Many thanks

--------------------

Background

I'm building a financial application which, although not at all large in terms of data storage ( <= tens of megabytes initially), will have a very complex data model.

Specifically, users will be working within the context of a "Project" entity, which will contain various sub-entities and lists. The users will be able to add formulae to diverse parts of this object graph and they will be continuously recalculated in realtime.

The complexity comes in two parts:

  1. No matter where in the class hierarchy a formula is located, it will be able to reach any other part of the same parent project entity using a path-like syntax. This will include the results of other formulae, and I will be implementing all of this with an in-memory dependency tree on a middle tier.

  2. I'd like all the formulae to have the same basic structure, metadata, etc. To accomplish this, all formulae will be represented by the same class structure in code, regardless of which part of the data graph they are attached to.

I'd much rather have the whole thing in a document - it would mean much less time spent fussing with database design, and I'd be able to have a much richer object model for the same amount of effort. I'm worried though about relational integrity - I'd still want to store my static data in relational tables.

Technologies

  • SQL Server 2012.
  • .Net 4.5 (via C#)
  • Entity Framework (database first) on the middle tier
  • Simple auto-generated DTOs over WCF for services
  • WPF with Prism on the client.

I've not considered NoSQL storage ideas, largely because I don't have enough experience of them for a project with tight timelines. I've also not considered Entity Framework code-first, for the same reason.

James Cane
  • 123
  • 1
  • 8
  • 1
    It's a .. mixed bag. I use XML for parts of the model which are very complex/detailed or need the utmost flexibility; with few exceptions I ensure the XML data is "opaque" from the RA (triggers, computed columns, and materialized views can all help ensure DRI with XML, but it becomes a mess if basing it around this!) I would recommend *against* using XML for top-level entities or top-level entity information. (SQL Server has quite good support for dealing with XML, but in my experience, it seems like it should primarily be used as "fall back" or for modeling data "not in" RA.) – user2864740 Mar 13 '14 at 07:49
  • (SSMS and a good Schema Diff tooling setup are my primary means of ensuring a flexible Schema that can be cleanly and incrementally updated; I *much* prefer and use Model First.) – user2864740 Mar 13 '14 at 07:54
  • Thanks for both of your replies. I should add that I'm actually using a database model project to design the database. Well, initially anyway. Once it goes to production and gets full of data, I'll switch to a scripted change approach, but at the moment I can throw away the database and create it from scratch in a handful of seconds with arbitrarily large changes. – James Cane Mar 13 '14 at 07:57

0 Answers0