I have been looking for some advice for a while on how to handle a project I am working on, but to no avail. I am pretty much on my fourth iteration of improving an "application" I am working on; the first two times were in Excel, the third Time in Access, and now in Visual Studio. The field is manufacturing.

The basic idea is I am taking read-only data from a massive Sybase server, filtering it and creating much smaller tables in Access daily (using delete and append Queries) and then doing a bunch of stuff. More specifically, I use a series of queries to either combine data from multiple tables or group data in specific ways (aggregate functions), and then I place this data into a table (so I can sort and manipulate data using DAO.recordset and run multiple custom algorithms). This process is then repeated multiple times throughout the database until a set of relevant tables are created.

Many times I will create a field in a query with a value such as 1.1 so that when I append it to a table I can store information in the field from the algorithms. So as the process continues the number of fields for the tables change.

The overall application consists of 4 "back-end" databases linked together on a shared drive, with various output (either front-end access applications or Excel).

So question is: Is this a common way how data driven applications work?

Each back-end database is updated with fresh data daily and updating each takes around 10 seconds (for three) and 2 minutes(for 1).

Project Objectives: I want/am moving to SQL Server soon. Front End will be a Web Application (I know basic web-development and like the administration flexibility) and visual-studio will be IDE with c#/.NET.

Should these algorithms be run "inside the database," or using a series of C# functions on each server request. I know you're not supposed to store data in a database unless it is an actual data point, and in Access I have many columns that just hold calculations from algorithms in vba.

The truth is, I have seen multiple professional Access applications, and have never seen one that has the complexity or does even close to what mine does (for better or worse). But I know some professional software applications are 1000 times better then mine.

I have been completely on my own and need some guidance on how to approach this project the right way. What would be a good approach to follow ?

closed as too broad by user40980, user22815, user53019, durron597, gnat Sep 28 '15 at 22:52

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • Is the database used for a data source? Or just to temporarily store data while processing? Is the output need to be stored for future use or do you just store data because you use the db as output? If you don't need the data stored you might just need an idempotent processing service, which is a fantastic implementation if you're requirements can support it. – Jimmy Hoffa Oct 14 '12 at 18:49
  • Yes it is pretty much temporary data. Data is pulled into Access in the morning, a script is run and all calculations are performed, and a final set of tables are created that have links to front-end applications. However, I do have several "archive" tables that store factory specific data; this is because it is dumped from the main Sybase database every 6 weeks. – user1745022 Oct 14 '12 at 18:56
  • You say basically the whole app exists in the "business layer" as data calculations and aggregation, this can often best be done by an idempotent service where it takes all the data it needs, and then returns the calculations desired. Read about idempotence at en.wikipedia.org/wiki/Idempotence and read about side effects at en.wikipedia.org/wiki/Side_effect_(computer_science) you can probably do all of this processing without side effects from your description. You would then have two pieces of software, one that retrieves the data and hands it to the processor, and the processor – Jimmy Hoffa Oct 15 '12 at 1:19

In short, it will really depend on usage of the application by end-users and the requirements.

However, you may strongly benefit from two general approaches that are parts of enterprise level application development, which i have experienced so far. Namely, you may have multi-tier application with 3 layers: Presentation Layer, Business Layer and Data Layer. or as an alternative you may have 2 layers, where you shift your Business layer to the Data Layer.

  • Presentation Layer: will be responsible for "User Interface Process Components"
  • Business layer: will be responsible for Business Components, Workflows, Entities, Service Interfaces
  • Data Layer: mainly responsible accessing data through Data Access Components, Service Gateways, Foundation Services (like Security, Operational Management and Communication).

As a universal truth, One diagram is worth of several description pages.

enter image description here


There are several places where you can get some thought on how to design your application in a scale-able way. One of the reference points is Enterprise Solution Patterns Using Microsoft .NET

  • 1
    This is interesting. The presentation layer is being kept as short and concise is possible. The Business Layer is most interesting to me because that is where all the algorithms and calculations are. Actually thats really the only point of the Access databases, data manipulation, not storage. – user1745022 Oct 14 '12 at 17:12
  • In addition, you need to minimize dependencies and avoid tight coupled components trough interface coding strategy. – EL Yusubov Oct 14 '12 at 17:19
  • Is it difficult keeping the business layer "fast" enough to be the go between the user and the raw data? Or are there tricks to allow temporary data caching (for instance calculations and algorithms run every 30 minutes) in the business layer? – user1745022 Oct 14 '12 at 17:21
  • Yes, the ruthless caching is also adding to performance boost, you may look into - bvnarasimharao.blogspot.com/2009/06/microsofts-velocity.html – EL Yusubov Oct 14 '12 at 17:23
  • 1
    Thanks for all your help! It looks like I have some learning/ researching to do. – user1745022 Oct 14 '12 at 17:27

As this sounds like a reporting application, to simplify the database side another approach to consider is to use some of the principles of data warehousing. This is basically denormalizing the database into into "facts" and "dimensions" (star schema). This makes it simpler for generating reporting and handling changes of the source database (e.g. just add another dimension) and also open it up to using OLAP tools for reporting. This could also reduce the number of databases/tables you have to worry about as they don't need to be customized.

Not the answer you're looking for? Browse other questions tagged or ask your own question.