# The Transfer Request > Apply, wait, get approved or denied. Track all of it. Canonical URL: Domain: Data Modeling · Difficulty: medium · Seniority: L4 ## Problem Employees can apply for internal transfers between teams. HR reviews each application and either approves or denies it. The reporting team needs to know: how many transfer requests were approved vs denied per quarter, by originating team and destination team. Design the data model, then write the query. ## Worked solution and explanation ### Why this problem exists in real interviews This probes two small but load-bearing modeling moves: a self-referential foreign key for reviewer (reviewers are employees, not a separate entity) and an append-only request table that preserves the history of resubmissions. The signal is resisting the urge to create unnecessary tables and choosing immutability over in-place updates. > **Trick to Solving** > > Before drawing any tables, a strong candidate asks: "is the reviewer a new entity or just an employee in a different role, and can a denied request be resubmitted?" If the reviewer is an employee, it is a self-referential FK. If resubmission is allowed, the table is append-only and each submission is a new row. > > 1. Spot the reviewer self-reference > 2. Make `transfer_requests` append-only > 3. Use two team FKs (from/to), not a junction > 4. Treat status as an ENUM with decided_at --- ### Break down the requirements #### Step 1: Model reviewer as a self-referential FK `reviewer_id` points back to `employees`. No separate reviewer table. HR reviewers are employees in a role, not a different entity. #### Step 2: Use two team FKs, not a junction `from_team_id` and `to_team_id` both point to `teams`. A junction would force simple directional queries into awkward self-joins. #### Step 3: Append-only request history Each submission is a new row. A resubmitted request never overwrites the denied one. HR compliance needs the denial history intact. #### Step 4: Status ENUM with decided_at `status` and `decided_at` live on the request row. In-flight requests have a null `decided_at`. --- ### The solution Below is one defensible model. The conceptual anchor is the self-referential reviewer and the append-only request table. > **Why this design holds up** > > Immutability of request rows preserves the denial history HR needs. The self-referential FK keeps the domain compact without a proliferation of role tables. Two team FKs turn directional questions into trivial joins. > **What strong candidates do** > > They immediately identify reviewer as an employee role, not a separate entity. They state that requests are append-only and justify it with the resubmission case. They keep the schema to three tables, no more. > **Red flags to avoid** > > Creating a `reviewers` table duplicates employee data. Updating a denied row to approved on resubmission loses the denial. A junction for from-team and to-team inflates join count for no benefit. --- ### The analysis pattern **Quarterly approvals and denials by team pair** ```sql SELECT DATE_TRUNC('quarter', r.submitted_at) AS quarter, ft.name AS from_team, tt.name AS to_team, COUNT(*) FILTER (WHERE r.status = 'approved') AS approved, COUNT(*) FILTER (WHERE r.status = 'denied') AS denied FROM transfer_requests r JOIN teams ft ON ft.team_id = r.from_team_id JOIN teams tt ON tt.team_id = r.to_team_id GROUP BY 1, 2, 3 ``` --- ### Trade-offs and alternatives **Append-only request table** Full history of resubmissions preserved. Simple directional joins via two team FKs. Requires a uniqueness rule if the business wants only one in-flight request at a time. **Mutable request with status history child** Parent request is updated in place; a child table logs status transitions. Cleaner representation of a single logical request. More tables and more writes per decision. --- ## Common follow-up questions - How would you prevent a second in-flight request from the same applicant without blocking resubmissions after a denial? _(Tests a partial unique index on (applicant_id) WHERE status = 'pending'.)_ - How would the schema track who approved a transfer after the reviewer left the company? _(Tests whether `employees` is soft-deleted and how `reviewer_id` resolves historically.)_ - If HR needs to audit every decision with a comment trail, where does that live? _(Tests adding a `transfer_request_notes` child table versus a JSON column.)_ - A denied request is resubmitted verbatim. Is that one logical request or two? _(Tests whether a `parent_request_id` self-reference on the request row is warranted.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_transfer_request) - [Data Modeling Interview Questions](https://datadriven.io/data-modeling-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.