Duplicating hierarchical table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Duplicating hierarchical table

Author

Topic

Phibian
Starting Member

32 Posts

Posted - 2002-04-04 : 16:06:27

Hi, I asked this question (in a rambling sort of way) before, but didn't get a response that was complete enough to help. Since then, I'm actually solved the problem in another way, but the overall question is still bugging me:

Say I have a table with the following structure:

ID | Parent_ID
1 0
2 1
3 2
4 3
5 0
6 5
etc

Say I want to duplicate ID 1 and all of its children (ie 2, 3, 4).
Can anyone provide me with a SQL statement that will do this given the sample data?

Thanks!

Mary

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2002-04-04 : 16:26:12

When you say duplicate do you want to retrieve the values or 'copy' them elsewhere.

Either way I believe you will have to write a recursive stored procedure that takes the values requested and spits them to a temp table. The stored procedure can then return the temp table to you with the values of:

Jay99

468 Posts

Posted - 2002-04-04 : 16:37:00

I tried to answer this for you before. This will only work if you know the maximum 'depth' of the hierarcy. If you don't know that, you will need to implement a recursive algorthm like yakoo suggested.


create table #temp (id int,parent int)
insert #temp values(1,0)
insert #temp values(2,1)
insert #temp values(3,2)
insert #temp values(4,3)
insert #temp values(5,0)
insert #temp values(6,5)

declare @id int
set @id = 1

select
    id
from
    #temp p --parent
where
    p.id = @id
union
select
    c.id
from
    #temp p
    inner join #temp c --child
        on p.id = c.parent
where
    p.id = @id
union
select
    gc.id
from
    #temp p
    inner join #temp c
        on p.id = c.parent
    inner join #temp gc --grandchild
        on c.id = gc.parent
where
    p.id = @id
union
select
    ggc.id
from
    #temp p
    inner join #temp c
        on p.id = c.parent
    inner join #temp gc
        on c.id = gc.parent
    inner join #temp ggc -- great-grandchild
        on c.id = ggc.parent
where
    p.id = @id

Jay
<O>

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-04 : 16:44:23

To be honest, I think the difficulty is due to the fact that the table structure you're using is not conducive to what you want to do with it (based on your original post):

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=14282

I know you said that you absolutely can't change it, but I can't understand WHY. It's obviously not allowing you to do what you want easily, and other than saying "I can't change it", you haven't explained the reason. If it's not working for you, then why CAN'T you change the design?

IMHO, the Attribute/Value concept is perfectly valid and very flexible, but I don't understand the method in which you're implementing it. In your structure, objects (workstation, mouse) are considered "attributes", and are equivalent to a true attribute like "quantity". To me, this is akin to saying "I have an asset over here with a 'baseball' attribute, and over here I have another asset with a 'sofa' attribute". Baseballs and sofas aren't attributes, they're objects. I don't know if you see it that way, but that's how your table design is treating them.

Now, sofas and baseballs have different attributes (sofa: color, seating capacity, fabric; baseball: manufacturer, price, stitching) that don't apply to all other objects. I don't see any problem in separating objects and attributes into 2 tables (like I recommended in the original thread) I also DO NOT see any BENEFIT in consolidating them into 1 table like you have now. I can't help thinking that your current structure is making you jump through hoops to do something that is extremely easy under another design.

For these reasons, I also don't see any benefit to having a quantity attribute. If you treat these assets as objects, which you should, then you would list each object separately, and the inferred quantity would be 1. It does not matter if you have 3 identical workstations, or if there is an implied grouping to them, you should list them as 3 separate rows (objects) in an objects table. In doing this, you've already "duplicated" the assets; more accurately, you're logging them as they should be logged. Any relationship between one object and another (workstation-mouse) is independent of its hierarchy or grouping, which it as it should be.

As far as building the full hierarchy for an object and its children/granchildren, the design I suggested is no more difficult to traverse than what you need to do with your current design. If anything, it's easier because you can completely avoid true attributes (quantity, color, etc.) when needed by simply excluding the attributes table from your query. You'd have to use some elaborate WHERE clause to accomplish this with your current design. And I truly don't know how you can properly handle adding or removing attributes from the hierarchy with a single table design. You can easily break an attribute chain by removing a parent object.

I hope this helps. I don't want to sound like I think you're wrong, but I truly believe you'll be better off with another table design from the one you have now, unless you can spell out in more detail why this design is carved in stone.

Phibian
Starting Member

32 Posts

Posted - 2002-04-08 : 15:25:27

Hey folks, thanks for all your help.

I think I understand Jay's solution a bit better now. And I see how a stored procedure would work well for the recursion. Really, it's the concept of variables combined with the self join that I hadn't come across before (not to mention that I was unable to find much documentation on self joins). The real problem is that SQL (particularly if trying not to use "SQL Server" specific SQL) really sucks at recursion. Anyway, as I said, I've solved the problem in a completely different way so it was really an academic problem but I do appreciate the example.

