Database programming is central to all projects. Regardless of the whether you are planning desktop or web deployment, your application will rely on information that must be effectively and efficiently stored.
Database programming only starts after analysis and design phases are complete. For a description of those phases see Data Analysis and Database Design. Here we will share real world example where practical considerations may be at odds with the theoretical optimal solutions. We suggest that you attempt to overcome the influences that may cause programming to conflict with ideal design but it is important to understand the occasions that make it necessary and the dangers when making those decisions.Once analysis and design are complete we move to physical design and actual construction of the database structures.
Most organizations do not embark on a database project without having previously gathered large amounts of data that has a known organization. So a typical database selection discussion might include the questions: how large are your master tales; how many transactions records do you accumulate per month; how many records normally get consolidated into a reporting period; how many users need simultaneous access; is record level or field level security required; what are the audit requirements. These six questions will normally be sufficient narrow database selection.
Consideration of the actual data is also now critical to development. The data may not precisely match the analysis or the design. Correct decisions as to how to fit the design to the data or the data to the design requires a great deal of experience.
Many suggest that is time to start developing sample screens and reports. That may seem like the logical path but it will save countless development hours and it is CRITICAL to proceed by constructing the routines to load the data, then move to reporting and only then do data maintenance screens. The quickest path to demonstrating the accuracy of the implementation is to be able to report actual data. Having the ability to move data from a prior system and then report from the new system insures minimum operation interruption.
The reporting functions provide an important window onto database performance. Reporting performance can disclose the need for key system indexing and the need for materialized views. These are frequently needed external views and queries that save computing time. The downside of materialized views is the overhead incurred when updating them to keep them synchronized with their database data.
The maintenance functions of the application will reveal the various needs of protecting the data. Database access control deals with controlling who is allowed to access what information in the database. The information may comprise specific database objects (e.g., record types, specific records, data structures) or even computations over objects. Using passwords, users are allowed access to the entire database or subsets of it called “subschemas”.
Change and access logging records who accessed which attributes, what was changed, and when it was changed. Logging services allow for audit later by keeping a record of access occurrences and changes.
Database transactions can be used to introduce some level of fault tolerance and data integrity. A database transaction is a unit of work, typically grouping a number of operations. Each transaction has be committed or rolled back as necessary. information,however a database serves a better function at this. Below is a comparison of spreadsheets and databases. Spread sheets strengths -1. Very simple data storage 2.Relatively easy to use 3. Require less planning Weaknesses- 1. Data integrity problems, include inaccurate,inconsistent and out of date version and out of date data. 2. Formulas could be incorrect Databases strengths 1. Methods for keeping data up to date and consistent 2. Data is of higher quality than data stored in spreadsheets 3. Good for storing and organizing information. Weakness 1. Require more planning and designing
For a more in depth view of the database design issues read SQL Server and MS Access Database Design
When we undertake a project we review the actual data as early as possible. We quickly determine if the real world details match the conceptual framework that was described. If you have not decided on the database required we can explain the options. MS Access may be sufficient and will likely have the lowest cost. SQL Server will be required for larger transaction volumes and for more granular security.
We have a more complete discussion of MS Access vs SQL Server if you are undecided.
At the same time as we are deciding on a database we will be discussing the options for front end development. If you have decided on MS Access you will probably want your entire application in Access but you also have the choice of using a .NET desktop or web application.
If we will be using SQL Server, web or desktop deployment is available and the deployment can even be mixed. When using SQL Server as the database we always recommend that SQL Server is accessed using stored procedure calls rather than in-line SQL.
If this is a first database for you and you want additional practical information about MS Access please also visit our web site devoted to Microsoft Access development.
Feel free to contact us for a no-cost consultation to discuss your project further.