← AT&T Interview Insights

AT&T·Software Engineer·Onsite - System Design / Architecture·Senior

Senior
May 2026

Summary

System design round for a software engineer role, focused entirely on a flight ticket booking system. Pretty deep dive, went from schema design all the way down to B-tree internals and concurrency problems.

Questions Asked (5)

Q1

Design the database schema for a flight ticket booking system. What tables would you create and how would they relate to each other?

Data ModelingSystem Design
Author's notes

Went with Flight, Passenger, and Flight_ticket tables which felt pretty natural.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the core requirements and scope of the booking system, then outline the main entities and their relationships using an entity-relationship diagram. Finally, detail the tables, primary/foreign keys, and discuss normalization, indexing, and scalability considerations.

Pro tip: Demonstrate awareness of real-world complexities like concurrency (e.g., seat locking during booking) and data consistency (e.g., ACID transactions) to show you understand production-grade systems.

1. Clarify Requirements and Scope

Ask questions to understand the system's scale, key features (e.g., seat selection, payment, cancellations), and non-functional requirements like performance and consistency.

2. Identify Core Entities and Relationships

List the main entities such as User, Flight, Airport, Booking, Payment, and Seat, and define how they relate (e.g., one flight has many seats, one booking has many passengers).

3. Design Tables and Keys

For each entity, define the table structure with columns, data types, primary keys, and foreign keys. Ensure relationships are properly enforced.

4. Address Normalization and Indexing

Explain how you would normalize the schema to reduce redundancy and add indexes on frequently queried columns to optimize performance.

5. Discuss Scalability and Trade-offs

Mention considerations like partitioning, sharding, and handling concurrent bookings, and discuss trade-offs between normalization and denormalization.

Key Points to Mention

  • Entities: User, Flight, Airport, Booking, Payment, Seat, Passenger
  • Relationships: One-to-many (Flight to Seat), many-to-many (Booking to Flight via BookingFlight), one-to-one (Booking to Payment)
  • Primary and foreign keys to enforce referential integrity
  • Normalization (3NF) to avoid data duplication, with strategic denormalization for performance
  • Indexing on foreign keys and search columns (e.g., flight number, date)
  • Concurrency control (e.g., optimistic locking, transactions) for seat booking

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.

Q2

What indexes would you build on these tables, and why?

Data ModelingTechnical Trade-offs
Author's notes

Talked through indexing on flight_id and departure time, and composite indexes for common query patterns.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the query patterns and workload (read vs write, frequency, data volume) before proposing any indexes. Then, for each table, identify the columns used in WHERE, JOIN, ORDER BY, and GROUP BY clauses, and propose indexes that cover those access paths while considering trade-offs like write overhead and storage. Finally, validate with EXPLAIN plans and suggest monitoring to ensure indexes are effective.

Pro tip: Always mention the trade-off between read performance and write overhead—indexes speed up reads but slow down writes and consume storage. Also, consider composite indexes and covering indexes to avoid key lookups, and remember that foreign keys often need indexes for joins.

1. Clarify the workload and query patterns

Ask about the typical queries, their frequency, and the read/write ratio. Understand the data volume and growth to assess index impact.

2. Analyze the table schema and relationships

Identify primary keys, foreign keys, and columns frequently used in joins, filters, and sorting. Note any unique constraints or business rules.

3. Propose indexes for each table

For each table, suggest indexes on columns used in WHERE, JOIN, ORDER BY, and GROUP BY. Consider composite indexes for multi-column filters and covering indexes for high-frequency queries.

4. Evaluate trade-offs and alternatives

Discuss the impact on write performance, storage, and maintenance. Consider if indexes are truly needed or if query rewriting or partitioning could help.

5. Validate and monitor

Suggest using EXPLAIN to verify index usage and monitoring tools to track performance. Be ready to adjust indexes as query patterns evolve.

Key Points to Mention

  • Selectivity of columns: high-selectivity columns are better for indexes.
  • Composite indexes: order of columns matters; leftmost prefix rule.
  • Covering indexes: include all columns needed by a query to avoid table lookups.
  • Write overhead: indexes slow down INSERT, UPDATE, DELETE operations.
  • Foreign keys: often need indexes to speed up joins and enforce referential integrity.
  • Regular maintenance: rebuild/reorganize indexes and update statistics.

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.

Q3

Can you explain how a B-tree index works internally?

Algorithms & Data StructuresTechnical Trade-offs
Author's notes

Did not expect them to go this deep in a system design round.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by defining what a B-tree index is and its purpose in databases, then explain its internal structure and how operations like search, insert, and delete work. Finally, discuss why B-trees are preferred for disk-based storage and mention trade-offs compared to other index types.

Pro tip: Relate the B-tree's design to minimizing disk I/O, which is crucial for database performance, and mention that many relational databases like MySQL and PostgreSQL use B-tree indexes by default.

1. Define B-tree index

