Home » SQL & PL/SQL » SQL & PL/SQL » I need a design idea / querying hierarchical model (merged by LF)
I need a design idea / querying hierarchical model (merged by LF) [message #257449] Wed, 08 August 2007 07:02 Go to next message
rajvaida
Messages: 26
Registered: May 2006
Location: India
Junior Member

Hi…

I have small query with database design.

I already have two tables, Category & Sub-Category.

Category Description:

CategoryID
CategoryName


Sub-Category Description:

Sub-CategoryID
CategoryID
Sub-CategoryName


I need to have another table (test) which should allow me to store multiple Sub-Categories from Multiple Categories.

I designed like

TestID Number
TestName Varchar2
Test Category ArrayList. (This will store List of Sub-CategoryID s. )

Is it fine?

Or you can come with better design Idea.





Regards...

Raj S Vaida


Re: I need a design Idea [message #257464 is a reply to message #257449] Wed, 08 August 2007 07:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is a bad idea.
Oracle is a Relational DBMS, so keep it relational.
Don't use collection columns, keep master/details tables.

Regards
Michel
Re: I need a design Idea [message #257467 is a reply to message #257449] Wed, 08 August 2007 07:30 Go to previous messageGo to next message
user82
Messages: 49
Registered: June 2006
Location: Romania
Member
Instead of one table(test) try to use two tables:
Test

TestID Number
TestName Varchar2

and one more table like:

TBL
TblID
CategoryID
Sub-CategoryID
TestID


It's just an idea, anyway it looks more relational than using collection columns
Re: I need a design Idea [message #257573 is a reply to message #257464] Wed, 08 August 2007 12:15 Go to previous messageGo to next message
rajvaida
Messages: 26
Registered: May 2006
Location: India
Junior Member

I do agree with u michel.
Re: I need a design Idea [message #257575 is a reply to message #257467] Wed, 08 August 2007 12:18 Go to previous messageGo to next message
rajvaida
Messages: 26
Registered: May 2006
Location: India
Junior Member

i guess, we can make it more relative by removing CategoryID from TBL (New Child. Am i thinking in a right way?
Re: I need a design Idea [message #257576 is a reply to message #257575] Wed, 08 August 2007 12:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It depends what is your PK of subcategory.
Is this Sub-CategoryID alone or both CategoryID and Sub-CategoryID?
In the former case, you can.

Regards
Michel

Re: I need a design Idea [message #257582 is a reply to message #257576] Wed, 08 August 2007 12:42 Go to previous messageGo to next message
rajvaida
Messages: 26
Registered: May 2006
Location: India
Junior Member

I dont have second case... (Compound Key)
Re: I need a design Idea [message #257587 is a reply to message #257582] Wed, 08 August 2007 12:56 Go to previous messageGo to next message
jb_vincennes
Messages: 5
Registered: August 2007
Location: Paris
Junior Member
Maybe I'm wrong but the relationship

category <- subcategory

looks a lot like a hierarchy relationship.

So maybe you could have only one table with two columns

mastercategory <- subcategory

That way you could query it with the syntax:

connect by, start with, prior

like in EMP empno and mgr.

Now I'm not sure if the query cost would be less.
Re: I need a design Idea [message #257588 is a reply to message #257587] Wed, 08 August 2007 13:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't think there is any hierarchy, just 2 levels: category and subcategory and no subsubcategory...

Regards
Michel
Re: I need a design Idea [message #257594 is a reply to message #257588] Wed, 08 August 2007 13:16 Go to previous messageGo to next message
jb_vincennes
Messages: 5
Registered: August 2007
Location: Paris
Junior Member
Hi,

Here is how the table would looke like:

category..mastercategory..details
.......1............null..Im the head mastercategory
.......2...............1..Ima subcategory of 1
.......3...............2..Ima subcategory of 2
.......4...............1..Ima subcategory of 1
.......5...............2..Ima subcategory of 2

select details, level
from categories
connect by mastercategory = prior category
start with mastercategory is null

It might even be possible to add another hierarchical column subsubcategory.
Re: I need a design Idea [message #257598 is a reply to message #257594] Wed, 08 August 2007 13:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is useless if you don't have more levels.
"connect by" is a very expensive operation.
Now it depends on other columns defining category and subcategory.
If both have only name, why not.
If they have different columns then 2 tables is mandatory.

Regards
Michel
Re: I need a design Idea [message #257603 is a reply to message #257594] Wed, 08 August 2007 14:00 Go to previous messageGo to next message
rajvaida
Messages: 26
Registered: May 2006
Location: India
Junior Member

i guess hierarchical model will be a best suit for Tree strctured relationship

Example
CEO <-VP(Marketing) <-Marketing Manager1 <-Executive1
                                         <-Executive2
                                         <-Executive3

                    <-Marketing Manager2 <-Executive4
                                         <-Executive5
                                         <-Executive6

   <-VP (Sales)    <-Sales Manager1      <-Executive7
                                         <-Executive8
                                         <-Executive9

                    <-Sales Manager2     <-Executive10
                                         <-Executive11
                                         <-Executive12


[LF: added [code] tags to improve readability]

[Updated on: Wed, 08 August 2007 14:41] by Moderator

Report message to a moderator

Re: I need a design Idea [message #257608 is a reply to message #257603] Wed, 08 August 2007 14:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, that's correct.

Regards
Michel
querying hierarchical model [message #257791 is a reply to message #257449] Thu, 09 August 2007 03:08 Go to previous messageGo to next message
rajvaida
Messages: 26
Registered: May 2006
Location: India
Junior Member

It
CEO <-VP(Marketing) <-Marketing Manager1 <-Executive1
                                         <-Executive2
                                         <-Executive3

                    <-Marketing Manager2 <-Executive4
                                         <-Executive5
                                         <-Executive6

   <-VP (Sales)    <-Sales Manager1      <-Executive7
                                         <-Executive8
                                         <-Executive9

                    <-Sales Manager2     <-Executive10
                                         <-Executive11
                                         <-Executive12



can single table model help me?

id (Unique ID)
Designation/Name
Manager (Manager's Unique ID)

How can i query list Level 3 people (ex: marketing manger 1)

[Updated on: Thu, 09 August 2007 03:09]

Report message to a moderator

Re: querying hierarchical model [message #257794 is a reply to message #257791] Thu, 09 August 2007 03:18 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use "connect by".
Many examples in the documentation.

Regards
Michel
Previous Topic: How to fetch several values at a time...
Next Topic: Can REF CURSORS be used as IN parameters in STORED procedures
Goto Forum:
  


Current Time: Sat Dec 10 16:22:02 CST 2016

Total time taken to generate the page: 0.11301 seconds