Database Concepts and Accounting Data Management

Introduction

Accounting information systems (AIS) depend on reliable data. Every sales invoice, supplier payment, payroll transaction, inventory movement and bank reconciliation begins as data captured by an organisation. If that data is duplicated, incomplete, incorrectly classified or poorly controlled, the resulting reports may be misleading even when the accounting software performs its calculations correctly.

A database provides an organised structure for storing, relating, retrieving and protecting accounting data. Database design is therefore not merely a technical concern. It affects transaction processing, financial reporting, auditability, internal control, regulatory compliance and managerial decision-making.

This chapter examines relational database concepts, accounting tables and keys, relationships, normalization, queries, data dictionaries and the Resource–Event–Agent (REA) model. It also considers data quality, data ownership, retention and governance, including the implications of cloud systems, automation and artificial intelligence.

Learning Objectives

By the end of this chapter, you should be able to:

1. Explain the role and architecture of databases in an AIS.
2. Design basic relational tables using appropriate fields, primary keys and foreign keys.
3. Identify one-to-one, one-to-many and many-to-many relationships.
4. Apply normalization principles to reduce redundancy and update anomalies.
5. Interpret accounting queries and distinguish operational from analytical uses.
6. Construct a basic data dictionary.
7. Apply the REA model to accounting transactions.
8. Evaluate data quality, ownership, retention and governance requirements.
9. Identify ethical, security and regulatory risks in accounting data management.

1. Databases in Accounting Information Systems

A database is a structured collection of related data managed so that authorised users and applications can store, retrieve and update information. A database management system (DBMS) is the software that manages this collection. Examples include Microsoft SQL Server, Oracle Database, PostgreSQL and MySQL. Enterprise accounting platforms commonly use a DBMS beneath their user interface.

An AIS typically contains several layers:

• User interface: screens, forms, reports and dashboards used by accountants and managers.
• Application layer: business rules such as invoice validation, tax calculation and approval workflows.
• Database layer: tables, relationships, constraints, procedures and stored data.
• Infrastructure layer: servers, networks, operating systems, backups and security services.

A transaction entered through an invoice screen may therefore pass through validation rules before being stored in several related tables. For example, a sales transaction could involve a customer record, sales-order header, sales-order lines, inventory records, tax details and a general-ledger posting.

A database differs from a spreadsheet in important ways. Spreadsheets are useful for analysis and small-scale tasks, but they can encourage duplicated data, inconsistent formulas, weak access control and uncertain version ownership. A DBMS supports concurrent users, validation, transaction controls, backup, recovery, role-based access and structured querying.

Data, information and metadata

Data are recorded facts, such as an invoice date or quantity sold. Information is data processed and interpreted for a purpose, such as a monthly revenue report. Metadata is data about data: field names, definitions, data types, permissible values, owners and retention rules.

The distinction is important. A field called Status is not adequately defined unless users know whether its values mean “draft,” “approved,” “posted” or “cancelled,” and whether those values are controlled by a formal list.

2. Relational Database Concepts

The relational model represents data in tables, formally called relations. A table consists of rows and columns:

• A row or tuple represents one occurrence, such as one customer or one invoice line.
• A column or attribute represents a property, such as customer name or invoice date.
• A domain is the permitted set of values for an attribute.
• A record is a practical term commonly used for a row.

A well-designed table normally represents one subject or event. A Customer table should describe customers; it should not also contain repeated product or payment details.

Example relational design

The CustomerID in SalesInvoice links an invoice to its customer. The InvoiceID in InvoiceLine links each line to its invoice. These links permit the database to avoid repeating the customer’s name on every invoice line while still retrieving it when needed.

Keys

A primary key (PK) uniquely identifies each row in a table. It must be unique and normally must not be null. A primary key may be:

• Natural, derived from real-world data, such as a legally assigned tax number; or
• Surrogate, generated solely for database identification, such as CustomerID = 10482.

