Solved

Data Model: SQL Limitations and XML Use

Posted on 2012-03-16
5
357 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 39

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 39

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

Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

Question has a verified solution.

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

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

696 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