Data Dictionary and Design Documentation

What Is a Data Dictionary?

A data dictionary is a structured catalog that describes every table, column, and relationship in a database. Think of it as the official reference manual for your schema. When a developer needs to know what a column stores, when a business analyst wants to understand why a field exists, or when a new DBA takes over a system, the data dictionary is the first place they look. Without it, teams waste hours reverse-engineering code, make incorrect assumptions, and introduce bugs.

Data dictionaries are a cornerstone of data governance — the policies and standards that ensure data is accurate, consistent, and trustworthy across an organization. Both the DAMA CDMP framework and real-world DBA practice treat documentation as a professional obligation, not an afterthought.

What a Data Dictionary Contains

A well-built data dictionary records the following for every column in the database:

• Table name — which table the column belongs to
• Column name — the exact name used in the DDL
• Data type — the PostgreSQL (or other RDBMS) data type, such as `VARCHAR(100)`, `INTEGER`, `DATE`, or `BOOLEAN`
• Nullable — whether the column accepts NULL values (YES or NO)
• Constraints — PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, DEFAULT, or any named constraint
• Allowed values / domain — the range or set of valid values (for example, `status` might allow only `'active'`, `'inactive'`, or `'pending'`)
• Business definition — a plain-English sentence explaining what the column means in the real world, written so a non-technical stakeholder can understand it
• Example value — a realistic sample that illustrates the expected content

Spreadsheets are the most common format for a data dictionary at this level, with one row per column. Enterprise tools such as erwin Data Modeler, IBM InfoSphere, or built-in catalog features in cloud platforms automate much of this, but the underlying content is the same.

Why Each Field Matters

Data type and nullable are the bridge between documentation and the actual DDL. If your dictionary says a column is `VARCHAR(50)` but the DDL says `TEXT`, someone will write code based on the wrong assumption. Always verify against the live schema using `\d tablename` in psql or by querying `information_schema.columns`.

Constraints communicate rules that the database enforces automatically. Documenting them prevents developers from writing application code that duplicates or conflicts with database-level validation. For example, if a CHECK constraint already limits `quantity` to values greater than zero, an application should not separately enforce a different lower bound.

Allowed values go further than constraints for columns where the rule is enforced by application logic or a lookup table rather than a CHECK constraint. Documenting them makes the implicit explicit.

Business definitions are arguably the most important field. A column named `cust_flg` is meaningless to anyone who was not present when the table was designed. A business definition such as "Indicates whether the customer has opted in to marketing emails; set to TRUE at account creation if consent is given" removes all ambiguity. Write definitions in complete sentences and plain English — avoid jargon and abbreviations.

Keeping the Dictionary Accurate

A data dictionary that is out of sync with the actual schema is worse than no dictionary, because it spreads misinformation. Treat it like code: update it whenever the DDL changes. In professional environments this is enforced through change-management procedures — no schema migration is approved without a corresponding dictionary update.

For this course, your source of truth is the retail-store DDL from Lesson 6. Use `\d tablename` in psql to pull the exact data type, nullable status, and constraint details for each column. Cross-reference the business narrative from Lesson 2 to write accurate business definitions. Any conflict between your dictionary and the DDL is an error.

Data Dictionaries and Professional Credentials

The DAMA CDMP exam tests data governance, data quality, and metadata management — all of which depend on documentation practices like the data dictionary. Oracle and Microsoft certifications (OCA/OCP and DP-300) include metadata and schema documentation topics in their exam domains. The PostgreSQL CE exam tests your ability to understand and manage PostgreSQL schemas, which requires fluency in reading and interpreting schema structure exactly as a data dictionary captures it.

Safety and Professional Responsibility

In database work, "safety" means protecting data integrity and confidentiality. A data dictionary may contain sensitivity classifications — marking which columns hold personally identifiable information (PII), protected health information (PHI), or payment card data. Never include actual production data values in a data dictionary; use realistic but fabricated example values. If your schema documentation will be shared outside your team, verify that it does not expose sensitive system details (internal server names, security column names) that could assist unauthorized access. Follow your organization's information-security policy when storing and distributing documentation.

Your Task

Using the spreadsheet template provided, build a complete data dictionary for your retail-store PostgreSQL schema. Every column in every table must have all required fields filled in. Start by exporting `\d` output for each table, then transfer the structural facts (data type, nullable, constraints) before writing business definitions from the narrative. During peer review, your partner will check every row against both the DDL and the narrative — discrepancies are errors, not style differences. Revise before final submission.