----
Going back to the table design argument, Rob, when I asked this question I was really trying to get an answer about the specific concept of 'copying' part of a hierarchy from one place to another (and the table design that I have currently has nothing to do with anything).

Say I did separate my attributes and assets - but still wanted to 'copy' one asset with all its children (with no depth limit on the hierarchy). Then, I'd still have had the same problem... (Which is why I was still academically interested in possible solutions, even though it is a moot point...)

Or say it wasn't an asset system at all. Say it was a forum instead where you have parent message, and child messages (and as many children's children etc as you want). Now say that you want to be able to take an entire sub-thread and make a copy of it somewhere else (okay - so I don't know why you'd want to, but say you did). It's the same problem, and the asset/attribute debate is really a "red herring" distracting you from actually answering the question

--------
Not that I mind having the asset/attribute debate. I'm probably wasting my time trying to convince you of this, because you are as convinced that I'm wrong as I am convinced that you aren't seeing the whole picture, but please believe me when I say that the current design is easier for everything except the duplication / grouping issue (which is a very small part of the overall application).

So why is the design carved in stone:

1) Changing the table design would require rewriting most of the application.

Since the application works very well as is, it would be inefficient, time-consuming, costly etc etc to do that just to add one feature. And the client wouldn't want to pay for that (always a good reason to leave it alone).

2) For some reason, neither you nor Jay really seems to "get" how the structure translates to real world.

First of all, I'm not really treating a mouse as an attribute. A mouse is a (sub) asset. But I am treating attributes (such as quantity) as (sub) assets too. (So not everything is an attribute - instead they are all assets <g>) Believe me though, this is CONVENIENT when listing any given asset and all its children, including attributes. It means I can just say:

SELECT * FROM Asset WHERE Parent_Asset_ID=x. And then because attributes don't have asset statuses, I can display attributes in one location, and subassets in another. Trust me, it's very easy, it's very fast (important) and it works. If I want to avoid retrieving attributes, rather than an "elaborate where clause" I add one condition -> and AssetStatus_ID<>0.

Having attributes in another table would work too, but it would require joining with that table and would be slightly slower. The main benefit thus is SPEED. I'm not saying that the put attributes/assets in two separate table approach is wrong, just that my approach has proven to be EASIER with the *exception* of the asset splitting. Personally, I think speed and ease of use are pretty important benefits...

And just separating assets and attributes wouldn't solve the basic problem that I was having which is that I don't know the ID of the duplicated asset until after it has been created.

Secondly, your statement "It does not matter if you have 3 identical workstations, or if there is an implied grouping to them, you should list them as 3 separate rows (objects) in an objects
table." is actually not true.

The whole point is that *because* the assets are part of a group (due to being identical), the user doesn't want to think about them separately. Anything that happens to the group of assets, happens to ALL of the assets. Forget the workstation/mouse example and think about something in a larger quantity instead. Say you have 5000
packages of CDs. There's no way that you want to have 5000 rows of CD packages (at least we don't!!!), nor does the user want to see those assets broken down into 5000 entries. They only care about QTY 5000, and one asset with various attributes (for instance, of type "Small Parts", with size of 25 CDs/package etc etc). The attributes are going to be identical across each of the 5000.

However, occasionally, you do care about one of those 5000 CD packages. Say you sell 3000 to client A, you open 1 package (thus the size changes) and you keep the rest in inventory. You still don't want 5000 entries cluttering up your database (not to mention your UI). But it would be useful to have three entries to describe the differences in the three types of assets (ie 3000 sold to client A, 1 with new size, 1999 with all the original properties). Hence the concept of duplicating/splitting etc the assets *as necessary* (this is really key).

Now, if I added a quantity field to the asset table, it might simplify things (certainly would from a conceptual point of view).

However, that's not an ideal option, because (unfortunately) not all assets have a quantity... And again, it wouldn't solve the basic problem, which was that I didn't know the ID of the duplicated asset until after it had been created...

Anyway... there would have to be a pretty compelling reason in order to change the structure, and I just haven't come across one yet...

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-08 : 16:29:45

Ahhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh

Now I see what you're getting at! I completely agree with you. And if you can accept my apologies, I offer them.

Sorry about being cranky, I'm just so used to dealing with incomplete information that I tend to fall back on previous experience, instead of stretching out first. If I had thought in terms of "inventory" rather than "assets" then it would've made sense right away.

However, one thing I do want to clarify:

quote:
For some reason, neither you nor Jay really seems to "get" how the structure translates to real world.

Just to be fair to myself, and probably Jay, I have NEVER seen an inventory- or asset-tracking system where a part number and its quantity were kept on separate rows from each other. I've seen dozens of these type of systems and NONE of them did that, EVER. If they measured the quantity of something, it was stored on the same row or it was not stored at all. I think a lot of the confusion on my part comes from this; it is a completely ALIEN way for me to look at data. I don't want to belabor the point or suggest that your design is wrong, but in the "real world", that's what I've seen, exclusively.

I'm done! Thanks truly for spending the time and typing to fill us in on your design, most people wouldn't have bothered.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources