Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 9 of 9 total |
Replication suggestion |
Fri, Jun 27 2008 5:15 AM | Permanent Link |
Rob Fletcher | 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 |
Fri, Jun 27 2008 6:02 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Rob
What's wrong with two columns - location & autoinc as the PK? Roy Lambert |
Fri, Jun 27 2008 1:37 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Fri, Jun 27 2008 10:38 PM | Permanent Link |
Rob Fletcher | 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 |
Fri, Jun 27 2008 10:54 PM | Permanent Link |
Rob Fletcher | 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 |
Sat, Jun 28 2008 12:25 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@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 |
Sat, Jun 28 2008 12:28 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@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 |
Wed, Jul 2 2008 8:53 AM | Permanent Link |
"Greg Bishop" | 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? |
Wed, Jul 2 2008 9:41 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
This web page was last updated on Saturday, May 4, 2024 at 12:54 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |