why use a database instead of a spreadsheet
Database vs. Spreadsheet
by Karyn Stille When do you use a spreadsheet, and when do you need a database? Both application types are used for managing data. How do you decide which would be more practical? Spreadsheets Before the computer, bookkeepers, record keepers, and accountants used the paper and pencil method along with a ledger or record book containing worksheets. Information and records were stored by hand and financial records were calculated manually and entered in to the worksheets. Ledgers used rows and columns that people learned could be used not only for financial records, but also for things like scheduling, inventory tracking, and employee information. The dawn of the computer age brought applications that could store information, perform complex calculations, and provide a printed output. This concept virtually revolutionized the use of the computer early on. The first application with any real power was Visi-Calc, which later became Lotus 1-2-3. Visi-Calc alone gave businesses a serious enough reason to justify investing in computers for the office and is actually credited with keeping Apple computers in business. The advantages of using a spreadsheet application rather than paper and pencil were numerous - not the least of which was that when data was changed, totals and other formulas were automatically recalculated, saving both time and headache. However, Visi-Calc lacked functionality in the way of tools available to the user. Currently, Microsoft Excel, along with Lotus 1-2-3, commands most of the market for spreadsheet applications. Tools have evolved tremendously since that first Visi-Calc program. Now users have help available at a click of their mouse along with tools such as complex formula support, formula and function builders, sorting and filtering, scenario managers (for What-if analysis), charts and graphs, and extended data formatting tools. Databases A database organizes information on a particular subject for retrieval. Databases utilize one or more tables of information entered by the user to retrieve data for a variety of purposes. Data can be retrieved through methods such as asking questions of the data (querying), sorting or filtering, and pulling information into a formatted report, like an invoice, that can be printed. Although the tables look similar to spreadsheets, the tables are used to store raw data. In other words, there is no need to format the information in a database table. Reports generated from the data in the tables are where you would want specific formatting. Information in a spreadsheet is formatted in the actual spreadsheet, and that makes data entry a bit more tedious. Databases also involve the use of records to structure the tables. A record can contain any number of fields. Comparing this to a common phone book, a record would be an entire entry for one individual, and a field would be each separate part of the entire entry - like the individual's phone number. Reports organize the information in an understandable way and can combine data by performing complex calculations. Databases can also easily manage a large amount of information and better maintain data integrity. For these reasons, databases are much more powerful and manageable when handling a large amount of information related to a particular topic. The downfall? The downfall is that most database programs are not as easy to learn and use as most spreadsheet applications and are not as easy to make structural changes in once queries, forms, and reports are developed. One must have knowledge of the best way to structure the information into one or more tables before any tables are used to develop a means of retrieving the information. The reason for this, is that once saved queries, forms, and reports are based on the table(s), any changes in the table(s) structure (like deleting/changing field names) may cause errors in all the objects based on the changed table(s). So, it is important that the developer of the database has a clear vision of all types of information that would need to be included and how to organize it.
This, combined with an interface that's not usually as intuitive as a spreadsheet, sometimes intimidates would-be database users. An Example of Database vs. Spreadsheet Use Now that you are a bit more familiar with the purposes of the two, how do you determine which is best for your data? Most businesses find that using both works best. Take a look at the following simple example: Company ABC needed a method of storing data related to customer sales where they could print invoices and be able to track orders and customer contact information. They also needed to be able to quickly calculate what an increase or decrease in product prices and/or sales would do to their overall revenue generation along with a way to analyze trends. First, they developed an Access database to store all of their customer information and ordering data. They included the following tables: Contact Information, Products, and Orders. From this they used the tables and also created queries of the table data on which to base reports, like invoices. They also created easy to use forms for inputting data and a user-friendly switchboard for easy navigation. This gave them an efficient way to enter data, store data, and generate information for invoices, sales by product, sales by customer, and so on. Second, they used Excel spreadsheets to quickly calculate what changes in price and sales would do to their revenue by creating various scenarios. They could also use their sales information in Excel to analyze trends by generating charts and graphs. This gave them an easy way to analyze their data and trends in a tool with understandable and meaningful formats. What can we gain from this example? As a general rule of thumb, databases should be used for data storage and spreadsheets should be used to analyze data. If you currently use a spreadsheet to store data, ask yourself the following questions: Do changes made in one spreadsheet force you to make changes in others? Is the sheer amount of data unmanageable or becoming unmanageable? Do you have several spreadsheets that contain related information (such as separate sheets with sales for branches in Los Angeles, Chicago, and Houston)? Can you see all relevant data on one screen, or do you have to keep scrolling to find information? Are several people accessing the data at the same time? Do you have a difficult time viewing specific data sets that you want? If you answered yes to at least two of the questions, you should think about moving your information to a database application. In a Nutshell Use a database if. the information is a large amount that would become unmanageable in spreadsheet form and is related to a particular subject. you want to maintain records for ongoing use. the information is subject to many changes (change of address, pricing changes, etc. ). you want to generate reports based on the information. Use a spreadsheet if. you want to crunch numbers and perform automatic calculations. you want to track a simple list of data. you want to easily create charts and graphs of your data. you want to create What-if scenarios. In most cases, using the combination of a database to store your business records and a spreadsheet to analyze selected information works best. I remember my first massive spreadsheet project. My office was tasked with tracking the departmentБs annual budget, so I built an impressive network of spreadsheets. It had multiple linked spreadsheets, each with several tabs, auto calculations, stylish charts, PivotTables, auto-calculations and lookup formulas. It was brilliant! And then it crashed. I had pushed my spreadsheets beyond their limits and now I had two fiscal years of data corrupted. What I did not know at the time was that the more sophisticated I made my spreadsheets, the more I taxed them. I should have sought a database solution. Spreadsheets have great features such as automatically recalculated formulas, stylish charts and graphs at the click of a mouse, pivot tables, sorting and filtering, and cell formatting.
Microsoft Excel even has a БFormat as TableБ option that will instantly Бpretty upБ your dull data. The array of features available in spreadsheet applications makes displaying and analyzing large amounts of data easier. Spreadsheets are easy to use and flexible and inexpensive, which is why they have become the go-to business tool for storing and analyzing data. As sophisticated as spreadsheets have become, they still have some serious drawbacks. Spreadsheets are not ideal for long-term data storage. They only offer simple query options, do not guard data integrity, and offer little to no protection from data corruption. The New Guy, Databases A database is similar to a spreadsheet. In the simplest terms, a database is a collection of tables, organized in columns and rows, just like a spreadsheet. The big difference is that in a database each table has a unique set of columns and rows, and different relationships can be made between the different tables. A (RDMS) standardizes the way data is stored and processed. RDMS tables store data in a logical manner specifically designed to provide data integrity, reduce duplication, and minimize irregularities. A lot of grief can be saved if you take the time to consider the parameters of your project before you start. When deciding if you should create a database for your project, or transfer your current spreadsheets to a database, here are a few things to consider: User Access: The number one reason for creating a database instead of a spreadsheet is if multiple people will need to access the file. Sure, you gave everyone a week to update the spreadsheet, but without fail a group of procrastinators will all try to do their updates in the last 30 minutes before the deadline, resulting in a mass of Бfile is locked for editing byББ error messages. This sort of traffic jam is prevented in a database because multiple people can make edits simultaneously. Scope: A spreadsheet is great for tracking a simple list, but will that list continue to grow and potentially become unmanageable? Databases are better for long-term storage of records that will be subject to changes. Databases have a far greater storage capacity than spreadsheets. If your spreadsheet exceeds 20 columns and/or 100 rows, chances are it would be better for you to use a database. Reports/Queries: If you have difficulty querying specific datasets for reports, a database could be the answer. When building a spreadsheet, that data is formatted and arranged to get the desired report when printed. With a database, the data and reporting features are separate, allowing you to generate multiple reports with the same data. For example, management wants to see company-wide sales records by quarter, the program manager only wants to see annual sales for her region, and the marketing department wants to see monthly sales by product type. Instead of maintaining three spreadsheets with customized views of each party, a database would allow you to use advanced queries to generate all three formats from one source - no copy and pasting needed! Data Integrity : Duplication of data is another reason for moving away from spreadsheets. Does changing one cell force you to update several others? Do people save independent copies of the spreadsheet, causing duplicate and often outdated versions? In a relational database, data is stored in one place which minimizes redundancy and saves space. Remember that spreadsheets and databases are not mutually exclusive. Just because you upgrade to a database doesnБt mean you have to divorce your spreadsheets. In most cases, a combination of the two is the best. You can store your records in a database, allowing you to make advanced reports and queries. In turn, those reports and queries can be exported to spreadsheets for analysis.
- Autor: Roto2
- Comments: 0
- Views: 0