Solved

Data Model: SQL Limitations and XML Use

Posted on 2012-03-16
5
346 Views
Last Modified: 2012-03-17
I have a data schema/model issue that is stretching my knowledge of implementation in SQL and I think I will need to implement in XML.  would like some thoughts as well as best practices if XML is th e way to go.

I will generisize the problem:

Object A is the main object, it can contain the following types (max quantities in brackets)

Thing 1 (unlimited), Thing 2 (unlimited), Thing 3 (1), Thing 4 (1)

Further Thing 1 can contain the following types (max quantities in brackets)

Thing 1 (unlimited), Thing 2 (unlimited), Thing 3 (1), Thing 4 (1)  [Yes, same as the above]

This can go on and on depending on how the use chooses to design the object.

Given I do not know in advance how deep (I am more concerned with depth then width) this problem space can go I do not see any easy way to structure, store and retrieve in SQL 2008.  XML and its nodes seems to be workable.  The tangential thought is the ability to srialize the XML data into a class I can use in the application.

Any and all comments welcome.
0
Comment
Question by:cadwal01
5 Comments
 
LVL 2

Expert Comment

by:winston33
ID: 37731682
While genericizing is helpful, in this case, I think a specific example may work better. For example, do Things 1-4 have multiple attributes each? In most cases, you will be able to solve the issue with proper schema design without the need to throw everything in an XML column.
0
 
LVL 38

Expert Comment

by:Aaron Tomosky
ID: 37731871
At a very high level, I basically suggest another table for each one->many relationship.
So table objects
Objectname, thing3, thing4

Table things
Objectname, thingname, data
A, thing1, yellow
A, thing1, browns
A, thing2, above
A, thing2, left

You could also harve a thing1 table with just the thing1 maps then also have a thing2 table with those maps.

If you can give us a less generic example I'm sure we can do it.
0
 
LVL 10

Accepted Solution

by:
pfrancois earned 300 total points
ID: 37732390
The main problem is to serialize this tree structure for storing it into SQL tables. Keep this in mind when analyzing the problem.

There is an analogy between this problem and a population of human beings. If the A object is a city or whatever, each thing could correspond to one person, having (unlimited) children.

Do not focus on the city (object) but on the person (thing). Make a databases of persons (things) with pointers to the parents (which are always unique), not to the children (can be multiple in the case of thing1 and thing2).

For finding in the database all the children of a person (the things), just find all the persons (things) having a certain parent. With a good choice of keys in SQL, you can do this in a very efficient way.

In this way, it must be possible to build the XML tree from SQL and vice-versa.
0
 

Author Comment

by:cadwal01
ID: 37732570
OK a more concrete example.  

A product is the top level.
It may have the following elements: subassemblies, components, labor, others costs

A subassemply may have other subassemblies, components, labor, other costs

Imagine a server rack as the product

It has a HP server (subassembly), KVM switch (component), build labor, shipping costs

The HP Server has a physical server (component), operating & other SW (component), install/config labor

And so on ...

The table option is concerning because I have no idea in advance how many levels deep I would be going  I could guess 5 max, but what happens if the user gets to six?

The population analogy is very close to the problem.  I just foresee some delicate coding and schema to accomplish.  If anyone has seen an example of implementation please let me know.
0
 
LVL 38

Assisted Solution

by:Aaron Tomosky
Aaron Tomosky earned 200 total points
ID: 37732793
Pfrancois has the right direction I think.
We have something like this in use with two tables:
Items:
Itemid, parentid, itemname, anyothersinglestuff

Itemdata
ItemDataId, itemid, key, value

Because our stuff changes I also add created as updated times, feel free to add any other columns that are always there.

These two tables allow you to have anything as an item and any item can reference a parent.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question