|
|
You're in the
sql server misc
group:Nasty ISPish hierarchy
sql server misc:
and ask questions like 'show me every top level parent that contains this child'. The speed gain is enormous. That being said, you may want to stick with the adjacency model as nested set is much more complicated to maintain. Hmmm. Well here's my suggestion, which will probably be shot down by someone else who knows their relational theory much better than I do. Both models are inherently directional, but in this case you may want to remove directionality. I would use the adjacency model, but denormalize it (it's not necessary, but to clarify the concept) by putting a trigger on the table on insert that inserts an opposite if it does not exist (and likewise, a trigger on delete that deletes any opposite). So when you insert a row like so... ParentID ChildID 1 2 You will get this: ParentID ChildID 1 2 2 1 Now you have directionless 'links' instead of parents and children, and you will leave it to your query or app to decide what is 'upstream' and 'downstream' based on the questions you ask. Or course you will need to put in a mechanism, which you probably would have needed to do anyway, in your queries that gracefully deals with loops while they're spooling up result sets to retrieve things like 'get the shortest path between these two nodes' and 'get everything connected to this node'. At first you may want to put the bulk of your data-gathering strategy in your app and keep the sql calls numerous and simple, i.e. ' "SELECT ChildID FROM MyTable WHERE ParentID = " & ParentID ' so you can play around, debug, and optimize before writing the final queries. Good luck, Bob [quoted text, click to view] "Bennett F. Dill" <ben@dillfamily.org> wrote in message
news:jHH7b.37$uJ2.28@fe3.columbus.rr.com... > Hmm... I was hoping the diagram would do that... > > Basically, there are many different routers at the different customers... > Which then connect to their own circuits, those circuits in turn connect to > the core router (every thing goes through the core router), then back out to > the terminal devices via more circuits.. > > ID Name ParentID ChildID RouterID > 1 Cust 1 Router Null 2 Null > 2 Cust 1 circuit 1 3 Null > 3 Core router ??? ??? Null <---- many > parents & children > 4 Circuit 3 4 Null > 5 Cust 1 terminal 4 Null 1 > 6 Cust 2 router Null 7 Null > 7 Cust 2 circuit 6 3 Null > 8 Cust 2 Circuit 7 9 Null > 9 Cust 2 terminal 8 Null 6 > > That I think might help explain the diagram??? I'm sorry its' so basic, > that's why I'm hear asking for help... > > So here, is the core router really the root? If so do I simply make > everything a child / leaf of the core router? > > That would kind of put a router which is really upstream of a terminal next > to the terminal... I'm just grey in this area... > > Thanks again, > Ben > > "Jens Süßmeyer" <jsuessmeyer@(Remove_ME]web.de> wrote in message > news:OWw$lB7dDHA.1944@TK2MSFTNGP12.phx.gbl... > > As loong as you can´t explain the symbols or parts of > > your network (also the relationship) we (as far i) can´t > > help you. You have to describe where there are the 1-1 1-n n-m > > relationsships. > > > > HTH, Jens Süßmeyer. > > > > > > > > "Bennett F. Dill" <ben@dillfamily.org> schrieb im Newsbeitrag > > news:F0H7b.33$uJ2.15@fe3.columbus.rr.com... > > > Ok here's the deal. I'm doing some work for an ISP / telcom. They > > > basically provide communication from the customer (banks mostly) to the > > > terminal (ATM / POS). So if you imagine a typical network, we're > looking > > at > > > something like... > > > > > > Cust 1 Cust 2 > > > | | > > > Router Router > > > | | > > > | ------------- > > > | | | > > > Circuit Circuit ISDN > > > | | | > > > ------------------------------------ > > > | > > > Core router > > > | > > > ----------------------- > > > | | > > > Satellite Circuit > > > | | > > > Terminal Terminal > > > > > > Now, this is of course a very very small representation of the network. > > > There are multiple routers and circuits at each customer. Now each > router > > > can handle say 20 terminal devices... One of the many things, but the > > main > > > objective here is to be able to determine how many down stream devices > are > > > affected by an upstream device. So basically I need to store parents > all > > > the way up to the ISP level for each terminal, but also associate a > > terminal > > > with a router... > > > > > > The problem... I'm no SQL guru, but I think I can handle this project. > > I've > > > been looking into nested sets and the adjacency model. I'm not seeing > > much > > > about parent - child, many-to-one - one-to-many... > > > > > > Anyway, I hope my sad diagram helps you visualize the network. Any > advise > > > or reading is greatly appreciated. I'm still in the design phase here > and > > > would really like to do it *right* from the onset... > > > > > > Thanks in advance any help! > > > Ben > > > > > > > > > > > >
Ok here's the deal. I'm doing some work for an ISP / telcom. They
basically provide communication from the customer (banks mostly) to the terminal (ATM / POS). So if you imagine a typical network, we're looking at something like... Cust 1 Cust 2 | | Router Router | | | ------------- | | | Circuit Circuit ISDN | | | ------------------------------------ | Core router | ----------------------- | | Satellite Circuit | | Terminal Terminal Now, this is of course a very very small representation of the network. There are multiple routers and circuits at each customer. Now each router can handle say 20 terminal devices... One of the many things, but the main objective here is to be able to determine how many down stream devices are affected by an upstream device. So basically I need to store parents all the way up to the ISP level for each terminal, but also associate a terminal with a router... The problem... I'm no SQL guru, but I think I can handle this project. I've been looking into nested sets and the adjacency model. I'm not seeing much about parent - child, many-to-one - one-to-many... Anyway, I hope my sad diagram helps you visualize the network. Any advise or reading is greatly appreciated. I'm still in the design phase here and would really like to do it *right* from the onset... Thanks in advance any help! Ben
Hmm... I was hoping the diagram would do that...
Basically, there are many different routers at the different customers... Which then connect to their own circuits, those circuits in turn connect to the core router (every thing goes through the core router), then back out to the terminal devices via more circuits.. ID Name ParentID ChildID RouterID 1 Cust 1 Router Null 2 Null 2 Cust 1 circuit 1 3 Null 3 Core router ??? ??? Null <---- many parents & children 4 Circuit 3 4 Null 5 Cust 1 terminal 4 Null 1 6 Cust 2 router Null 7 Null 7 Cust 2 circuit 6 3 Null 8 Cust 2 Circuit 7 9 Null 9 Cust 2 terminal 8 Null 6 That I think might help explain the diagram??? I'm sorry its' so basic, that's why I'm hear asking for help... So here, is the core router really the root? If so do I simply make everything a child / leaf of the core router? That would kind of put a router which is really upstream of a terminal next to the terminal... I'm just grey in this area... Thanks again, Ben [quoted text, click to view] "Jens Süßmeyer" <jsuessmeyer@(Remove_ME]web.de> wrote in message news:OWw$lB7dDHA.1944@TK2MSFTNGP12.phx.gbl... > As loong as you can´t explain the symbols or parts of > your network (also the relationship) we (as far i) can´t > help you. You have to describe where there are the 1-1 1-n n-m > relationsships. > > HTH, Jens Süßmeyer. > > > > "Bennett F. Dill" <ben@dillfamily.org> schrieb im Newsbeitrag > news:F0H7b.33$uJ2.15@fe3.columbus.rr.com... > > Ok here's the deal. I'm doing some work for an ISP / telcom. They > > basically provide communication from the customer (banks mostly) to the > > terminal (ATM / POS). So if you imagine a typical network, we're looking > at > > something like... > > > > Cust 1 Cust 2 > > | | > > Router Router > > | | > > | ------------- > > | | | > > Circuit Circuit ISDN > > | | | > > ------------------------------------ > > | > > Core router > > | > > ----------------------- > > | | > > Satellite Circuit > > | | > > Terminal Terminal > > > > Now, this is of course a very very small representation of the network. > > There are multiple routers and circuits at each customer. Now each router > > can handle say 20 terminal devices... One of the many things, but the > main > > objective here is to be able to determine how many down stream devices are > > affected by an upstream device. So basically I need to store parents all > > the way up to the ISP level for each terminal, but also associate a > terminal > > with a router... > > > > The problem... I'm no SQL guru, but I think I can handle this project. > I've > > been looking into nested sets and the adjacency model. I'm not seeing > much > > about parent - child, many-to-one - one-to-many... > > > > Anyway, I hope my sad diagram helps you visualize the network. Any advise > > or reading is greatly appreciated. I'm still in the design phase here and > > would really like to do it *right* from the onset... > > > > Thanks in advance any help! > > Ben > > > > > >
As loong as you can´t explain the symbols or parts of
your network (also the relationship) we (as far i) can´t help you. You have to describe where there are the 1-1 1-n n-m relationsships. HTH, Jens Süßmeyer. "Bennett F. Dill" <ben@dillfamily.org> schrieb im Newsbeitrag news:F0H7b.33$uJ2.15@fe3.columbus.rr.com... [quoted text, click to view] > Ok here's the deal. I'm doing some work for an ISP / telcom. They > basically provide communication from the customer (banks mostly) to the > terminal (ATM / POS). So if you imagine a typical network, we're looking at > something like... > > Cust 1 Cust 2 > | | > Router Router > | | > | ------------- > | | | > Circuit Circuit ISDN > | | | > ------------------------------------ > | > Core router > | > ----------------------- > | | > Satellite Circuit > | | > Terminal Terminal > > Now, this is of course a very very small representation of the network. > There are multiple routers and circuits at each customer. Now each router > can handle say 20 terminal devices... One of the many things, but the main > objective here is to be able to determine how many down stream devices are > affected by an upstream device. So basically I need to store parents all > the way up to the ISP level for each terminal, but also associate a terminal > with a router... > > The problem... I'm no SQL guru, but I think I can handle this project. I've > been looking into nested sets and the adjacency model. I'm not seeing much > about parent - child, many-to-one - one-to-many... > > Anyway, I hope my sad diagram helps you visualize the network. Any advise > or reading is greatly appreciated. I'm still in the design phase here and > would really like to do it *right* from the onset... > > Thanks in advance any help! > Ben > >
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
July 2007
August 2007
September 2007
October 2007
| home · blog · groups | Questions? Comments? Contact the dn |