Explain that a B-tree index is a balanced tree data structure used by databases to quickly locate data without scanning the entire table. Mention that it maintains sorted data and allows searches, sequential access, insertions, and deletions in logarithmic time.

2. Describe structure

Detail the internal nodes and leaf nodes: internal nodes contain keys and pointers to child nodes, while leaf nodes contain keys and pointers to the actual data rows (or row IDs). Emphasize that all leaf nodes are at the same depth, ensuring balance.

3. Explain operations

Walk through how search, insertion, and deletion work. For search, start at root and traverse down based on key comparisons. For insertion, find the appropriate leaf, insert, and split nodes if they become full, propagating splits upward. For deletion, remove from leaf and rebalance by merging or redistributing if underflow occurs.

4. Highlight disk optimization

Discuss how B-trees are optimized for disk access: nodes are sized to match disk blocks (e.g., 4KB), and the tree's high fanout reduces the number of disk reads. Explain that this minimizes I/O operations, which is critical for performance.

5. Compare and conclude

Briefly compare B-trees to other indexes like hash indexes or B+ trees (if relevant), noting that B+ trees store data only in leaves and are often used in databases. Conclude by reiterating why B-trees are a fundamental component of database indexing.

Key Points to Mention

  • Balanced tree with all leaves at the same level
  • High fanout to minimize disk I/O
  • Nodes correspond to disk blocks
  • Logarithmic time complexity for search, insert, delete
  • Node splitting and merging to maintain balance
  • B+ trees vs B-trees: data stored only in leaves in B+ trees

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.

Q4

Walk through the full UX flow and the backend queries involved when a user books a flight.

System DesignAPI & Integrations
Author's notes

This was actually my strongest part.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by outlining the end-to-end UX flow from search to confirmation, then dive into the backend queries at each stage, emphasizing data consistency and performance. Structure your answer to show both user-facing and system-level thinking, and highlight trade-offs like caching vs. real-time availability.

Pro tip: Mention how you would handle failures and edge cases, such as payment failures or seat unavailability, and how idempotency and retries ensure a smooth UX. This shows you think beyond the happy path and understand production realities.

1. Outline the UX Flow

Describe the user journey: search for flights, select a flight, enter passenger details, choose seats, pay, and receive confirmation. Highlight key screens and user actions.

2. Map Backend Queries to Each Step

For each UX step, identify the backend queries: e.g., search queries flight inventory, selection queries seat availability, booking creates a reservation record, payment updates status.

3. Discuss Data Consistency and Transactions

Explain how you ensure consistency, such as using database transactions to lock seats during booking, and handling concurrent bookings to prevent double-booking.

4. Address Performance and Scalability

Talk about caching flight search results, using read replicas for queries, and asynchronous processing for payment and confirmation emails.

5. Cover Failure Handling and Edge Cases

Describe how to handle payment failures, seat unavailability, and network issues, including retries, idempotency keys, and user notifications.

Key Points to Mention

  • Database schema: flights, seats, bookings, payments tables with appropriate indexes.
  • Query examples: SELECT available seats, INSERT booking, UPDATE payment status.
  • Concurrency control: optimistic vs. pessimistic locking, or using distributed locks.
  • Caching strategies: Redis for flight search, CDN for static assets.
  • API design: RESTful endpoints for search, book, and pay, with proper status codes.
  • Idempotency and retries: ensuring duplicate requests don't create multiple bookings.

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.

Q5

How would you prevent double booking when two users try to book the same seat at the same time?

System DesignTechnical Trade-offs
Author's notes

The real deep dive.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the requirements and constraints, such as expected concurrency and consistency needs. Then discuss strategies like database transactions with locking, optimistic concurrency control, or distributed locks, and explain how you would handle failures and ensure scalability.

Pro tip: Mention that while technical solutions prevent double booking, you should also consider user experience, such as providing clear feedback and handling race conditions gracefully to avoid frustrating users.

1. Clarify Requirements

Ask about the scale, consistency requirements, and whether the system is distributed. This shows you understand that the solution depends on context.

2. Choose a Concurrency Control Strategy

Discuss options like pessimistic locking (e.g., SELECT FOR UPDATE), optimistic locking (version numbers), or distributed locks (e.g., Redis). Explain trade-offs.

3. Implement with Database Transactions

Describe how you would use transactions to ensure atomicity, such as checking availability and booking within a single transaction.

4. Handle Failures and Retries

Explain how to handle conflicts, such as retrying with exponential backoff or returning a clear error to the user.

5. Consider Scalability and Performance

Discuss how your solution scales, e.g., using sharding, caching, or queue-based systems to handle high concurrency.

Key Points to Mention

  • Database transactions and ACID properties
  • Pessimistic vs. optimistic locking
  • Distributed locking with Redis or ZooKeeper
  • Idempotency and retry mechanisms
  • User experience and error handling
  • Scalability considerations like sharding or partitioning

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.