Solved

Data Model: SQL Limitations and XML Use

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

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.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the fileā€¦
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now