hierarchy table design question

hierarchy table design question

Post by dtong.. » Sat, 06 May 2000 04:00:00



Hello,

Let say I have the following schema.

Table A has a 1-M relationship with Table B.
Table B has a 1-M relationship with Table C.

I want to get related rows from Table C by given
Table A's primary key. The following is pseduo
code from application layer.
1. Get all related rows from B by providing A's pk
2. For each row of B, get all rows from c.

U see if I have 1..n rows of B, then I have to
loop through 1..n times to retrieve Cs. All these
code is from app layer, and therefor it is
involved the network traffic..etc.

What is the best way to solve this problem?

Thanks,
D. T.

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

hierarchy table design question

Post by dtong.. » Sat, 06 May 2000 04:00:00


Hello,

I have the following table schema:

Table A has 1-M relationship with Table B.
Table B has 1-M relationship with Table C.

I want to get all rows from C by given the primary key of A.
The following is pesudo code from application layer.

1. gets all rows of B by providing primary key of A.
2. for each row of B, gets all rows from C.

U see if I will have 1..n number of rows from B, and I have to loop
through each in order to get rows of C. It is really expensive way for
doing it.

Should I denormalized these tables? .... Any suggestion?
Thanks,
D. T.

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

hierarchy table design question

Post by Jon M » Sat, 06 May 2000 04:00:00


With SQL:
select TC.* from TA, TB, TC Where TA.pk = [whatever] and TA.pk = TB.fk and
TB.pk = TC.fk

pk=primary key
fk=foreign key

HTH,
Jon Myers


>Hello,

>Let say I have the following schema.

>Table A has a 1-M relationship with Table B.
>Table B has a 1-M relationship with Table C.

>I want to get related rows from Table C by given
>Table A's primary key. The following is pseduo
>code from application layer.
>1. Get all related rows from B by providing A's pk
>2. For each row of B, get all rows from c.

>U see if I have 1..n rows of B, then I have to
>loop through 1..n times to retrieve Cs. All these
>code is from app layer, and therefor it is
>involved the network traffic..etc.

>What is the best way to solve this problem?

>Thanks,
>D. T.

>Sent via Deja.com http://www.deja.com/
>Before you buy.

 
 
 

1. Table Design: Hierarchy

I have a table that has the following:
        Employee Name
        Employee Number
        Employee Number of immediate supervisor

I need to build an organization chart using this information (All the people
that report to a manager then senior manager, then vp).  In this
organization, it is possible for employees to report to someone of the same
level.  Can anyone recommend an efficient way of building this query or
multiple queries?

Thanks, Steven

2. Bottlenecking and disk I/O and hotspots

3. query design for XML from hierarchy table

4. Connecting FM to Other DBs

5. DESIGN: Should SQL table design mirror OO design?

6. Pivot Table & Measures Disctinct Count

7. Table Design Question: Code Tables

8. Problem with Oracle OCI Driver

9. design for an Organization's Hierarchy

10. Advice needed on Hierarchy Design

11. Design issue: nested menu options / hierarchy

12. Designing a changing Hierarchy

13. Problem with Application Design Transformer in Function Hierarchy Diagrammer in Oracle Designer 6