Research Nexus: What a Real Dataset Changed About the Database Design

Research Nexus was designed as a discovery and trend-analysis database for papers, researchers, venues, institutions, topics, citations, and coauthor relationships. The schema was not difficult because it had eleven tables. It was difficult because real source data refused to obey the clean categories drawn in the first diagram.

The implemented shape

The MySQL 8 schema contained five entity tables and six relationship tables:

venue ──< paper >── paper_topic ──> topic
            │
            ├──< paper_author >── researcher
            ├──< cites >───────── paper
            │                       │
            └──────── coauthor ─────┘

researcher ──< researcher_institution >── institution
researcher ──< interested_in >─────────── topic

The core held 3,000 papers, 9,404 researchers, 10,469 paper-author relationships, and 19,218 derived coauthor pairs. A controlled topic vocabulary had only 19 rows by design. The citation table had 333 edges because both endpoints had to exist inside the selected 3,000-paper subset.

That last point matters: a small table is not automatically a failed load. Its expected size follows from the sampling rule.

What the data forced us to decide

Identity was a source-system contract

Paper and researcher identifiers came from the source dataset rather than from names. Names are labels, not stable keys: two researchers can share a name, and one researcher can publish under several spellings.

Google Scholar attributes were optional enrichment. Keeping scholar_author_id, citation count, and email domain nullable preserved the difference between “unknown” and “zero.” It also prevented the enrichment source from becoming the identity owner.

Free text needed controlled vocabularies

Raw keywords had spelling, punctuation, and casing variants. They were mapped into 19 canonical computer-science topics. This improved grouping and navigation, but deliberately lost detail. The correct design is to retain the raw term and mapping version when reproducibility matters; a canonical category should not erase provenance.

Institution strings were even harder because the source often contained postal addresses. Extracting the segment that looked like an institution was a heuristic, not a fact. Such transformations should produce confidence and rejection records rather than silently claiming certainty.

Relationships sometimes carry data

paper_author was more than a bridge: author_position belonged to the authorship relationship. A paper does not have one author position, and a researcher does not have one either.

The coauthor table stored each undirected pair canonically as id1 < id2. Without that rule, (A, B) and (B, A) would represent the same relationship twice. A database check constraint would make this ETL convention enforceable.

One ingestion bug worth remembering

A CSV file can have more physical lines than logical records because a quoted field may contain a newline. Counting with a line-oriented shell tool and comparing that number to imported rows produced an apparent discrepancy: 1,505 physical lines versus 1,441 parsed venue records.

The database importer was not necessarily dropping data. The two tools were counting different units.

In plain terms: CSV is a grammar, not “one line equals one row.” Use a CSV parser to count CSV records.

This changed the validation method. Instead of comparing physical line counts, the pipeline should compare parser record counts, staged rows, accepted rows, and explicitly rejected rows.

Indexes follow query direction

A composite primary key on (paper_id, researcher_id) supports lookup by paper. It does not automatically make lookup by researcher_id efficient because the leftmost column differs. Queries that rank researchers or filter by topic therefore need indexes in the reverse access direction, such as:

CREATE INDEX idx_pa_researcher_paper
    ON paper_author (researcher_id, paper_id);

CREATE INDEX idx_pt_topic_paper
    ON paper_topic (topic_id, paper_id);

The lesson is not “index every foreign key.” It is to trace actual joins and filters, then verify them with EXPLAIN and realistic cardinalities.

What I would change in a second version

1. Add staging tables so parsing, normalization, and relational loading are separate phases.
2. Preserve raw values, mapping versions, rejection reasons, and source timestamps.
3. Add enforceable constraints for canonical coauthor order and controlled ranges.
4. Treat derived coauthor and interested_in rows as rebuildable products with lineage.
5. Design indexes from named application queries, then remove redundant ones after measurement.
6. Report subset coverage beside every graph metric so a sparse induced citation graph is not mistaken for the full network.

Review card

• Real identifiers should come from identity-owning sources, not display names.
• Nullable enrichment is different from a zero value.
• Controlled vocabularies improve analysis but should preserve raw provenance.
• Relationship attributes belong on junction tables.
• CSV record counts require a real parser.
• Composite-index order must match the query’s leading access path.