[OSM-dev] Alternative PostgreSQL Schema
Patrick Weber
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:
> http://www.bretth.com/osm/pgsql_erd1.jpg
> 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
field?
> * 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.
>
> Cheers,
> Brett
>
>
> _______________________________________________
> dev mailing list
> dev at openstreetmap.org
> http://lists.openstreetmap.org/cgi-bin/mailman/listinfo/dev
>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: p.weber.vcf
Type: text/x-vcard
Size: 282 bytes
Desc: not available
URL: <http://lists.openstreetmap.org/pipermail/dev/attachments/20071024/f7820484/attachment.vcf>
More information about the dev
mailing list