Surrogate keys are often preferred because natural identifiers can change, be unavailable, or contain privacy-sensitive information. However, a surrogate key does not eliminate the need for unique business rules. A database may still require each tax number or invoice number to be unique within the relevant legal entity.

A foreign key (FK) is a field whose value refers to a primary key in another table. It enforces referential integrity. For example, every SalesInvoice.CustomerID should refer to an existing Customer.CustomerID, unless the design explicitly permits an exceptional state.

A candidate key is any minimal field or combination of fields that could uniquely identify a row. One candidate key is selected as the primary key. A composite key uses two or more attributes, such as (InvoiceID, ProductID) in a simple invoice-line design. Composite keys can accurately model relationships but may be less convenient for integration, so a surrogate line identifier is also commonly used.

Constraints

Constraints protect data quality at the database level:

• NOT NULL: a value is required.
• UNIQUE: duplicate values are prohibited.
• CHECK: values must satisfy a condition, such as Quantity > 0.
• PRIMARY KEY: identifies each row uniquely.
• FOREIGN KEY: prevents invalid references.
• DEFAULT: supplies a value when none is entered, where appropriate.

Constraints should complement, not replace, application controls and review procedures.

3. Relationships and Cardinality

A relationship describes how instances in two tables are associated. Cardinality specifies how many instances may participate.

• One-to-one (1:1): one employee has one current payroll profile, and one profile belongs to one employee. This is relatively uncommon and may indicate that two tables could be combined unless security or optionality justifies separation.
• One-to-many (1:M): one customer may have many invoices, but each invoice belongs to one customer. This is common in accounting.
• Many-to-many (M:N): many sales orders may contain many products. This cannot normally be implemented directly in a relational database. It is resolved through an associative table, such as SalesOrderLine.

Optionality is also important. A customer may have zero or many invoices; an invoice may require exactly one customer. This can be expressed conceptually as Customer 0..* — Invoice 1.

A relationship must reflect business rules rather than assumptions. For example, if an invoice can contain products from multiple warehouses, a simple invoice-to-warehouse relationship may be inadequate. The design may require an allocation table recording invoice line, warehouse and quantity.

4. Normalization

Normalization is a systematic method of structuring tables to reduce redundancy and prevent anomalies. It is based on dependencies among attributes. The objective is not to create the greatest possible number of tables; it is to represent facts once, in the appropriate place, while preserving useful performance and clarity.

First normal form (1NF)

A table is in 1NF when each field contains an atomic value relevant to the chosen design and repeating groups are removed. A poor invoice table might contain:

InvoiceID | CustomerName | Product1 | Quantity1 | Product2 | Quantity2

The number of products is limited by the number of columns, and searching or validating lines is difficult. A normalized design separates invoice header data from invoice-line data.

Second normal form (2NF)

A table is in 2NF when it is in 1NF and every non-key attribute depends on the whole primary key, not only part of a composite key. If InvoiceLine has the composite key (InvoiceID, ProductID), ProductDescription depends only on ProductID, not on the whole key. It belongs in Product, not in InvoiceLine.

Third normal form (3NF)

A table is in 3NF when it is in 2NF and non-key attributes do not depend on other non-key attributes. Suppose Customer contains:

CustomerID | CustomerName | PostalCode | City

If a postal code determines a city within the organisation’s rules, City may depend on PostalCode, rather than directly on CustomerID. A separate postal-code reference table may therefore be appropriate. In practice, exceptions may be justified where historical snapshots or performance requirements are important.

Anomalies prevented by normalization

• Update anomaly: changing a customer address requires updating many duplicated rows, and some may be missed.
• Insert anomaly: a new product cannot be recorded until an invoice exists.
• Delete anomaly: deleting the last invoice accidentally removes the only record of a product.

Normalization reduces such risks, but excessive normalization can make queries complex and slow. Denormalization deliberately duplicates selected values for reporting performance, integration or historical preservation. It should be controlled, documented and supported by reconciliation rules.

