[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Data Model: SQL Limitations and XML Use

Posted on 2012-03-16
5
Medium Priority
?
373 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 1200 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 800 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 Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

650 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