Click here to Skip to main content
15,867,453 members
Please Sign up or sign in to vote.
5.00/5 (3 votes)
Hello.

A friend of mine approached me about re-architecting his company's entire system. They're a somewhat small company but are starting to grow pretty quickly and their current system is outdated and takes too much manual labor to interact with. I don't normally deal with architecture of this sort but figured it would be a fun challenge and I'm looking for advice on the best way to handle this. I would greatly appreciate any help or tips you can provide. This is a rather long post, but I wanted to be as descriptive as possible, so thanks in advance for bearing with me

Just to give you some background...his company deals with reading and measuring energy usage for all apartments within an apartment complex. His client's are the apartment landlords (not the apartment renters). His company employs field technicians to review and measure energy usage using these custom developed tools. Once the technician has read all energy meters in the apartment complex, he downloads the data he gathered up in his little tool, to the company's server. This data is downloaded in the form of an ms access mdb file. Each mdb file contains about 10 tables of data and is about 600kb in size. One mdb file is created for each apartment. So if an apartment complex has 50 apartments, the technician downloads 50 mdb files to the server.

Once the data is downloaded to the server, the company has a staff of about 6 data entry employees to go into this old, outdated, custom developed application they own. The purpose of this application is to allow these data entry employees to review energy data for each apartment, and to export an apartment's data into a report (in the form of an ms word document). This ms word report is created for the purpose of providing it back to the apartment landlord. The data entry employees take these reports and manually upload them to the company website, one by one, using the website's administration backend and link them to the correct client user account. The landlords can then log into their account and review these reports. The website is currently storing this data in a sql server 2005 database on a dedicated server hosted by a 3rd party company.

I've provided these details in this images here:

http://208.74.30.126/images/architecture.jpg
[link]

Ok, so that's what his system currently does. He would now like to rearchitect much of it. Here's what he's looking to change:

-The data that's downloaded from these tools, currently downloads in the form of an mdb file. He would now like to take these mdb files and store the data into a sql server 2005 database.

-He wants me to create a new application to replace his old outdated one for the purpose of reviewing energy data and exporting reports.

-He now wants reports to be exported in pdf rather than ms word

-Once the data entry employee creates the pdf report, he wants it to automatically upload to the website.


So here are my questions that I have:


-He wants these mdb files stored in sql server 2005 on his company's server. However, the issue I see with this is he already has a sql 2005 db on his dedicated web server for his website. Wouldn't it make more sense to just store all data on the dedicated web server rather than installing another copy of sql 05 on his personal company server?

-In regards to replacing his application, he's asking me to just give him another windows forms application again. I'm thinking though that rather than create a standalone winows forms app, this may be a good opportunity for me to start a small company intranet website and within this new intranet site, I'll incorporate the features his old windows app used to perform. Would that be a better alternative?

-Oh, and my original idea was to set up the process to be fully automated so that once every few hours, an executable would run, read data from the mdb files, create new pdf reports, and automatically upload these reports to the website, however I was told we can't do this because he wants his employees to review each apartment's energy data before generating the report.
Any tips or advice you can give me as far as setting this whole process up? I want to create a process that runs as clean and smoothly as possible.

Thanks so much!
Posted

If he wants a richer application, and you want something web based, it seems like Silverlight would be the perfect fit.

Other than that, use SQL Server 2008 instead of 2005 (it's more secure and more closely tied in to reporting services), put the database server on a separate box, and use a WCF web service to store/retrieve data.
 
Share this answer
 
Comments
Keith Barrow 21-Mar-11 6:24am    
Got my 5, hopefuly the SQL Server instance is already on another box!
He wants these mdb files stored in sql server 2005 on his company's server. However, the issue I see with this is he already has a sql 2005 db on his dedicated web server for his website. Wouldn't it make more sense to just store all data on the dedicated web server rather than installing another copy of sql 05 on his personal company server?

I don't see the need for a new SQL server, but I would run this on a new database on the current sever. You should also check the security out, from the website side, you don't want the data that forms the backbone for biling being exposed...

In regards to replacing his application, he's asking me to just give him another windows forms application again. I'm thinking though that rather than create a standalone winows forms app, this may be a good opportunity for me to start a small company intranet website and within this new intranet site, I'll incorporate the features his old windows app used to perform. Would that be a better alternative?

You might meet some resistance to this, winforms can provide a richer UI than web, plus they might see the Intanet as empiure building. Personally, I think a web app would be an improvement if the UI is simple any way, as you can expose some parts to the Internet. What I would do is place a web or wcf service layer between the database and the application layer. That way you can run both winforms and webforms through these with the minimum of fuss, you prevent the need for storing Database connections on the website. The services will be a boon as the comapny expands because they increase flexibility.

Oh, and my original idea was to set up the process to be fully automated so that once every few hours, an executable would run, read data from the mdb files, create new pdf reports, and automatically upload these reports to the website, however I was told we can't do this because he wants his employees to review each apartment's energy data before generating the report.
Any tips or advice you can give me as far as setting this whole process up? I want to create a process that runs as clean and smoothly as possible.


I don't see the need for batch processing like this: when the mdb is uploaded, I'd transfer the contents into a normalised database tables. That way the company can run reports on demand with the newest information thay have, and if they need to take a manual readings they can be done without interfering with the original reading data. Again you could consider doing this through a web/wcf service, but this will slow the process down for the automatic transfer from the mbd.


All this is a matter of opinion, but that's my tuppeny's worth
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900