[OSM-dev] Alternative PostgreSQL Schema

[Patrick Weber]

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>