Database Anti-Patterns from PDX Ruby Tuesday

On Tuesday night, Xavier Shay (@xshay) gave a short talk on database anti-patterns.

Here are my rough notes:

  • STI for Shared Data
    • STI = single table inheritance. google “rails sti”.
    • this is sometimes called a "god table". It is easily found by having a single table that has many null columns, because different kinds of objects are being stored in a single table.
    • Example: a database for books with columns id, type, name, illustrator. for books, there is no illustrator, so you have a null field.
    • This gets complicated over the time. you have to loosen database constraints (you can’t enforce a value for illustrator), and logic is required to handle the null case, even for books. 
    • Using class table inheritence is one solution: books is one table, a second table is comics, and comics table has the illustrator field. In this case, instead of complicating the handling for books with a null illustrator field, we make comics, which needs the extra data, handling getting it from the table.
  • Not Deleting Data
    • People are afraid to delete data
      • in part, afraid of creating dead references, e.g. can’t use has_many
      • business needs to go back in past
    • It’s bad to try to use one database for both reporting and operational needs.
    • Example: a users database, in which records can marked as deleted.
      • then either user names can’t be unique, or then user names can’t be reused. either way, this then gets into extra coding and/or relaxing constraints.
      • plus, all your queries become “find users where state is not deleted”, so all queries become more complex and slower.
    • Solution: have two tables, and move the deleted users into the old_users database, which gives you your history.
  • Different data, same database
    • No notes here, sadly.
  • Not locking data
    • Example: it’s easy, if the user hits pay twice, that you could have a race condition here:
      • order = Order.find(id)
      • order.mark_as_paid!
    • Fix
      • Order.transaction do 
        • order = Order.find(id, :lock => true)
        • order.mark_as_paid!
      • end

Post a Comment