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:
-
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.
-
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.
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?
Absolutely, as long as you know what you are doing, and you know what it is good for. For example, modeling your object graph as an XML document won’t let you change parts of that XML concurrently by different users. And it will make SQL queries more complex when querying for a property within the XML (note that SQL Server has a lot of direct XML support). On the other hand, you will gain the flexibility you already mentioned in your post. So when the advantages outweigh the disadvantages, there’s no reason to stick dogmatically to “only relational” or “only XML”.