Developing a system with a spreadsheet
A spreadsheet is a grid of cells. Each cell holds a number, a word or a formula.
Design a spreadsheet system like this:
- Input cells: facts you type (item, quantity, price).
- Formula cells: results calculated for you, such as
Total = Qty × Price. Change an input and the total changes. - Checks: limits on what can be typed (for example, a quantity cannot be negative) and a clear heading for every column.
- Report: a total, a chart or a filtered list.
Good for: small jobs, quick calculations, one or two users.
Weak at: the same fact typed again and again, many users editing at once, and large amounts of data.
Developing a system with database software
A database stores data in tables. A table has rows (records) and columns (fields).
- Primary key: a field whose value is different in every row, like Customer ID. It names the row.
- Foreign key: a field in another table that holds that ID, so the two rows are linked.
- Query: a question you ask, such as "all orders of customer 1".
- Form and report: a clean screen to enter data, and a printed or on-screen summary.
Customer details live in the Customers table once. The Orders table holds only the Customer ID. When a phone number changes, you change it in one row, and every report is right. This avoids data redundancy (repeated data) and mismatched copies.
Steps to develop either system
- Need: ask users what they must record and what reports they want.
- Design: list the data, decide columns or tables, draw how tables link, plan formulas.
- Build: create the sheet or tables, formulas and forms.
- Test: enter normal, extreme and wrong values, compare results with a hand calculation, fix bugs.
- Use and improve: train users, take backups, and change the system as needs change.
Choosing: start with a spreadsheet for a small, single-user job. Move to a database when data grows, facts repeat, or many people need to share it safely.
Try it: tuition fee tracker
On paper, draw a fee tracker for five students. First as one sheet with columns Name, Phone, Month, Amount. Count how many times the phone numbers are written. Then draw it as two tables (Students with ID; Payments with Student ID). Count again. Compare with the free-play step in the 3D.
Key formulas and definitions
- Spreadsheet formula example: Total = Qty × Price
- Primary key = a field that is different in every row
- Foreign key = a field that holds another table's primary key
- Data redundancy = the same fact stored many times
- Development steps: need → design → build → test → use and improve
Worked examples
1. Cell B2 holds 4 and C2 holds 25. D2 has the formula =B2*C2. What does D2 show? What happens if B2 becomes 6?
D2 shows 4 × 25 = 100. If B2 becomes 6, D2 updates to 150 by itself.
2. A sheet has 30 orders from the same customer, with the name and phone in every row. How many copies of the phone number are stored? How many would a database store?
The sheet stores 30 copies. A database stores 1 copy in the Customers table; the 30 orders store only the customer ID.
3. In an Orders table, which field is the foreign key: OrderID, CustomerID, Item?
CustomerID. It holds the primary key of the Customers table and links each order to its customer.
4. Which tool is better for (a) a quick total of this week's bills, (b) a shop with 5000 customers and 3 staff all entering orders?
(a) A spreadsheet is quick and enough. (b) A database, because data repeats, is large and is shared by several people.
Common mistakes
- Typing a result instead of using a formula. When an input changes, the typed result stays wrong.
- Repeating the same details in every row and then correcting only one copy.
- Using a name as a key. Two customers can have the same name; use a unique ID.
- Skipping the test step. Always check the system with a hand calculation and with wrong inputs.