What's The Right Way To Do This?
-
I'm building an retail appliction where the software will be installed locally on the client. There will be a local copy of the database, and the software will do CRUD operations on it. Then, each day the software will send inserts/updates/deletes to the server. On the server the database is identical to the local DB. The reason for this design is that if the user cannot connect to the server, the software has to still be able to run. The customer cannot be stopped from using the software in their store because they cannot connect to the server. To facilitate this I'm going to use composite primary keys. The first part of the key is the CustomerId, and the second is the Id of that table. So, for an Order row the key would be CustomerId + OrderId. This way, when the data is pushed to the server I will have unique keys across all customers. The question is this... Assume a new customer downloads and installs the software. How do I get the CustomerId? I'm guessing that during setup I would connect to the server and get the next available CustomerId, and to do that I would have to store the CustomerId to the server's table right then & there. But again, what if they can't connect? Anyone have a better idea? Any problems with this design? Thanks
If it's not broken, fix it until it is
-
I'm building an retail appliction where the software will be installed locally on the client. There will be a local copy of the database, and the software will do CRUD operations on it. Then, each day the software will send inserts/updates/deletes to the server. On the server the database is identical to the local DB. The reason for this design is that if the user cannot connect to the server, the software has to still be able to run. The customer cannot be stopped from using the software in their store because they cannot connect to the server. To facilitate this I'm going to use composite primary keys. The first part of the key is the CustomerId, and the second is the Id of that table. So, for an Order row the key would be CustomerId + OrderId. This way, when the data is pushed to the server I will have unique keys across all customers. The question is this... Assume a new customer downloads and installs the software. How do I get the CustomerId? I'm guessing that during setup I would connect to the server and get the next available CustomerId, and to do that I would have to store the CustomerId to the server's table right then & there. But again, what if they can't connect? Anyone have a better idea? Any problems with this design? Thanks
If it's not broken, fix it until it is
Why not use a GUID for the id field? It's better because it doesn't rely on knowledge of server values.
*pre-emptive celebratory nipple tassle jiggle* - Sean Ewington
"Mind bleach! Send me mind bleach!" - Nagy Vilmos
CodeStash - Online Snippet Management | My blog | MoXAML PowerToys | Mole 2010 - debugging made easier
-
Why not use a GUID for the id field? It's better because it doesn't rely on knowledge of server values.
*pre-emptive celebratory nipple tassle jiggle* - Sean Ewington
"Mind bleach! Send me mind bleach!" - Nagy Vilmos
CodeStash - Online Snippet Management | My blog | MoXAML PowerToys | Mole 2010 - debugging made easier
I thought of that, but then I figured I didn't want to be transmitting alot of GUID's to the server. Seems like alot of data each trip.
If it's not broken, fix it until it is
-
I'm building an retail appliction where the software will be installed locally on the client. There will be a local copy of the database, and the software will do CRUD operations on it. Then, each day the software will send inserts/updates/deletes to the server. On the server the database is identical to the local DB. The reason for this design is that if the user cannot connect to the server, the software has to still be able to run. The customer cannot be stopped from using the software in their store because they cannot connect to the server. To facilitate this I'm going to use composite primary keys. The first part of the key is the CustomerId, and the second is the Id of that table. So, for an Order row the key would be CustomerId + OrderId. This way, when the data is pushed to the server I will have unique keys across all customers. The question is this... Assume a new customer downloads and installs the software. How do I get the CustomerId? I'm guessing that during setup I would connect to the server and get the next available CustomerId, and to do that I would have to store the CustomerId to the server's table right then & there. But again, what if they can't connect? Anyone have a better idea? Any problems with this design? Thanks
If it's not broken, fix it until it is
-
I thought of that, but then I figured I didn't want to be transmitting alot of GUID's to the server. Seems like alot of data each trip.
If it's not broken, fix it until it is
-
I'm building an retail appliction where the software will be installed locally on the client. There will be a local copy of the database, and the software will do CRUD operations on it. Then, each day the software will send inserts/updates/deletes to the server. On the server the database is identical to the local DB. The reason for this design is that if the user cannot connect to the server, the software has to still be able to run. The customer cannot be stopped from using the software in their store because they cannot connect to the server. To facilitate this I'm going to use composite primary keys. The first part of the key is the CustomerId, and the second is the Id of that table. So, for an Order row the key would be CustomerId + OrderId. This way, when the data is pushed to the server I will have unique keys across all customers. The question is this... Assume a new customer downloads and installs the software. How do I get the CustomerId? I'm guessing that during setup I would connect to the server and get the next available CustomerId, and to do that I would have to store the CustomerId to the server's table right then & there. But again, what if they can't connect? Anyone have a better idea? Any problems with this design? Thanks
If it's not broken, fix it until it is
One way to deal with IDs in disconnected systems is to have the server pre-allocate a number of them and distribute them in advance to the clients (give each of them a chunk); the clients would then hold "empty records" they can fill up at their own pace. BTW: Whatever your solution will be, you must consider that a customer could interact with more than one retail point, so sooner or later you may need a way to coalesce some records. :)
Luc Pattyn [My Articles] Nil Volentibus Arduum
-
I thought of that, but then I figured I didn't want to be transmitting alot of GUID's to the server. Seems like alot of data each trip.
If it's not broken, fix it until it is
-
I'm building an retail appliction where the software will be installed locally on the client. There will be a local copy of the database, and the software will do CRUD operations on it. Then, each day the software will send inserts/updates/deletes to the server. On the server the database is identical to the local DB. The reason for this design is that if the user cannot connect to the server, the software has to still be able to run. The customer cannot be stopped from using the software in their store because they cannot connect to the server. To facilitate this I'm going to use composite primary keys. The first part of the key is the CustomerId, and the second is the Id of that table. So, for an Order row the key would be CustomerId + OrderId. This way, when the data is pushed to the server I will have unique keys across all customers. The question is this... Assume a new customer downloads and installs the software. How do I get the CustomerId? I'm guessing that during setup I would connect to the server and get the next available CustomerId, and to do that I would have to store the CustomerId to the server's table right then & there. But again, what if they can't connect? Anyone have a better idea? Any problems with this design? Thanks
If it's not broken, fix it until it is
Bloody hell, we built this system in the 90s and it was a PITA then! We used GUIDs and if they cannot connect they cannot download - have them register when they download - if they do not install then delete them at some later time. There is nothing wrong with the ID/ID structure it is just more work to maintain and you are saddled with a composite primary key. This is where I got my loathing of GUIDs they really are irritating to debug with. We also segregated the data so the user only replicated their own information locally, not the entire database!
Never underestimate the power of human stupidity RAH
-
Bloody hell, we built this system in the 90s and it was a PITA then! We used GUIDs and if they cannot connect they cannot download - have them register when they download - if they do not install then delete them at some later time. There is nothing wrong with the ID/ID structure it is just more work to maintain and you are saddled with a composite primary key. This is where I got my loathing of GUIDs they really are irritating to debug with. We also segregated the data so the user only replicated their own information locally, not the entire database!
Never underestimate the power of human stupidity RAH
I got to thinking about my design some more.... My application will be installed in a retail environment. My initial design calls for the software to work even if there's a loss of internet connection. So I thought of installing a local copy of SQL for my app to work with. Each night the app's update service would then transmit any data to the server that's not already been sent. But then I got to thinking, it's possible for there to be multiple PC's in any given store. What if there's no network? I could offer to install a basic peer to peer network, and make the first PC the server, then all the other PC's could hit the DB there. I went into this thinking thatI can't assume an internet connection. Do you think this is unreasonable in today's world? Should I build the app so that all data resides in the server and require them to have an internet connection? It would solve both the internet and network the issues. Your thoughts? Any thoughts on this?
If it's not broken, fix it until it is
-
I got to thinking about my design some more.... My application will be installed in a retail environment. My initial design calls for the software to work even if there's a loss of internet connection. So I thought of installing a local copy of SQL for my app to work with. Each night the app's update service would then transmit any data to the server that's not already been sent. But then I got to thinking, it's possible for there to be multiple PC's in any given store. What if there's no network? I could offer to install a basic peer to peer network, and make the first PC the server, then all the other PC's could hit the DB there. I went into this thinking thatI can't assume an internet connection. Do you think this is unreasonable in today's world? Should I build the app so that all data resides in the server and require them to have an internet connection? It would solve both the internet and network the issues. Your thoughts? Any thoughts on this?
If it's not broken, fix it until it is
It depends on your market, small outlets I would think. However even small outlets often have multiple POS. A large, multi shop company will require the data to be consolidated but the POS operation is decidedly local. I would build a local environment (WPF on the desktop) and make sure you cater for a replication scenario where the local server can replicate its data to HO. Personally I see absolutely no requirement for the internet in such an environment.
Never underestimate the power of human stupidity RAH
-
It depends on your market, small outlets I would think. However even small outlets often have multiple POS. A large, multi shop company will require the data to be consolidated but the POS operation is decidedly local. I would build a local environment (WPF on the desktop) and make sure you cater for a replication scenario where the local server can replicate its data to HO. Personally I see absolutely no requirement for the internet in such an environment.
Never underestimate the power of human stupidity RAH
Ya, it's a tough design decision. I'll sleep on it over the weekend. Thanks
If it's not broken, fix it until it is