Marteyn

Hi..

This is my first post on here, so I hope I hit the sweet spot by posting it here. I've been given an interesting challenge and I can't seem to come up with a grand solution, so I could do with some help.

The idea is that I am to design an application for a language center. So far the center consists of two physical locations, out of which one would be considered both learning center as well as "HQ". What they want is a fairly simple application that enables them to enter student registration, classes (no planning involved) and payments made/due. Each location has their own administration; HQ is to be able to look into the data of every location.

I came up with several approaches (in terms of which language to develope in etc), but there are quite a few conditions, which I will summarize down here:

  • There is no real infrastructure, no LAN/WAN.
  • Usually all computers are turned off when the last staff member heads home. There is only a handfull of staff per location
  • The "best" pc available has barely enough capacity to run Windows XP and then some
  • Each location has an Internet connection, however it's no more than 512/256 connection with dynamic IP
  • They have a website running at a host (so no dedicated server or the likes) whicch may or may not have the option to run a database
  • All data transfers (if applicable) through the Internet must be secured in whatever way or form
  • MS Access is available on all locations. They'd prefer to make use of this as a database
  • The OS they use is Windows XP Home edition
  • Budget is limited, so there's no funds for new computers or expensive software
  • Time is limited. The application should be tested, up and running by the 3rd of January
  • Expectancy is no more than 600 students within the next 5 years

My main problem is the database. Obviously, I'd rather use one single database. MS Access should be enough for the time being regarding scalability and performance. However, I'm a little lost when it comes to the multiple physical locations. What would be the best and/or easiest way to have each location access the data Should I use one database or chose for some sort of synchronisation If so, how Should I choose to build a web-based solution rather than an executable

Any suggestion is MORE than welcome. Even the "quick and dirty" I can get away with as there's limited time and funds :)

Thank you in advance!

Marteyn




Re: Architecture General Application design challenge

Arnon Rotem Gal Oz

You can probably create a simple windows service with a timer based export utility that will "SELECT" the updated data (e.g. based on a timestamp) into a text file, encrypt and FTP it

On the HQ side have another windows service that will monitor the direcory of the FTP, decrypt and update the HQ db. This shouldn't be too complicated as I understand your description of the problem the schema would be rather simple

Make the export around noon -when people are likely to have a lunch break. alternativly you can make the export explicit (i.e. some user would have to choose "update HQ" which will run this process) - if you go with the explicit option, you might as well send the whole access file over (with 600 students it shouldn't be too big)

On normal databases you'd have better options.. :-)

HTH,

Arnon






Re: Architecture General Application design challenge

cverdon

Hi,

Since time is limited and the hardware is deficient, I would go with a fully Access based solution. Create your ui with forms in Access and have one user send the file by email to HQ whenever needed.

You can have 3 boolean fields in your table (Created, Modified, Deleted) that are reset to false after the employee emails the file. On the HQ database, have an import form that connects to the other database and updates itself.

Two weeks should be plenty to develop and test this setup.

Charles





Re: Architecture General Application design challenge

Marteyn

Hi Arnon..

Thanks for your reply. I had this too as one of the options. However, I'm not quite sure how to handle the database(s) in this case. At HQ side, would I merge the tables of the other locations into the "main" tables, or should i create a seperate table for all the accumulated data (per location; HQ being also "location A"), or would HQ just keep all info in seperate databases (per location) According to you, would each location require an "updated" database send back in return

Cheers,

Marteyn






Re: Architecture General Application design challenge

Marteyn

Hi Charles..

Thank you for your suggestion. This alternative has crossed my mind as well. Seems like the easiest way to go about it. Only problem is, they might eventually (in a year or so) decide to change the application into a web-based version. So initially I was wanting to keep the UI and the db seperate More so because they might want to migrate from MS Access to MS SQL at that time.. any suggestions on that Thanks!

Cheers,

Marteyn






Re: Architecture General Application design challenge

cverdon

Well if you do a good job of creating a nice table schema in Access, upgrading it to Sql Server will not be a problem. As for creating a web app, you'll have to rebuild the UI anyway whether it's initially in Access, WinForms or MFC does not make a difference.

Charles





Re: Architecture General Application design challenge

KondwaniHara

Arnon Rotem Gal Oz wrote:

You can probably create a simple windows service with a timer based export utility that will "SELECT" the updated data (e.g. based on a timestamp) into a text file, encrypt and FTP it

On the HQ side have another windows service that will monitor the direcory of the FTP, decrypt and update the HQ db. This shouldn't be too complicated as I understand your description of the problem the schema would be rather simple

Make the export around noon -when people are likely to have a lunch break. alternativly you can make the export explicit (i.e. some user would have to choose "update HQ" which will run this process) - if you go with the explicit option, you might as well send the whole access file over (with 600 students it shouldn't be too big)

On normal databases you'd have better options.. :-)

HTH,

Arnon

I agree with access to SQL, infact i preffer an access project to a plain MDB. However i do have a problem with UI's and the bootable options. I'd like a customized, executable application database that would run independently, truncated and sit on the desktop as an exe. I havent done it yet but im trying to work towaards it.

Anybody got any suggestions Other than that, how do you FTP your text or transfer it from your odbc to that format. what extenstion would that be

thanx






Re: Architecture General Application design challenge

Arnon Rotem Gal Oz

Marteyn wrote:

Hi Arnon..

Thanks for your reply. I had this too as one of the options. However, I'm not quite sure how to handle the database(s) in this case. At HQ side, would I merge the tables of the other locations into the "main" tables, or should i create a seperate table for all the accumulated data (per location; HQ being also "location A"), or would HQ just keep all info in seperate databases (per location) According to you, would each location require an "updated" database send back in return

Cheers,

Marteyn

It depends on your future plans - if you plan to have more than one "HQ" or maybe another level of hierarchy then you can treat it as another location

if HQ is always the HQ and the only extnension you forsee is adding other locations then there's no need for the extra complexity

Another thing to consider is how often you need just the HQ results vs. how often you need the combined information which you can also solve with an appropriate index

Arnon