0

My application has a complex schema for the domain entity. It is required use SQL Server 2008. Following are the complexities:

Domain Entity is Hierarchical: The data structure is a tree; it is nested to many levels. Few nodes in the tree are repeatable (multi-valued). For example, the entity can have unlimited addresses (home, billing, shipping, office, etc.)
Domain Entity is Expandable: The schema may expand (not shrink) in future.

Designing such a schema directly as related SQL Server tables is quite challenging. If not designing, quering will surely be so.

I am thinking of using XML type to store the domain entity records. However I have following queries:

  • Due to peculiar reporting needs, each field should be query-able (within and across entity records). This applies to even the fields that are added in future to the schema.
  • While using XML type, since I lose the structure, what is the best Data Access Layer I can design?
  • Can I use Entity Framework effectively in this situation?
  • Any best practices recommended?
Kabeer
  • 4,138
  • 7
  • 40
  • 62

4 Answers4

2

One advice: DO NOT DO IT. Seriously. You are already down a slippery slope - etter learn to use databases.

The "Domain Entity" you define here will be large, which means that querying it will be a challenge.Unlimited addresses means 100.000 plus that you ahve to be prepared to. Anyone stupid enough to ask for the xml document will get a bad surprise, as will the server.

You also loose a lot of tooling left and right - from ORM's to reporting tools. Simply because you abuse wthe XML support the databae has (which is planned to store documents, not act as pseudo database).

Your queries:

Due to peculiar reporting needs, each field should be query-able (within and across entity records). This applies to even the fields that are added in future to the schema.

In the english language, this is not a query, you know. It is also not possible.

While using XML type, since I lose the structure, what is the best Data Access Layer I can design?

Start writing SQL. By hand. Or develop your own. You are way out of what people use XML For, so no predefined tooling support.

Can I use Entity Framework effectively in this situation?

Obviously no.

Any best practices recommended?

Yes, learn using SQL Server properly. This is NOT a good approach.

TomTom
  • 61,059
  • 10
  • 88
  • 148
  • +1. Relational DB can model hierarchical models. That's what 1-N relations are for. Most real world models *are* hierarchical and nevertheless in relational DBs. – Alain Pannetier Mar 04 '11 at 06:15
  • Thanks TomTom. Since you've strongly objected, I guess you are absolutely clear that this is a wrong approach. So thanks again. I already gave adequate thought to using SQL Server conventionally and these are the roadblocks I stumbled into. 1) Implementing hierarchy using table relationships will have huge performance penalty. 2) I am unable to think of a graceful way to implement the desired schema expandability. Next, I came across http://www.sisodb.com/ which takes model-first approach for designing entities but I am not convinced to use it for my production. – Kabeer Mar 04 '11 at 06:23
  • Well, 1 and 2 are problems with all databases. It gets complex (so what ;) - this is pat of the job) and hierarchies CAN be handled somehow efficientlx, but they are a bad problem in all languages. Wont get easier with xml in terms of having good performance. – TomTom Mar 04 '11 at 06:48
  • Check 2008 data types. HierarchId can be of help, possibly. – TomTom Mar 04 '11 at 06:49
  • TomTom, I have checked HierarchyId earlier and concluded that it is good for a hierarchy that has similar record (types) in all nodes in the hierarchy. Mine has different for each node. – Kabeer Mar 04 '11 at 07:18
  • Irrelevant ;) what it aloows is fast retrieval of node info .- from there you ahve to still do the same processing. But at least you can get parts of the tree faster. Note the costs it comes wassociated with, though). – TomTom Mar 04 '11 at 07:31
2

I'm working on an abstraction layer for this: http://rogeralsing.com/2011/02/28/linq-to-sqlxml/

Code is available on https://github.com/rogeralsing/linq-to-sqlxml

You can query and select/project entities from Sql server XML columns. We are using it for evolving entity schemas while keeping old versions intact.

That beeing said, we only use it for special cases and go O/R mapping as a default approach.

Roger Johansson
  • 22,764
  • 18
  • 97
  • 193
  • Prima facie it looks very good! I appreciate your effort. This will be helpful to many. I hope you continue solidifying this piece and make it top production quality stuff. All the best! – Kabeer Mar 04 '11 at 11:46
1

In all honesty, and whilst I see @TomTom 's point, but it depends whether it is just ONE xml document or not. With 2008, you can setup XML schema's and map them to an XML field.

In contrary to TomTom 's answer, you can query an xml data field like you would do normally. Check the following SO answer for more information: https://stackoverflow.com/questions/966441/xml-query-in-sql-server-2008

You can use the entity framework (my knowledge is a bit short on this), by making some sproc's to query your data, then call the sproc from code and cast it to an XDocument. Not the prettiest way of doing it but it should work. Note: there might be another way of doing this, but that's as far as my knowledge of EF goes, perhaps add a tag for EF in the question?

I guess you need to come back to us and state whether you need to query 1 xml document (in which case an relational DB would possibly be better, suggested by @TomTom) or multiple documents (which I would use SQL Server to do the work. Chances are you'll have some way of linking these documents together anyway).

XML indexing tips can be found here

And some more info on XML in SQL 2008 here

Hth,

Stu

Community
  • 1
  • 1
Stuart Blackler
  • 3,732
  • 5
  • 35
  • 60
  • I want to query across many XML documents (like many rows in a table). Querying & searching within a single document, I agree is very well supported already. – Kabeer Mar 04 '11 at 11:48
  • Yes you can do that. A brief example can be found: http://stackoverflow.com/questions/1509529/grabbing-values-from-multiple-xml-rows-in-sql-ms-server-2005 if you need any more let me know – Stuart Blackler Mar 04 '11 at 12:26
0

Did you try SisoDb? If you have any questions about it I would happily answer them. Use the contact form at http://www.sisodb.com or ping me at Twitter.

slang
  • 626
  • 7
  • 26
Daniel
  • 8,133
  • 5
  • 36
  • 51