5. Queries and Accounting Use

A query is a structured request for data. SQL (Structured Query Language) is widely used with relational databases. A basic query conceptually selects columns from a table and filters rows:

SELECT InvoiceID, InvoiceDate, CustomerID
FROM SalesInvoice
WHERE Status = 'Posted';

A join combines related tables:

SELECT i.InvoiceID, c.CustomerName, i.InvoiceDate,
       l.ProductID, l.Quantity, l.UnitPrice
FROM SalesInvoice AS i
JOIN Customer AS c ON i.CustomerID = c.CustomerID
JOIN InvoiceLine AS l ON i.InvoiceID = l.InvoiceID
WHERE i.Status = 'Posted';

A calculated amount for one line is commonly represented as:

Here, Quantity is the number of units and UnitPrice is the price per unit in the stated currency. This calculation assumes that discounts, taxes, returns and rounding are either excluded or handled by additional fields and rules. A complete invoice total may be expressed as:

Σ means the sum across invoice lines. The formula is applicable only when the components use consistent currency, tax treatment and rounding policies.

An aggregate query may group transactions:

SELECT CustomerID, SUM(Amount) AS TotalReceipts
FROM Payment
GROUP BY CustomerID;

Accounting queries support:

• aged receivables and payables;
• exception reports for duplicate invoices;
• inventory turnover analysis;
• tax and statutory reporting;
• bank and subledger reconciliations;
• audit sampling and transaction tracing;
• management dashboards.

Queries must be tested carefully. A join that omits a relationship condition can multiply rows and overstate totals. For example, joining invoices to payments without a valid allocation relationship may incorrectly associate every payment with every invoice for a customer.

6. The Data Dictionary

A data dictionary is a controlled catalogue describing data elements. It supports consistent interpretation, system development, training, audit and governance.

A mature dictionary also records source system, business definition, sensitivity classification, permitted values, validation rules, retention period, lineage, last review date and responsible steward. Definitions should be business-oriented. “Revenue” must specify whether it means invoiced sales, recognized revenue, cash receipts or a management measure.

7. The REA Model

The Resource–Event–Agent model provides a conceptual framework for representing accounting phenomena. It is especially useful when designing databases that capture economic events rather than merely reproducing debits and credits.

• Resources are economically valuable objects controlled by the organisation, such as cash, inventory or equipment.
• Events are business occurrences that increase or decrease resources, such as a sale, cash receipt, purchase or payment.
• Agents are people or organisations participating in events, such as customers, employees, suppliers and cashiers.

REA commonly uses a duality relationship between economic events. A sale may be linked to a cash receipt or an account-receivable settlement. A purchase may be linked to a cash disbursement or an increase in accounts payable. The model also identifies stock-flow relationships: events change resources, and participation relationships connect events with internal and external agents.

For a credit sale, a conceptual REA design may contain:

• Inventory resource;
• Sale event, reducing inventory and increasing receivable or revenue-related information;
• AccountsReceivable or CashReceipt event, depending on settlement;
• Customer external agent;
• SalesEmployee internal agent;
• Product resource classification.

REA is not a replacement for the general ledger. Rather, it provides detailed operational facts from which accounting entries, reports and controls can be generated. It can improve traceability because a reported balance can be linked to underlying events, resources and agents.

8. Data Quality, Ownership and Governance

Data quality is multidimensional. Common dimensions include:

• Accuracy: data correctly represents the real-world fact.
• Completeness: required values are present.
• Validity: values conform to definitions and permitted formats.
• Consistency: the same fact agrees across systems.
• Timeliness: data is available when needed.
• Uniqueness: duplicate records are controlled.
• Integrity: relationships and history are preserved.

