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
by