Data Model: SQL Limitations and XML Use

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.
cadwal01Asked:
Who is Participating?
 
Pierre Fran├žoisConnect With a Mentor Senior consultantCommented:
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
 
winston33Commented:
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
 
Aaron TomoskySD-WAN SimplifiedCommented:
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
 
cadwal01Author Commented:
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
 
Aaron TomoskyConnect With a Mentor SD-WAN SimplifiedCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.