Disconnected data

Question

Hi I'm doing my first really disconnected data application. And my problem is about handling primary (and foreign) keys. I have an application that handles customers and their orders. I need to be able to add (disconnected) new customers and new orders to them. So I'll have to find a way to give a new primary key to customers so I can use it when making new orders for them. That's not a problem (is it?) but how about persisting data to a db when the app gets in connected environment? Somehow I should give REAL primary keys and change the corresponding keys in orders table. How to do that? Thanks!

sacresp · Answer

Okay I coded my own custom solution just to find that it works well from Windows client but Compact framework doesn't support serialization. I've read misleading and contradictory information about the situation others say it does support neither binary nor soap -serialization. But also some say that it supports serialization for SOAP. How is it? Now I'm trying to send an array of custom objects and each object contains an array of other objects. How to send this to web service from Pocket PC?

sacresp · Answer

Steven I was thinking about a solution like this you just gave me the right words :) But I started to think if DataAdapter/DataSet could help me with this? This must be so common scenario that I thought MS would have done something already for me.

sjones · Answer

My suggestion would be to design the application as a disconnected data application. Don't consider the new customer and orders as customers and orders as they are entered. Consider them as Customer applicatioins and order requests until they are stored in the database. If you are using Auto Increment primary keys you really need to let the central database choose that value. When you merge your data you should use the central database's INSERT to create the customer and return the PKey. Then use the central database's INSERT for the Order Request using the new Customer's given PKey. This also gives you the benefit of allowing server side business logic determine if a Customer application is approved before creating a Customer record. It can also do some server side order validation befor creating a real Order. If you are sure that server side validation is not and will never be required I would simply suggest replacing auto increment PKeys with GUIDs.

Andrzej Wegierski · Answer

Your explanation was OK my English is poor. What about to reserve id numbers for users? user 1order 1: 0000100001 user 1order 2: 0000100002 .. user 5order 3: 0000500003 dumb but works if we have big id numbers and and small user count ... no code from scratch but some sorting not on id. Or - add user id to Orders primary key.

sacresp · Answer

Okay that's the price of simplification :) I have more complicated situation than the one I explained I will have to use unnatural primary key (that is autoincrement field) The problem is not dropping and adding primary key constraints but how to handle the values Let say I have this data already Customer ID | NAME -------------- 1 | Foo 2 | Bar Orders ID | CustID.... -------------- 1 | 1 2 | 1 3 | 2 This data is read in Pocket PC. Maybe to many different units. Then first user will add new data Customer ID | NAME --------------- 3 | foobar Orders ID | CustID -------------- 4 | 3 5 | 3 Also second user will add new data Customer ID | NAME --------------- 3 | new data Orders ID | CustID -------------- 4 | 3 5 | 3 Now there's a problem. Somehow I should be able to add all those new additions but primary key of the other new customer will have to be changed in customer table and also in orders table. Is there any automatic functionality or do I have to code this manually from scratch? Was I obscure enough in my explanation? :)

Andrzej Wegierski · Answer

What about temporary deleting foreign keys modifying primary key fields to real values and re-adding foreign keys? About customers added by different users in thesame time: may be You can find real primary field values using another customer properties eg: - tax identification number - name by something like soundex