[Zope] RE : [Zope] Sybase: How generate id keys? (surrogate keys)

[Ken Kinder]

This is less of a Zope issue and more of a DBMS issue (which is why I
can answer it)...

There are two ways to get global unique ID's:
  - Have a unique ID server
  - Find a way two servers can come up with ID's unique to each other
    without talking to each other. This is a lot of fun, and is done
    by using the network card's ID number, which is statistically
    unique. Basically, if you use the computer's network card ID, you
    can generate statistically unique ID's (this, incidentally, is the
    MS SQL Server way[*])

[*] I in now way indorse MS SQL Server. Please do not flame me for
mentioning it; I know it's a peice of crap. :)

The former is more elegant, but the latter is sometimes more practical
for distributed systems. At any rate, there is no simple solution to
this problem (many companies have had major research into this area
with little results). Your best bet is to keep this out of Zope and
just set up a unique ID server.

Have your databases (Sybase?) establish foreign links to the unique ID
server. Then, write triggers that grab ID's when rows are insert. Say
you had two tables, on two servers, that need to have unique ID's.
You would omit their ID fields upon doing an insertion, making them
NULL. You would write a trigger on insertion for these tables that
would detect the NULL id field, grab the unique ID from the unique ID
server, and insert the row with the unique ID.

In very large environments with lots of data importing, this can be
a large strain on the network and the unique ID server. If this is
the case, be sure to have good bandwidth between the servers. And
as always, bigger servers means more performance. :)

Sin Hang Kin wrote:
> 
> The unique id problem come up again and again.
> 
> The automatically generated unique id will benefit web author with
> 
> sql row id
> object id
> 
> and may be some others
> 
> With a view that zope will be run over the network with distributed storage
> across the net, I would like to suggest we implement a unique id server
> which can be a zope server serving on another port and serialize all request
> and return the globally unique id.
> 
> Does this work?
> 
> I also suggest zope can be configured to automatically get the object id
> from this service and have globally unique object ids.
> 
> Rgs,
> 
> Kent Sin
> 
> _______________________________________________
> Zope maillist  -  Zope@zope.org
> http://lists.zope.org/mailman/listinfo/zope
> **   No cross posts or HTML encoding!  **
> (Related lists -
>  http://lists.zope.org/mailman/listinfo/zope-announce
>  http://lists.zope.org/mailman/listinfo/zope-dev )

-- 
Ken Kinder
303.381.7631