[OSM-dev] Alternative PostgreSQL Schema
p.weber at ucl.ac.uk
Wed Oct 24 14:34:56 BST 2007
Just a few comments from a general database perspective. I dont have
PostgreSQL experience, but some SQL Server development experience. So
forgive my ignorance if I point out obvious stuff.
Brett Henderson wrote:
> 2. No duplication of data between history tables and current tables
> other than the primary key and the visible flag.
So the current tables are physical tables, not Views? Why the
duplication of data, you could just create a View on the main
nodes/way/relation table with only the current visible members?
> 3. Transaction support. This comes for free with PostgreSQL.
> An ER diagram is available here:
> I don't have a script yet because it was quicker to draw a diagram than
> to actually create all of the table definitions.
> The history tables cntain the only full copy of data.
> Current tables only allow a single version of an entity to be active at
> one time.
geo spatial indexing sounds great. how far could one then go with
spatial queries as well?
> * Current tables require a visible field which only exists so that only
> visible data can be added to the current table. Can't think of a better
> way to do this.
Could you just get rid of visible all together and rely on the version
> * Circular references between relations are possible but I believe that
> is practically impossible to prevent in the data model itself.
> This schema supports the existing 0.5 logical data model, just in a
> different physical db representation. I'm also playing with an
> alternative schema that results in unique ids across all data types and
> reduces the number of tables significantly but that may be too drastic.
> Any thoughts appreciated.
> dev mailing list
> dev at openstreetmap.org
-------------- next part --------------
A non-text attachment was scrubbed...
Size: 282 bytes
Desc: not available
More information about the dev