Thanks for the replication features in EDB.  You gave us some really great features there.
I've been doing replication since btree filer days using my own scheme.  I'm hoping to be
able to scrap my replication scheme and use the one in EDB.

The big problem in replication is avoiding duplicate PK added at different locations.  In
my replication scheme I use an Int64 as the primary key for all tables.  The high order
integer is Location ID (SalesPerson in your example).  Then the low integer is like an
autoinc that I maintain manually.  This eliminates the need for allocating key ranges and
you'll never run out of keys.  When a new record is added at a location I just combine the
LocationID and next incremental number to make a new PK. I have a table to track the last
ID used.  Its a bit chatty adding new records since I have to get the next autoinc and
then update the key tracker table with the last used key so I don't use it again.

It would be really great if we could have a special datatype or function to make these
special autoinc keys.  Perhaps i could do it in SQL using your current system but don't
have a clue how.  

Rob

Rob


What's wrong with two columns - location & autoinc as the PK?

Roy Lambert

Rob,

<< Thanks for the replication features in EDB.  You gave us some really
great features there. I've been doing replication since btree filer days
using my own scheme.  I'm hoping to be able to scrap my replication scheme
and use the one in EDB. >>

There's still one showstopper bug with the replication that will be fixed in
Build 3, and should be available by tomorrow.   The issue is that it isn't
handling updates that come back to the original publisher after going
through more than one other subscriber.  However, the fix is very simple and
solves the problem nicely.

<< The big problem in replication is avoiding duplicate PK added at
different locations.  In my replication scheme I use an Int64 as the primary
key for all tables.  The high order integer is Location ID (SalesPerson in
your example).  Then the low integer is like an autoinc that I maintain
manually.  This eliminates the need for allocating key ranges and
you'll never run out of keys.  When a new record is added at a location I
just combine the LocationID and next incremental number to make a new PK. I
have a table to track the last ID used.  Its a bit chatty adding new records
since I have to get the next autoinc and then update the key tracker table
with the last used key so I don't use it again.

It would be really great if we could have a special datatype or function to
make these special autoinc keys.  Perhaps i could do it in SQL using your
current system but don't have a clue how.  >>

My question would be the same as Roy's - is there any particular reason that
you can't use more than one column and just assign the location id as a
separate column ?

The problem with using combined identity columns like this is all of the
special operators, etc. that are needed to extract the separate values.
It's a lot of work for something that can be accomplished fairly easily
right now, but just in a different manner.

--
Tim Young
Elevate Software
www.elevatesoft.com

I didn't realize you can use two columns for the PK.  My mistake.  That would help but the
autoinc should really increment independently at each location.  After you sync that
autoinc column will take on the highest value that exists at any location.  I guess that
would work anyway.

Rob


"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote:

Rob,


My question would be the same as Roy's - is there any particular reason that
you can't use more than one column and just assign the location id as a
separate column ?

The problem with using combined identity columns like this is all of the
special operators, etc. that are needed to extract the separate values.
It's a lot of work for something that can be accomplished fairly easily
right now, but just in a different manner.

--
Tim Young
Elevate Software
www.elevatesoft.com

Multi-column PK are very difficult to deal with.  I don't think you can use dblookupfield
with them and they make SQL statements much more complex when doing joins.  So my original
request stands.  Please give us a guaranteed unique integer or int64 based PK column for
use in replication applications.  GUID, no thanks.

This would really set Elevatedb above any other database that supports replication.

Rob


"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote:

Rob,

My question would be the same as Roy's - is there any particular reason that
you can't use more than one column and just assign the location id as a
separate column ?

The problem with using combined identity columns like this is all of the
special operators, etc. that are needed to extract the separate values.
It's a lot of work for something that can be accomplished fairly easily
right now, but just in a different manner.

--
Tim Young
Elevate Software
www.elevatesoft.com

Rob,

<< I didn't realize you can use two columns for the PK.  My mistake.  That
would help but the autoinc should really increment independently at each
location. >>

It would.  All you would need to do is just make sure that the IDENTITY
column is defined as GENERATED BY DEFAULT at the "main office" where the
updates will be coming in, or simply make it a non-IDENTITY INTEGER column.
All of the "satellite offices" would simply have their own IDENTITY value
sequence going.  Because these IDENTITY values would always be combined with
a location ID, they would never cause a primary key constraint violation at
the "main office".

--
Tim Young
Elevate Software
www.elevatesoft.com

Rob,

<< Multi-column PK are very difficult to deal with.  I don't think you can
use dblookupfield with them and they make SQL statements much more complex
when doing joins. >>

You can use them with lookup fields.  Just separate the key fields with ';'.
As for joins, you're going to have joins that have multiple columns in any
database design that has any relationships deeper than one level.  Plus,
when EDB gets the multi-column index optimization in place (very soon), EDB
will always be able to use your primary keys and foreign keys as
optimizations for joins without any additional indexes required.

--
Tim Young
Elevate Software
www.elevatesoft.com

What is the recommended method for storing and retrieving a LocationID and
inserting it into records?  Should a function be created in EDB that returns
the LocationID (this would have to be customized at each location) and
called via a Trigger?  Or is there some other method such as a property in
one of the EDB components or a setting in EDB Manager?

Greg,

<< What is the recommended method for storing and retrieving a LocationID
and inserting it into records?  Should a function be created in EDB that
returns the LocationID (this would have to be customized at each location)
and called via a Trigger?  Or is there some other method such as a property
in one of the EDB components or a setting in EDB Manager? >>

The design is completely up to you, but the easiest way would be to simply
have a column in a system information table that can be referenced and used
in a before insert trigger to populate the LocationID column for any new
inserts.

--
Tim Young
Elevate Software
www.elevatesoft.com