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