Quality should be measured against business use. A customer address may be adequate for a marketing report but inadequate for legal invoicing. Data-quality controls include validation at entry, duplicate detection, master-data review, reconciliations, exception reporting and periodic cleansing.

Ownership and stewardship

A data owner is accountable for the business meaning, access decisions and risk associated with a data domain. A data steward performs operational activities such as reviewing duplicates, maintaining definitions and resolving quality issues. A system administrator may manage technical access without owning the accounting meaning of the data.

Clear responsibility is essential. For example, the tax manager may own tax-code definitions, while the IT administrator manages the database permissions that implement approved access.

Governance framework

Data governance establishes decision rights, policies, standards and monitoring. A practical governance framework includes:

1. classification of data by sensitivity and criticality;
2. documented ownership and stewardship;
3. approved definitions and data standards;
4. access based on least privilege and segregation of duties;
5. change management and approval trails;
6. backup, recovery and continuity procedures;
7. quality metrics and issue escalation;
8. retention, archival and secure disposal rules;
9. periodic review of third-party and cloud providers.

Accounting data should be protected against unauthorised alteration as well as unauthorised disclosure. Audit logs should record who changed a transaction, what changed, when it changed and, where practicable, the reason and approval.

9. Retention, Privacy and Ethical Responsibilities

Retention is a controlled period during which records must be preserved and accessible. It should be determined by applicable company law, tax law, financial-reporting requirements, contractual obligations, litigation holds and organisational policy. Retention is not simply “keep everything forever.” Excessive retention increases storage, privacy and breach risks; premature deletion can obstruct audits or violate legal duties.

A retention schedule should specify the record category, legal or business basis, retention period, responsible owner, archival format and destruction method. Records subject to litigation or investigation must be placed on legal hold and excluded from routine destruction.

Accounting professionals also have ethical duties concerning confidentiality, integrity, objectivity and professional competence. Examples of unacceptable conduct include changing transaction dates to manipulate period results, sharing credentials, extracting customer data for personal use, or allowing an AI tool to process confidential records without approved safeguards.

Privacy principles generally require purpose limitation, data minimization, appropriate security, transparency and lawful processing. Exact legal obligations vary by jurisdiction, so organisations must consult applicable legislation and professional guidance.

10. Security, Controls and Contemporary Technology

Database security includes authentication, authorization, encryption, network controls, vulnerability management, backup protection and monitoring. Role-based access can separate duties among data entry staff, approvers, accountants and administrators. However, roles must be reviewed because an accumulation of individually reasonable permissions can create excessive access.

Important accounting controls include:

• validation of supplier and customer master-data changes;
• approval of journals and payment instructions;
• immutable or well-controlled audit trails;
• reconciliations between subledgers and the general ledger;
• independent review of privileged access;
• tested backups and recovery procedures;
• controls over interfaces and automated postings.

Cloud databases can improve scalability and availability, but they introduce questions about provider access, data location, subcontractors, service continuity, exit arrangements and shared responsibility for security. Blockchain or distributed-ledger technologies may improve shared evidential records in selected contexts, but they do not automatically guarantee accurate input data, privacy or compliance.

AI can assist with anomaly detection, invoice extraction, duplicate identification and natural-language querying. Its output remains subject to human oversight. Risks include biased classification, fabricated explanations, leakage of confidential information, weak auditability and automation bias. AI-generated accounting results should be traceable to source records, governed by approved models and reviewed according to materiality and risk.

Applied Case: Meridian Office Supplies (Illustrative)

Background

Meridian Office Supplies is a medium-sized wholesaler using spreadsheets for customer records, inventory, invoices and receipts. Its finance team reports frequent customer duplicates, unexplained differences between sales reports and the general ledger, and delays in preparing receivables analyses.

Problem

The spreadsheet design stores up to five products in one invoice row. Customer addresses are repeated on every invoice, invoice status is entered as free text, and receipts are listed separately without a formal allocation to invoices. Several employees can overwrite files, and no data dictionary exists.

