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:
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!