Advice needed on Hierarchy Design

Advice needed on Hierarchy Design

Post by Sam » Fri, 28 Feb 2003 12:24:11



Hi,

I'm designing a business management application where there is a
hierarchical relationship among business objects. In order to organize and
categorize them, I want use the following levels:

Namespace
  Group
    Type
      Object

Somethimes, I don't have groups or types under a certain namespace; I just
have objects. My question is how do I design this in my database? I was
thinking about having a TypeID field in my Objects table. Obviously, the
problem is I may not have a Type. How can I design the table so that the
levels of hierarchy are not rigid. I should be able to have as many or as
few levels of hierarchy as necessary.

I don't have any code to give you guys as I don't have anything yet. Thanks.

Sam

 
 
 

Advice needed on Hierarchy Design

Post by Joe Celk » Fri, 28 Feb 2003 12:41:23


Search "nested sets model" in the archives. I also have a book on TREES
& HIERARCHIES IN SQL later this year.  

--CELKO--
 ===========================
 Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

Advice needed on Hierarchy Design

Post by Joel Aske » Fri, 28 Feb 2003 12:57:46


Sam:

Representing hierarchical relationships in a database may not be your real
problem here.
If I was holding a design review with this idea on the table, I would be
asking you the following questions:

- Why do you need to be this abstract?
- Since this appears to be code metadata, what do you propose storing at
each level?
- What will the data be used for?
- If the answer to the above question is "generating code stubs", how do you
propose to keep data and code reconciled?
- If the answer to the above question is "documenting design", then why are
you rolling your own CASE tool, unless you work for a tools vendor?

I sympathize with the urge to be abstract and data driven - sometimes this
works out great, and I frankly don't know your application.  However, if
you're in the application programming business, consider that you may be
creating a set of data that is of little worth to maintain in a database.

There is a place for high-level abstraction in a database, but somehow this
doesn't strike me as one of the useful ones.

You may have great answers for the above questions, so consider that
hierarchies can be represented with:

- Adjacency lists ( store self-referential parent ID in each row )
- Directed Graph ( see Joe Celko's frequent posts in this newsgroup on the
subject )
- XML and XPATH with the SQL 2000 features ( including SQLXML ).


Quote:> Hi,

> I'm designing a business management application where there is a
> hierarchical relationship among business objects. In order to organize and
> categorize them, I want use the following levels:

> Namespace
>   Group
>     Type
>       Object

> Somethimes, I don't have groups or types under a certain namespace; I just
> have objects. My question is how do I design this in my database? I was
> thinking about having a TypeID field in my Objects table. Obviously, the
> problem is I may not have a Type. How can I design the table so that the
> levels of hierarchy are not rigid. I should be able to have as many or as
> few levels of hierarchy as necessary.

> I don't have any code to give you guys as I don't have anything yet.
Thanks.

> Sam

 
 
 

Advice needed on Hierarchy Design

Post by Joel Aske » Sat, 01 Mar 2003 02:12:27


Sam:

Just in case you're using .NET to implement your new system, remember that
code metadata is available via Reflection.


Quote:> Hi,

> I'm designing a business management application where there is a
> hierarchical relationship among business objects. In order to organize and
> categorize them, I want use the following levels:

> Namespace
>   Group
>     Type
>       Object

> Somethimes, I don't have groups or types under a certain namespace; I just
> have objects. My question is how do I design this in my database? I was
> thinking about having a TypeID field in my Objects table. Obviously, the
> problem is I may not have a Type. How can I design the table so that the
> levels of hierarchy are not rigid. I should be able to have as many or as
> few levels of hierarchy as necessary.

> I don't have any code to give you guys as I don't have anything yet.
Thanks.

> Sam

 
 
 

1. Design Advice Needed

Hi, I'm working on a project that involves testing
material used in developing solar cells.  There are
several different tests that are completed and there is
always the possibility more will be added.  All of these
tests have 2 things in common:

1.  A TestResultId that ties the test to a TestLocation
record - for sake of clarity, this is an x,y location on a
specific roll of treated stainless steel - but that's
irrelevant to my question.

2. A TestResultValue

However, the format of the result will vary depending on
the test data being captured, i.e., it could be text, a
floating point number, whatever depending on which test is
being recorded.

I really don't want to create a separate table for each
type of test simply because the format of the value
varies.  I'm thinking I could just define my result as a
varchar(100) and save all test results as text and then
convert them as needed in my objects that create and
update and read the test results.  Is my thinking flawed?  
Is there something I'm overlooking that is going to get us
into trouble if I take this approach?

Thanks

Mark

2. Accessing Paradox from python

3. Need table relationship design advice

4. Moderated Data Modelling List

5. Advice Needed - Table Design - PKs

6. OLAP & http in Standard Edition

7. table design - need expert advice

8. CLI0156E

9. Need Db design advice

10. Need DB design advice

11. Need advice on database design tool

12. need advice on table design

13. Table design advice needed please