Data Modeling easy
Why is it best practice to include `created_at` and `updated_at` timestamps on every table row?
- A They provide a metadata audit trail that is essential for debugging data corruption and sorting records chronologically for end-users.
- B They are strictly required by the ANSI SQL:2011 standard and the database engine will reject any table definitions that omit them.
- C They significantly accelerate full-text search operations by providing a secondary internal index for the database's natural language parser.
- D They prevent the accidental creation of duplicate rows by acting as a secondary, time-based unique constraint that is enforced by the disk controller.
Answer: A. They provide a metadata audit trail that is essential for debugging data corruption and sorting records chronologically for end-users.
Standard timestamps (`created_at` and `updated_at`) are a 'free' form of observability. They allow developers to answer critical questions like 'When did this record change?' or 'Which items were added in the last hour?'. While they consume a small amount of extra storage, their utility in synchronization, debugging, and audit logging far outweighs the cost.
One system design question a day, with spaced repetition so the ones you miss come back.
Get the appRelated questions
- When modeling a Distributed Transaction using the Saga Pattern (Choreography or Orchestration), what type of consistency do you achieve?
- Which practice prevents SQL Injection attacks?
- What is 'Bi-Temporal' data modeling (e.g., in SQL:2011 standard)?
- Adding an Index to a database column generally improves Read performance. What is the negative trade-off?
- Log Systems Merge (LSM) Trees (used in RocksDB, Cassandra) optimize for fast writes. What is their primary downside regarding Disk I/O?
- What is the primary objective of normalizing a relational database schema (e.g., to 3NF)?