Re: How to display all the content of the database?

Just a wild thought: why would one like/need to have so many different tables (one for each level)? I would rather have one table which in each record would hold its own ID, its level number (say: section = 1; department = 2; ...), its name and the ID-number of its parent (and any other info you need to store for this entity.

Traversing such a structure or selecting would be rather simple with the help of some well chosen SQL-queries:
SELECT name FROM organization WHERE level = 3 and parent = 10
would give you all level 3 entities which are overseen by entity 10.

Finding out how deep your organization is, is as simple as finding the maximum of the level numbers.

You could store meta-data about the levels (not the individual entities) in a separate table, the ID of which is of course the level number, ... etc. etc.

No need to access the meta-data of the database and you can implement it in any type of database and use DBI::DBD in a portable way.

Of course if the structure of your organization is much more complex, you can always try to catch it in an XML-structure, which is more flexible but more difficult to use.

CountZero

"If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law

Comment on Re: How to display all the content of the database? Download Code

Replies are listed 'Best First'.

Re: Re: How to display all the content of the database?
by osama (Scribe) on Jan 09, 2003 at 22:22 UTC

I agree with you completely, seems that the person who designed the database has no database experience....

In Oracle I would do:

create table organization(
orgid number not null primary key,
ordparent number,
orgname varchar2(40) not null);

alter table organization add constraint fk_parent foriegn key (orgpare
+nt) references organization(orgid);

-- start inserts for organizations
insert into organization  ....etc.
-- end inserts for organizations

create table orglevels(
levelid number not null,
levelname varchar2(20) not null);

insert into orglevels values (1,'Sector');
insert into orglevels values (2,'Department');
insert into orglevels values (3,'Division');
[download]

You would probably get lots of data by running a query like this one (untested):

select orgid,orgname,orgparent,level,l.levelname from organization o s
+tart with orgparent is null connect by prior orgid=orgparent
left join orglevels l on l.levelid=level
[download]

[reply]
[d/l]
[select]


more useful options
	PerlMonks