Re[4]: Database design goals...

["John C. Peterson" <jcp eskimo com>]

On Tue, Nov 13, 2001 at 09:47:36AM -0500, mike_phillips urscorp com wrote:
> John,
> 
> I don't have any problems with the comments you've put forward. For a 
> [...snip...]
> 
> I'm on vacation next week, but I'll start work on the generic interface 
> next and getting it to work with the existing files and functions. The 
> question here is whether we produce something that just emulates the 
> existing data queries (which means each new function added would require 
> an addition to the interface code), or whether we write a generic-generic 
> interface code, i.e. something that will interpret a query string and 
> return the values. This would make future development a lot easier, and 
> actually make the interface api simpler, but make the interface itself 
> more complex as it would have to interpret the query and translate it into 
> the format for the required data file. 

   I don't have any real attachment to the current db interface
functions. A re-design makes more sense since we really needed
some new capabilities that aren't currently there.

> The use of index files is definitely beneficial but should be transparent
> to the developer/user, the db interface should take care of all this.

   Oh, that was always my intent.... It takes some reasonable level
of experience with database design to construct effective methods
for access, etc. So you don't want these messy and potentially
confusing details exposed to the programmer if there's no reason
for it.

> I would like to get to the point that a developer can simple use
> something  like the following:
> 
> struct db_values_t { 
>         fname char[16] ; 
>         fvalue char[256] ; 
> } ; 
> 
> struct db_values_t *ret ; 
> 
> ret = db_query("get airport_details 17N") ; 
> [...snip...]

  Off the top of my head, the design of the generic interface
may wind up being driven by the "back end" where the desired
functionality is most difficult to implement. (A design that
just represents the "lowest acceptable common denominator".

> Once the syntax of the query is decided upon (a simple version of sql may 
> do the trick), it would be easy for a developer to add new functionality 
> to fplan and not worry about the database side. e.g. to get a boundary 

  A simple version of SQL might work OK, but I'm a little suspect
that it might get messy for the case of the embedded "back
ends". Keep in mind they don't have any sort of query language
to translate to, so the language would need to be mapped to
the home brewed methods of access. A simple SQL might be more
attractive, but it might need to be "dumbed down" so much that
there's little benefit?

> list of navaids:
> 
> ret = db_query("get navaids range N39,W80 - N35,W75") ; 
> 
> The other, simpler way is to create individual functions for each type of 
> query, i.e.
> 
> ret = db_query_aprt("17N") ; 
>  
> ret = db_query_navaids("range N39,W80 - N35,W75") ; 
> 

  I'd like the former, as they are much more flexible from the
(fplan) programmer's perspective than individual functions.
Here's a more functional approach;

ret = db_lookup(rec_type, rec_field, field_value);

  rec_type = "APT", "NAV", "AWY", etc.

  rec_field = "IDENT", "NAME", etc...

  field_value = "KJFK", "JOHN F KENNEDY INTL"

ret = db_nearby(ident, distance);
  a function that returns a list of nearby airports and navaids

Access schemes for the embedded case will need to be designed
for functions like db_nearby() or for lookups of multiple
records based on names with wild cards, or distances, etc.

For the case of nearby airports and navaids you could create a
set of buckets, one for each 1/2deg by 1/2deg square that
contains a list of pointers for all the airports and navaids in
that region. First determine the 4 nearest buckets, get all the
contents and then lookup the actual data for only those that
meet the distance metric.

There's other desirable styles of access I've forgotten, but
they still appear to be limited in number..


> Anyway, a lot of the details will come out in the wash once we start 
> putting this together.

  For sure, thanks again for your thoughts Mike...

John

-- 
 ___|___ | John C. Peterson, KD6EKQ | Try Linux for Intel x86, because all
  -(*)-  | mailto:jcp eskimo com    | of the best things in life are free!
  o/ \o  | San Diego, CA  U.S.A     | See http://www.linux.org/ for info