Analysis

The design violates 1NF through repeating product groups and creates update anomalies through repeated customer data. Free-text status values such as “posted,” “Post,” and “complete” undermine consistent reporting. Unallocated receipts make it difficult to determine which invoices remain outstanding. Lack of ownership means tax codes and customer identifiers are changed without review.

Approach and solution

Meridian designs Customer, Product, SalesInvoice, InvoiceLine, Payment and PaymentAllocation tables. Primary and foreign keys enforce relationships. Invoice status becomes a controlled code. Customer master data is maintained by a designated steward, while the financial controller owns accounting definitions. A data dictionary documents field meanings and retention requirements.

The company introduces role-based access, approval of master-data changes, daily interface checks and a reconciliation between posted invoices, invoice lines and the general ledger. A query identifies duplicate customers using combinations of tax number, legal name and address, with human review before merging. Historical spreadsheets are retained according to the approved schedule and imported with documented lineage.

Outcome

The new design eliminates repeated product columns, improves invoice tracing and permits reliable aged-receivables queries. Management receives more timely reports, while auditors can trace selected balances from reports to invoices, lines, customers and payment allocations. The project does not remove all risk: data migration errors, incorrect source documents and inappropriate access still require continuing controls.

Learning points

The case demonstrates that database design is inseparable from accounting control. Normalization addresses structural weaknesses, but governance, ownership, reconciliation and user discipline are needed to maintain trustworthy information.

Challenges and Limitations

Relational databases are powerful, but they require careful design and administration. Poorly designed joins can produce misleading totals. Highly normalized structures may be difficult for non-technical users to query. Legacy systems may use incompatible identifiers and inconsistent definitions. Data migration can introduce errors that are difficult to detect if no baseline reconciliation exists.

There are also legitimate tensions between historical accuracy and correction. An incorrect posted invoice should usually be corrected through an auditable adjustment rather than silently overwritten. Similarly, deleting personal data may conflict with accounting-record retention requirements. Such conflicts require documented legal and governance decisions.

Best-Practice Checklist

Before implementing or reviewing an accounting database, ask:

1. Does each table represent a clearly defined subject or event?
2. Are primary keys stable and unique?
3. Are foreign keys and relationship rules enforced?
4. Have repeating groups and unnecessary duplication been removed?
5. Are exceptions to normalization documented?
6. Are business definitions recorded in a data dictionary?
7. Can important reports be traced to source transactions?
8. Are owners, stewards and access responsibilities clear?
9. Are retention, privacy, backup and recovery requirements documented?
10. Are automated, cloud and AI-supported processes subject to human oversight and audit?

Chapter Summary

A database is a controlled foundation for accounting information. The relational model organizes data into tables linked by primary and foreign keys, while constraints protect uniqueness, validity and referential integrity. Cardinality explains how records participate in relationships, and normalization reduces redundancy and update, insert and delete anomalies. Queries retrieve, join and summarize data, but they must be designed and tested to avoid misleading results.

A data dictionary establishes shared meanings, ownership and rules for data elements. The REA model represents resources, economic events and agents, offering a detailed conceptual basis for transaction processing and traceability. Reliable accounting data also depends on accuracy, completeness, consistency, security, retention, privacy, governance and professional ethics. Contemporary cloud and AI technologies create opportunities but require risk-based controls, transparency and human accountability.

Key Terms

Database: An organized collection of related data managed for storage and retrieval.

Database management system (DBMS): Software that creates, maintains and controls access to databases.

Relational model: A model that represents data in related tables.

Primary key: A field or combination of fields that uniquely identifies a row.

Foreign key: A field that refers to a key in another table.

Candidate key: A minimal attribute set capable of uniquely identifying a row.

Referential integrity: The requirement that foreign-key references remain valid.

Cardinality: The number of instances that may participate in a relationship.

Normalization: Structuring tables to reduce redundancy and anomalies.

