Client - server + Partly static tables - best approach?

[badukist]

Hi all!

I've started a new client-server project, for our sales department (truck parts sales), and I need to chose the best approach regarding lookup tables. We have huge lookup tables, with thousands of records(products table, cross reference table), 2000 customers all over the country and so... The issue is that we need to design the system for future point of sales. I don't know yet the type of connection with these POS, but I'm sure that the connection will be very slow compared with a regular LAN.
Everything need to be centralized on a single server, all transactions must be recorded at a single location (we need to know if the part is available in other POS) . These huge tables are not 100% static tables, are modified from time to time. One solution can be to "cache" locally these tables, but what if gets changed at server side? (we have an unstable economy, prices need to be changed often)

I will use Firebid SQL server and I can think about stored procedures, triggers, but I am fairly new to this field.

Any ideas?
Thanks.

 

[Dan Freeman]

Here's how I've done this in the past. Add a versions table to both the client setup and the server setup. When the table changes on the server, increment that file's version number in the server-side versions table.

On startup, have the client app compare the local versions against the server-side versions and download any file with a later version number, and update the local versions table accordingly.

You can also use this approach to deliver updated EXEs to the client workstation.

 

[badukist]

Thanks for the reply.
This will be useful if we will change the tables structure, but I was talking about adding/updating a new customer or a price change for a product. What I want to avoid is to download a large amount of data every time one enter a detail line (order, invoice, etc).
The web type interface is out of the question, they will kill me .

 

[Dan Freeman]

Who said anything about every time you enter anything?

I said check on application startup.

 

[badukist]

You're right, sorry, but even so will take a long time to download tables from server (I'm sure that the connection will be slower than a LAN), the product list has thousands of rows.
I was thinking about those super new, super nice related programs (from our suppliers) that we use to identify a truck part code in its hierarchy, with a nice web interface, but incredibly slow.

Anyway,I think I know how to solve the issue.
I will use triggers at server side to record every transaction for those partly static tables to a separate log table with an autoincrementing transaction id. At client side, the application will keep last transactions ids in a table and will compare values in this table with the last id in the server log table. If there is a difference between those two values, will download only new transactions and apply them to local table (or offline view).
Sounds simply, but I need to learn the procedural language of the Firebird server.
Thanks.