Re: abstract data types?

Hi Josh et al,

Sorry for the tardy reply, and thanks for your comments.  Any 
suggestions or pointers on robust database design will be greatly 
appreciated.

Josh Berkus wrote:

> Jim,
> 
> 
>>> I'm trying  to figure out what support PostgreSQL
>> 
>> offers for SQL99 
>> 
>>> abstract data types.
>> 
> I'm a little curious why what you're attempting couldn't be
> done with two columns rather than inventing your own data
> type.  As somebody who often rescues databases gone bad,
> composite data types have not earned a warm place in my
> heart ...
> 
> -Josh Berkus


What we are attempting is the storage of vector data for a geographical 
(or spatial) information system in a database.  We hope to base the 
implementation on the upcoming standard from the  ISO TC/211 committee.  
More information  can be found at http://FMaps.sourceforge.net/ - the 
webpages need a major  revamp so the best place to look for current 
developments is in the mailing list archive.  A good source of info can 
be found at http://gdal.velocet.ca/projects/osvecdb/index.html, 
especially relevant is the comparison of the SQL/MM, OGC, and ISO TC/211 
standards (http://gdal.velocet.ca/projects/osvecdb/comp-mm-ogc-tc211.pdf ).

To answer your question, it is a bit hard to say at the moment as the 
design  schema for our project has only just been started.  The draft 
versions of  the ISO standard that I have seen use an object oriented 
data model, so  to me it makes sense to try and keep the database schema 
as close as possible to this (minimise data impedance).

Briefly, at its' simplest the schema will probably use a two tier approach.

Tier 0ne
------
The original data stored in the most flexible way that we can think of, 
with associated metadata tables.

Tier Two
-------
These will effectively be persistent views on the T1 tables structured 
for efficient access by client applications.  OK, as far I know no such 
beast as a persistent view exists in the SQL standards, but that is 
probably the best way to describe what I have in mind.  Using views as 
currently implemented in PostgreSQL would probably not be viable as it 
is likely that, if multiple spatial reference systems are defined on a 
area of interest, reprojection of the geometry objects would be a 
performance killer.

cheers,
John