Denormalization: Deliberate duplication for performance or other documented purposes.

Query: A request to retrieve or manipulate data.

SQL: A language commonly used to manage and query relational databases.

Data dictionary: A catalogue of data-element definitions, rules and ownership.

Data quality: The fitness of data for its intended purpose.

Data owner: The person accountable for the meaning, use and risk of a data domain.

Data steward: The person responsible for operational data-management activities.

Retention: The controlled period for preserving records.

REA model: A framework based on Resources, Events and Agents.

Audit trail: A record of transactions, changes, users, times and approvals.

Denormalization: Controlled repetition of data to support performance or reporting.

Review Questions

Short Answer Questions

1. What is the difference between a primary key and a foreign key?
2. Define referential integrity and give one accounting example.
3. What problem does normalization address?
4. What is the purpose of a data dictionary?
5. Name the three categories in the REA model.

Long Answer Questions

1. Explain how a relational database can improve the reliability and auditability of an accounting information system.
2. Describe 1NF, 2NF and 3NF, using an invoice database to illustrate each level.
3. Explain the importance of cardinality and optionality when designing accounting relationships.
4. Discuss the responsibilities of data owners, data stewards and database administrators.
5. Evaluate the governance, privacy and retention issues associated with cloud-based accounting databases.

Analytical / Application Questions

1. A company stores customer details, invoice details and five product columns in one spreadsheet row. Identify the principal design problems and propose a normalized table structure.
2. A management report shows receipts that are twice the actual amount after invoices and payments are joined. Diagnose a likely query or relationship problem and describe tests that would verify your diagnosis.
3. Design a basic REA model for a credit purchase followed by payment to a supplier. Identify the resources, events, agents and relevant relationships.

Multiple Choice Questions with Answers

Question 1

Which field most appropriately serves as the primary key of a Customer table?

A. CustomerName

B. CustomerID

C. CreditLimit

D. City

Correct Answer: B. CustomerID

Question 2

A customer may have many invoices, while each invoice belongs to one customer. What relationship is this?

A. One-to-one

B. Many-to-many

C. One-to-many

D. Many-to-one between both tables

Correct Answer: C. One-to-many

Question 3

Which normal-form principle requires non-key attributes to depend on the whole composite key?

A. First normal form

B. Second normal form

C. Third normal form

D. Fourth normal form

Correct Answer: B. Second normal form

Question 4

What is the main purpose of a foreign key?

A. To encrypt a table

B. To calculate tax automatically

C. To enforce a relationship with another table

D. To replace all candidate keys

Correct Answer: C. To enforce a relationship with another table

Question 5

Which item is a Resource in the REA model?

A. Customer

B. Cash

C. Sales clerk

D. Sale transaction

Correct Answer: B. Cash

Question 6

Which data-quality dimension asks whether required values are present?

A. Completeness

B. Uniqueness

C. Timeliness

D. Confidentiality

Correct Answer: A. Completeness

Question 7

In the expression LineAmount = Quantity × UnitPrice, what does Quantity represent?

A. The currency code

B. The number of units invoiced

C. The tax percentage

D. The invoice identifier

Correct Answer: B. The number of units invoiced

Question 8

Which control best helps prevent invalid customer references on invoices?

A. A foreign-key constraint

B. A larger monitor

C. A report colour change

D. A spreadsheet font rule

Correct Answer: A. A foreign-key constraint

Question 9

What is the principal role of a data dictionary?

A. To replace database backups

B. To define and document data elements and rules

C. To approve every customer payment

D. To eliminate the need for user access controls

Correct Answer: B. To define and document data elements and rules

Question 10

Which is a significant risk of an incorrectly designed join between invoices and payments?

A. Rows may be multiplied and totals overstated

B. All primary keys become encrypted

C. The database automatically becomes normalized

D. Retention periods are extended automatically

Correct Answer: A. Rows may be multiplied and totals overstated