@smi-mark - Thank you for the response and valuable feedback. If you don't mind, I'll provide some additional information about the application below to see if your recommendations would still apply. I left them out originally because when I previewed my original post I was thinking "OMG, many may not want to read an entire 4 page novel to get the background on my issues. :) I thought it would be best to provide it as the conversation evolved but I appreciate your thoughts.
@Ward - I am greatful for your response as well. When I read your blogs and your responses to others I find them extremely valuable because your ideas, approach and detailed reasoning behind your answers helps me learn a tremendous amount each time. Thank you for the suggestion on the BigInts, I completely forgot about using that as my identity type. In my initial conversations with Derrick Chung and Albert Wang, my original tables structures used Guids because I wouldn't have to worry about the collisions/identity synchronization.
I probably spooked myself while I was doing some research and I read the number of neverending debates between using Ints vs Guids. Many pointed to the size of Guids and potential speed issues that may arrise as the data grows in addition to being able to identify some type of order (Seq Guids would help but since I'm creating from many distributed sources, this wouldn't work for me), however my simple dteCreated column tells me the creation sequence (for the application's needs) regardless of what the keys say.
------ Some additional Data Information -----
Equipment, Parts, RemanParts, Kits - This subset of data which is used by all aspects of the application (much of it referenced and provides details) is global across all companies (my customers). Using Ward's concept, this information makes up the "Equipment Catalogue (specs, market pricing, etc) along with all the oem-parts (parts) and aftermarket parts (remanparts) available for each piece of equipment. There might be somewhere from 25K-30K different equipment records along with any parts/remanparts/kits associated with each one. The parts/remanparts/kits can be shared across multiple equipment records that belong to the same family/series.
Rate of Change: Maintained by my group, initially there will be a lot of new records, but over time this will settle down to a few changes every 3-6 months. The WPF client application will primarily update their local database with this information so that off-line references can be done.
Dealer Profile - At a high level, a dealer (my customers, originally called a Company above) will create a profile where they selected all the equipment/parts/remanparts/kits that they service/support/offer supplies for. This profile contains additional information such as their cost of service, base line pricing models and min/max service thresholds. This is basically a subset of the Equipment information.
Rate of Change: Typically created initially by my group but then adjusted by the managers at the Dealer (company). Changes may happen frequently and will be done through a module that is exposed on certain deployments of the WPF client application. The ability to make changes to the dealere profile while off-line is a goal.
Account Profile - A dealer will build a "profile/map" of each account. This typically happens as the first step in trying to acquire the account. An account profile is similar to the equipment portion of a Dealer Profile but it contains a list of all the equipment (referenced to our master catalogue) that an account currently has/uses across all of it's physical locations.
The only difference is that this list is per physical existence of a piece of equipment. ie: An account might have 3 installations of Equipment Model A so there would be 3 AccountEquipment records all referencing Model A but with additional attributes unique to that install (ie: serial #, location, usage stats,etc).
There are several other supporting records that will be created (potentially off-line in addition to the list of equipment) such as locations, contacts, pricing, orders, service requests and "stat" captures". The stats for each piece of equipment contains about 20-30 additional attributes such as consumable levels, total runtime, errors, etc. determined by a third party vendor. This information is a 1-to-1 against each account equipment record. Pricing at this level is not always 1-to-1. Pricing only exists if the sales rep/dealership arranged a special pricing model to help "seal the deal" for this account. This overrides the Dealer base line pricing model.
Rate of Change: Any change to an account's profile (location, equipment list, pricing, stats, etc) can be done remotely while off-line by a sales rep who has access to this account. During an "assessment", practically all the account equipment records, stats records, locations, contacts, etc will be created in one session (possibly offline).
@Ward - The idea of separating data that will change more often from the base data that doesn't change as often makes perfect sense.
The more I think about this, I believe I can utilize the DevForce caching system and serialzed cache files for practically all of my local client changes. I don't think the data will become too large while they are waiting for connectivity.
Not knowing when someone could be off-line, I would like to make sure that sales reps/techs could still access account information/assessments/proposals for any account they have access to, even if they have not previously requested it through the User Interface. Let's say they're offline and another sales reps calls them to ask them to review it (since they are a secondary rep or something). I don't want to implement a "local cache database" simply based on what they have explicitly requested previously through the application.
It seems like I want to implement something similar to Outlook using an Exchange Server running with Offline Cache support enabled. I might have only looked at the Inbox, but Outlook has also sync'd any changes to my calendar, notes, etc during the connected session.
Of course I could be missing the boat (and maybe you've presented it in the form of a huge tanker I still missed it), but could the background "sync" process simply be a "puller" that happens after the DevForce change caches have been posted to the backend? I'm not sure if this is what you were suggesting or if you were suggesting that my local database only store the fairly static data and not the all of the data (my thought).
Using this approach, I would eliminate the need for the local identity synchronization issues and lets DevForce handle all changesets via Serialized Cache Files. Once pushed up, I could locate all server changes (which would include my most recent ones commited) via the Change Tracking ability in SQL Server or query for any create/mods on the server since my last sync date/time. I can refresh my local database instance with changes / new records introduced by other sales reps including myself since they would have never existed in my local database. This would meet the goal of having access to all accounts while off-line at any point in time (all of the data, including assessments, stats, etc).
I feel like I've taken a couple steps toward what you guys were suggesting but I'm still not going all the way because of my desire to store much of the data in the local database still and not treat it as "temporary" storage. I think my suggestion would eliminate my need to keep track of my local changes and worry about merging them with incoming changes because they would be handled on two separate paths.
Would I have the newid generation done in the application and remove the newid() in the dbschemas so that whatever is generated is maintained as the PK? Or have DevForce create a temp Guid for the cached entities, allowing the server's DB schema to apply a permanent sequential guid?