opslagschema

An opslagschema (lookup schema) is a design concept in data modeling and information systems that defines one or more lookup tables used to translate codes or keys into human-readable values or other reference data. It helps normalize data by separating reference data that is reused across transactions from the transactional data itself.

Core characteristics include a small set of reference data, where each row pairs a key or code with a descriptive value. The primary key is typically the code, and the accompanying fields provide the description and, optionally, additional attributes such as language, validity, or category. Lookup tables are often joined to other tables via foreign keys, enabling translations at query time.

Common usage scenarios include relational databases, where a foreign key from a fact or dimension table points to the lookup table to enrich data with descriptive text. In ETL processes, lookups are used to enrich, validate, or standardize data during extraction, transformation, and loading. In data warehouses, lookup tables support dimension lookups, category mappings, and slowly changing dimensions.

Typical examples include country codes mapped to country names (DK → Denmark) or status codes mapped to descriptions (200 → OK). Design considerations emphasize keeping lookup tables compact, well indexed, and subject to referential integrity. Performance strategies may include caching, controlled denormalization for read-heavy workloads, and versioning of reference data to manage changes. Handling of null or unknown codes and proper auditing of changes to reference data are also important.

Limitations include the potential for extra joins to impact performance and the risk of stale data if lookup tables are not refreshed regularly. Suitable when reference data is stable, small, and shared across multiple records or processes.