Mailing List Archive


Back to the month index Back to the list index
Brian Jepson (bjepson@ids.net)
Wed, 23 Apr 1997 10:17:17 +0000 ( ) 

Date: Wed, 23 Apr 1997 10:17:17 +0000 (   )
From: Brian Jepson <bjepson@ids.net>
Subject: Re: [mSQL] help representing hierarchies in mSQL database
Message-Id: <Pine.LNX.3.95.970423100847.159B-100000@bjepson.ids.net>

On Tue, 22 Apr 1997, Maurice L. Marvin wrote:

> the text-file processing I'm doing on a web site. Currently, we have
> a couple hundred thousand records, and some of the information
> is hierarchial. In other words, we have fields like 'country',
> 'state/providence', 'city', etc in each record. And we need to be
> able to list those hierarchies (e.g. list all of the countries, or
> all of the states/providences under a country, or all of the cities
> under a state, etc.).
>
> I've created indexes for these queries, but applying a
> 'unique' to it still takes way too long. Can anyone suggest
> a better way to handle this hierarchial information?
>
> I've considered using mSQL, and then representing
> the hierarchy on disk with directories ... but that is not ideal.
> If I remove a record (due to a typo in data-entry for example),
> and that happens to be the only reference to a given city,
> I won't know that I should remove that city from the directory
> structure (unless whenever I remove a record, I do a query
> to see if the city is present anywhere else ... which doesn't
> seem very efficient).
>
[...]

Maurice,

This doesn't sound too hard; you're not talking about building a
self-referential database using SQL, though are you? While that *can* be
involved, there are some extremely efficient ways to traverse that
information on the client side.

How are you presently representing this data? Do you have all of it in one
table? It sounds like you only have a limited depth to the tree, which is
good. It sounds like this sort of setup might benefit from normalization;
perhaps you could carry a "city id" on each row in the table, which would
point to a table consisting of city names, ids, and state/province ids.
The state/province ids in the city table would link to a table of
state/province ids and full names, and would further link to a table of
countries. That way, the information for a given city, state or country
would only occur once, and fixing the spelling of it would ripple through
the system, since they're linked on ids.

Maybe I've misunderstood your design, but I think you won't need to use
disk directories to represent hierarchical data, especially if it's only
3-deep.

Hope this helps,

Brian Jepson * (bjepson@ids.net) * http://www.ids.net/~bjepson
  Int(ra|er)net Database Developer, Author, Crypto-Fluxologist
Non-Prophet Arts Technology Flux: http://www.ids.net/~as220
WWW/Database/NT,Java/Database: http://www.ids.net/~bjepson/books

--------------------------------------------------------------------------
To remove yourself from the Mini SQL mailing list send a message containing
"unsubscribe" to "unsubscribe" to msql-list-request@bunyip.com. Send a message containing
"info msql-list" to majordomo@bunyip.com for info on monthly archives of
the list. For more help, mail owner-msql-list@bunyip.com NOT the msql-list!