Systems and records
Legacy database data dictionary recovery after the expert has left
By SourceX Editorial · Updated
9 sections
Short answer
To recover a legacy database's data dictionary after the person who understood it has left, first freeze a copy and harvest what the system still says about itself: catalog comments, keys, lookup tables, reports and screens. Then rebuild each field's meaning from evidence, interviewing remaining users against real records, and grade every definition as confirmed, corroborated, inferred or unknown so no one treats a guess as fact.
System export
Legacy database data
Key takeaways
- Meaning rarely disappears completely; it is scattered across the catalog, the application, old documents, the values themselves and the people who used the screens.
- Secure the software, reports, code tables and export totals before the interviews, because those are what the interviews will be checked against.
- Interview people with real records in front of them, not with a blank list of column names.
- Every recovered definition carries a confidence grade and the evidence behind it, plus the date range it holds for.
- A dictionary is only finished when it reconciles with the data: every column, every observed code and every blank has an entry or an open question.
Where does the meaning of a legacy database still survive?
It survives in five places, and the person who left was only one of them. The other four are the database catalog, the application that sat on top of it, the documents people wrote around it, and the stored values, which carry patterns that betray what a field was used for.
Start with the catalog because it is the cheapest evidence and it was written at the time. PostgreSQL lets developers attach a comment to a table or a single column with COMMENT ON, and those comments can be read back through the built-in functions obj_description and col_description. SQL Server keeps similar notes as extended properties: the sys.extended_properties view returns one row per property, and Microsoft's own examples name the description property MS_Description, with each value capped at 7,500 bytes.
Most legacy systems have few such comments, but the ones that exist are gold, and a column with a comment that contradicts how the data looks is itself a finding. After the catalog, look at the application layer: entry screens show the human label next to each field, saved reports show which codes were grouped together, and import scripts show which spreadsheet column fed which table.
The last two places are softer. Vendor manuals, support tickets, onboarding notes and month-end checklists often explain a code in passing. People still in the company, such as the order desk lead, the controller or the warehouse supervisor, may never have seen the schema but know exactly what the Hold box on screen three meant.
What has to be captured before the first interview?
Capture everything an interview answer will later be checked against, and do it while the system still runs. Once the server is retired, the vendor contract lapses or the last licensed workstation is wiped, screens and reports are gone, and every definition becomes harder to confirm.
Choose an archive format that will still open without the original software. The Library of Congress describes SIARD, a vendor-neutral package for relational databases, as a ZIP file holding a metadata.xml description of the structure plus one XML file per table. Read the limits before relying on it: the same description notes that SIARD 1.0 does not support time-zone-bearing timestamp types or intervals, so check how those columns would be carried.
| Item to secure | Why it disappears | How to capture it | Proof it was captured |
|---|---|---|---|
| Database files and a full backup | Hardware failure or server retirement | Copy to protected storage and restore the copy once | Restore log and file hashes |
| The software that opens it, with version and license | Licenses lapse; installers vanish | Image the last working client or document the install | Screenshot of the version screen |
| Entry screens with field labels | Screens only exist while the application runs | Screenshot every tab of every form used | Numbered screenshot index |
| Saved reports and queries | Stored in the application, not the tables | Export definitions and one sample run of each | Report list with run dates |
| Lookup and code tables | Often edited or deleted during migration | Export in full with any inactive rows | Row counts per code table |
| Export totals by table and by year | Needed later to prove nothing was lost | Run counts before any migration step | Signed count sheet |
| Attachment folders and linked files | Live on a file share outside the database | Copy with folder paths unchanged | File count and total size |
| Manuals, tickets and handover notes | Sit in mailboxes of people who leave | Collect into one folder with sources noted | Document inventory |
How do you rebuild structure when keys were never declared?
Rebuild it from naming patterns and value overlap, then label every inferred link as inferred. Many older databases enforced relationships in application code rather than with declared foreign keys, so the catalog shows isolated tables even though the application joined them every day.
Schema documentation tools help with the first pass. SchemaSpy, an open source tool, draws diagrams from declared foreign keys and also from implied relationships, where a column's name and type match another table's primary key. Its schemaMeta file lets a team add comments and relationships the database never declared, which makes it a practical place to record what the interviews confirm.
Then profile the values. For every column, count blanks, distinct values and the earliest and latest dates, and tabulate each code by year. A code that appears only from one year onward, or a flag whose share jumps when a new manager arrived, is a strong hint that the meaning changed, and that is exactly the kind of question to bring to an interview.
Treat a name match as a lead, not a fact. CUST_ID in two tables may point at different customer lists if one table was loaded from an acquired company. Test each implied join by counting orphans: rows whose key finds no match on the other side.
The schema meaning recovery interview, stage by stage
Run the interview in front of real records, with the screens and reports captured earlier, and ask the person to explain specific rows rather than abstract columns. People remember what they did with a record far better than what a field was called, and a walkthrough produces evidence you can cite.
Interview whoever is left who touched the data: long-serving users, the finance team that reconciled it, the vendor's support staff, and the former owner if they can be reached and agree to help. Record each answer with the person's role, the date and the record IDs discussed.
| Stage | Question to ask | Evidence in front of them | What to record |
|---|---|---|---|
| 1. Role and period | When did you use this system and for what task? | Their user ID activity by year, if available | Role, years of use, screens used |
| 2. Screen walk | Walk me through entering one real order from start to finish. | Screenshots of each entry tab | Screen label to column mapping |
| 3. Record readback | What does this row tell you happened? | Five to ten real rows, chosen across years | Plain-language reading of each row, with row IDs |
| 4. Codes and flags | When would you set this code, and who else could? | Code frequency by year from profiling | Meaning, who set it, when usage changed |
| 5. Blanks and defaults | What does an empty value here mean? | Rows with blanks in the field | Blank meaning: not applicable, not known, not entered |
| 6. Workarounds | Which fields did people reuse for something else? | Free-text fields and odd value spikes | Repurposed fields and the period of reuse |
| 7. Contradiction check | This comment says X but the data shows Y; which is right? | Catalog comments and conflicting rows | Resolution or an open question |
| 8. Who else knows | Who would know the parts you do not? | List of remaining open questions | Names or roles to interview next |
What does each recovered definition need to record?
Each definition needs its meaning, the period it holds for, the evidence behind it and a confidence grade. Without the grade, a careful definition and a hopeful guess look identical in a spreadsheet, and whoever uses the data next cannot tell them apart.
At a glance
What does each recovered definition need to record
Use four grades. Confirmed means at least two independent kinds of evidence agree, one of them a system artifact or a record walkthrough. Corroborated means one person's account matches the value pattern. Inferred means only the name, type or profile suggests it. Unknown means no evidence yet, and the field stays in the dictionary with an open question rather than being dropped.
For a machine-readable copy, the Frictionless Table Schema specification is a convenient target: each field can carry a description and a list of categories for coded values, and the schema can declare missingValues, primaryKey and foreignKeys. Keep the grade and evidence columns in a companion file so the schema stays valid.
| Field | Purpose | Allowed values or form | Required |
|---|---|---|---|
| table_name, column_name | Identify the column exactly as stored | Catalog spelling, case preserved | Always |
| screen_label | Link to what users saw | Label text and screenshot ID | When a screen exists |
| business_meaning | What the value records, in plain words | One or two sentences | Always, or the text unknown |
| valid_from, valid_to | Period the meaning applies to | Dates or open-ended | When usage changed |
| code_list | Each code with its meaning | Code, meaning, first and last year seen | For coded fields |
| blank_meaning | What an empty value means | Not applicable, not known, not entered, mixed | Always |
| evidence_ids | Pointers to proof | Screenshot, report, comment, interview or row IDs | Always |
| confidence | How far the meaning can be trusted | Confirmed, corroborated, inferred, unknown | Always |
| source_roles | Who supplied the meaning | Roles and interview dates, no personal opinions | When interviews were used |
| open_question | What is still unresolved | Question and who might answer it | When confidence is below confirmed |
Worked entries: one that passes and one that fails
The two entries below are illustrative, written for an invented order database; they are not taken from any real company. They show how the same template exposes a weak definition before it reaches anyone downstream.
The passing entry holds up because each claim has a different kind of proof. The failing entry is graded confirmed on the strength of one recollection and covers a field whose values changed partway through the history, so the validator rejects it and the grade drops to corroborated with an open question.
| Element | Passing entry: ORD_HDR.STAT_CD | Failing entry: ORD_HDR.CUST_FLG2 | Rule applied |
|---|---|---|---|
| Meaning | Order status: A open, H on hold, X canceled, C shipped and invoiced | Probably credit hold | Meaning stated without hedging words |
| Period | Codes A, H, X from the first year; C added in a later year after an invoicing change | None given, although the flag is blank in all early years | Period required when the profile shows a change |
| Evidence | Screen label Status, status report grouping, and an order desk walkthrough of eight rows | One former clerk's memory | Confirmed needs two kinds of evidence |
| Blank meaning | Never blank in the data | Not recorded | Blank meaning always required |
| Grade | Confirmed | Labeled confirmed; validator downgrades to corroborated | Grade must match the evidence rule |
| Open question | None | Ask the controller whether the flag drove credit holds or collections | Required below confirmed |
Which checks prove the dictionary matches the data?
Run checks that compare the dictionary with the catalog and the values, not just with itself. A dictionary that reads well but leaves out a third of the codes in use is a liability, because users will assume the gaps mean nothing.
At a glance
Which checks prove the dictionary matches the data
Record the dictionary version and date with each run, and keep the output. When a migration, archive or buyer review happens later, that run log is the evidence that the definitions were tested against the same data.
- Coverage: every column in the catalog has a row, including columns that are always blank.
- Code completeness: every distinct value found in a coded field appears in its code list or is flagged as unexplained, with a count.
- Evidence rule: every confirmed entry cites at least two kinds of evidence, one of them a system artifact or a record walkthrough.
- Period coverage: the valid_from and valid_to ranges for a field cover the earliest and latest dates actually present.
- Join test: each declared or inferred relationship reports its orphan count, and inferred joins stay labeled inferred.
- Reconciliation: row counts per table and year match the export totals captured before migration.
- Open questions: every entry below confirmed has a question and a named role who might answer it.
What an interview with a former owner does and does not settle
An interview settles what a field meant; it does not settle who may use the records or the notes. Keep those questions apart from the start, and agree in writing how interview notes and recordings will be used before anyone sits down.
Oral historians draw the same line. The Oral History Association's glossary describes informed consent as documenting that the narrator had the information needed to decide whether to take part, and states that consent does not deal with copyright, which is handled through separate agreements. A business interview is not an oral history, but the distinction is useful: agreement to talk is not permission to publish or reuse.
Keep the rights questions on their own track. Having admin access to the old database does not show the company owns every record in it, and a recovered meaning does not create a right to license. Free-text fields that the interviews revealed were used for personal notes about customers or staff need a privacy review before any use, and the vendor agreement for the old software may restrict how its files are extracted. This is general information; counsel reviews the specific facts.
Bringing a recovered dictionary to a licensing conversation
A graded dictionary makes an old database far easier to assess, whether the next step is a migration, a long-term archive or licensing selected records for AI. Buyers and reviewers ask what each field means and how sure the company is; a dictionary with evidence and open questions answers both.
When licensing is the goal, SourceX takes a company's selected records from rights review through preparation, contracting and delivery as a dataset licensed for AI. Whether the old system's history qualifies is checked through a brief assessment that asks for none of its data, and no record leaves until the company has signed off on scope and terms in an agreement. Fields that stay unknown can simply be left out of the offered scope.
Frequently asked questions
What is a data dictionary for a legacy database?
It is a record of every table and column in the old system with its plain-language meaning, allowed values, what blanks mean and how the meaning changed over time. For a recovered dictionary, each entry also lists the evidence behind it and a confidence grade.
Can you rebuild a data dictionary without the original developer?
Usually yes, for most fields. Catalog comments, entry screens, saved reports, code tables and value profiling explain a large share of a typical schema, and long-serving users can explain the rest against real records. Fields nobody can explain stay in the dictionary marked unknown.
Which tools help reverse engineer an undocumented database?
Schema documentation tools such as SchemaSpy can diagram declared and implied relationships, and simple SQL profiling queries give blank rates, distinct values and date ranges. Tools produce leads; interviews and record walkthroughs turn them into confirmed meanings.
Should we keep the old software after migrating the data?
Keep a working copy, or at least screenshots of every screen and saved report, until the dictionary is finished and checked. Screens and reports often hold the only record of field labels and code groupings, and they disappear with the application.
What if a field's meaning changed over the years?
Record each meaning with its own date range rather than choosing one. Value profiling by year usually shows when the change happened, and an interview can explain why; downstream users then filter or recode by period.
Sources
- PostgreSQL's COMMENT ON attaches comments to tables and columns, readable through obj_description and col_description. Source
- SQL Server's sys.extended_properties view returns a row for each extended property, including those on objects and columns. Source
- Extended property values are sql_variant and limited to 7,500 bytes; Microsoft's examples use the MS_Description property name. Source
- SIARD packages a relational database as a ZIP with metadata.xml and one XML file per table; version 1.0 does not support time-zone timestamp types or intervals. Source
- SchemaSpy diagrams declared foreign keys and implied relationships where a column's name and type match a primary key, and accepts added comments and relationships. Source
- The Table Schema standard lets each field carry a description and categories, and lets a schema declare missingValues and primary and foreign keys. Source
- The Oral History Association separates informed consent to take part from copyright and use rights, which need separate agreements. Source
See if your company qualifies
A short company assessment. No data uploads are needed.