An invoice can appear once in a billing table and several times in a payment application table. Preparing an invoice population requires understanding those relationships before combining the records.
A relational database organizes related information into tables connected through corresponding key values. The auditor uses the structure to locate the required attributes and determine how records can be combined without changing their meaning.
- Table: A collection of records about a defined subject, such as customers, invoices, or payment applications.
- Record: One row representing an instance of that subject, such as a particular invoice.
- Field: A column describing an attribute, such as invoice date, customer number, or amount; its data type controls permitted representations.
- Schema: The database's structural description, including tables, fields, data types, keys, and relationships.
- Record granularity: The level represented by each row, such as one invoice, one invoice line, or one payment application.
An invoice header records the total invoice, while invoice lines describe individual products or services.
Common mistakes
- Numeric labels treated as quantities. Averaging customer numbers 0007, 0012, and 0021 produces no meaningful customer characteristic.
- Current status substituted for history. Removing closed inv-102 omits its $600 year-end receivable despite the retained January settlement date.
- Valid applications deleted. Removing pay-502 solely because inv-101 already has pay-501 discards a separate $150 allocation.
Bottom line
- Tables contain records and fields; primary keys identify records, while foreign keys connect related records.
- Database normalization organizes attribute dependencies and remains distinct from standardizing extracted values.
- Measurement scales determine meaningful comparisons; discrete and continuous separately describe possible values.
- Extraction requests specify population boundaries, record granularity, attributes, sources, formats, and control information.
Exam shortcut
Identify what one row must represent before choosing fields or transformations. If the procedure concerns invoices, preserve invoice identifiers through every stage. Circle the requested date and distinguish it from the extraction date. Look for later collections, reversals, or closures that change present status. When a join changes totals, compare row counts with distinct-key counts, then inspect repeated keys on the matching side.
The full lesson (about 5,426 words, 36 min read) adds 2 worked examples, all 5 common mistakes, a self-check, free in the app.
Learning objectives
- III.A1
Browse all free CPA AUD lessons or jump into free CPA AUD practice questions.