[OSM-dev] Database Schema (was Re: [OSM-talk] Oh dear - planet has duplicate id's)

[SteveC]

On 23 Jun 2007, at 15:36, Tom Hughes wrote:

> In message <19E75E3A-30F1-4BA2-BD73-96B6DBDB41DF at asklater.com>
>           SteveC <steve at asklater.com> wrote:
>
>> On 23 Jun 2007, at 15:07, Tom Hughes wrote:
>>
>>> In message
>>> <7679c25f0706230653m427dbbcfv68fafa95dc5faaf6 at mail.gmail.com>
>>>           "Ray Booysen" <raybooysen at rjb.za.net> wrote:
>>>
>>>> Sorry if I'm jumping in late, but I'm finding it a bit strange
>>>> that on the
>>>> most important tables, we're missing primary keys.  Seems almost
>>>> the first
>>>> thing you do when designing tables is chosing you primary key and
>>>> setting
>>>> the index.
>>>
>>> Yes, we know. It's a bug that we want to fix!
>>>
>>> I'm not quite sure if it has always been like that or if the problem
>>> was introduced by the rails port, but it doesn't really matter now.
>>
>> its a hangover from when all data was in one table per object, now we
>> have current_nodes etc...
>
> Ah right.
>
>> there shouldn't be primary keys on the nodes, segments and ways
>> tables as they have multiple entries from the history, of course.
>
> Well ways can have one on id+version as it has an explicit version

not id+version+sequence_id ?

> column. Unfortunately nodes and segments don't and rely on a timestamp
> which means there can be (and are in some cases) more than one copy
> with the same timestamp in the history table.

yeah so a index makes sense, just not a primary key index :-)

have fun,

SteveC | steve at asklater.com | http://www.asklater.com/steve/


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/dev/attachments/20070623/75ae0133/attachment.html>