I was recently going through my notes of and was shocked to realize how actual this book still is. I'm going to share my summary of the book. I guarantee you'll find at least a few antipatterns in your current database design. SQL Antipatterns Logical Database Design Antipatterns Jaywalking Storing a delimiter-separated value string in a varchar field instead of creating an intersection table. It might be easier, but it makes queries harder. And updating the field is also a pain. And your field might have all kinds of consistency errors (for instance, ids are normally numbers, but now you have varchar. The DB cannot ensure consistency for you). Antipattern: when the data you're storing in the varchar is not needed to be used in the queries (gets displayed as-is). Legitimate Uses of the Antipattern: create an intersection table. Solution: Naive trees When you have multiple level parent -> child relationship and you represent it using an Adjacency list (you have a parent id in every row). It becomes impossible to get the whole tree in one query (because by default you can only get the next level). Antipattern: when you're certain that you will only have 1 level of queries. The alternatives are harder to implement. Legitimate Uses of the Antipattern: Solutions: Path Enumeration (storing the string of ancestors as an attribute of each node, just like the filesystem directories). Drawback: limitations of the Jaywalking antipattern. Nested Sets (add to each node nsleft and nsright: the nsleftnumber is less than the numbers of all the node’s children, whereas the nsright number is greater than the numbers of all the node’s children. These numbers have no relation to the id values). General tree reading becomes easy, but editing the tree becomes hard because all the nsleft and nsright need to be reprocessed. Closure Table: an extra table that holds all the relations between ancestors and descendants (including node indirect relationships and a reference to itself). The only drawback is the extra table size for big trees. But other operations are very easy to implement. "Joe Celko’s Trees and Hierarchies in SQL for Smarties" Reference: ID required Using a primary key that is not the appropriate primary key for this table (e.g., there is a natural primary key like social security number or there could be a compound key in there). If a table has a unique column then, most probably that could be the primary key. Antipattern: declare a primary key on a field that is easy to index and makes the most sense for that particular table Solution: Keyless entry when you don't use the constraints offered by the DB (especially foreign keys). Antipattern: never, unless your DB doesn't support it Legitimate Uses of the Antipattern: always declare constraints Solution: Mixing data with metadata For example, by appending the year onto the base table name, we’ve combined a data value with a metadata identifier. Antipattern: This is the reverse of mixing data with metadata from the Entity-Attribute-Value and Polymorphic Associations antipatterns. In those cases, we stored metadata identifiers (a column name and table name) as string data. In Multicolumn Attributes and Metadata Tribbles, we’re making a data value into a column name or a table name. If you use any of these antipatterns, you create more problems than you solve. Entity-value-attribute when you have a table with the columns: entity_id, attribute_name, attribute_value Antipattern: It appears when you have OOP inheritance and the children have various fields. If you use it, you lose referential integrity and data type validation. you shouldn't use this in a relational DB. Just use a non-relational DB or one of the solutions below. Legitimate Uses of the Antipattern: Solutions: Single table inheritance: one table with all the attributes of the children (it's going to be a sparse table Concrete table inheritance: completely independent tables for each subtype Class table inheritance (one table for the parent properties and tables with the child-specific properties) Semistructured data (one table with the parent properties and an extra blob field with the child-specific attributes). Disadvantages similar to the Entity-value-attribute. Polymorphic associations When you need to reference from a single table, or multiple parent tables (e.g., comments to features/bugs). You're using the pattern if you store in a column the name of your type of parent table. Antipattern: Solutions: Create intersection tables Create a common super table (with only ids) and reference that with your new table. The children will reference the super table (ex: comments references issues; bugs references issues, features references issues) Multicolumn attributes When an entity's attribute can have multiple values, you create multiple columns called attribute1, attribute2, etc. Antipattern: Solutions: Create a dependent table (a table that references the initial table by id and has the attribute as the other column) Store each value with the same meaning in a single column. Metadata tribbles When to support scalability and performance, you clone tables or new columns to support new partitions: ex: sales_2013, sales_2014, etc. (tables of the same schema with different entities). Basically, in your database, you will have a table per "some data value". Antipattern: when splitting the database sensibly makes database administration tasks easier after the database size passes a certain threshold. Legitimate Uses of the Antipattern: Solutions: Using Horizontal Partitioning: rows are separated into partitions. Mostly managed by the DB. Using Vertical Partitioning: splitting a table by columns. Databases usually do this for TEXT and BLOB. Physical Database Design Antipatterns Rounding errors (when you want to store floating point numbers in the DB) When you use float, double, or any other related DB type. The problem is how the number is represented internally by the DB. It will do all kinds of unpredictable roundings. Antipattern: scientific applications. Legitimate Uses of the Antipattern: use NUMERIC or decimal Solution: 31 flavors When you want to restrict a column to specific values (e.g., the status of a ticket), you define the allowed values in the table's schema or as a trigger. Antipattern: when you have very few values that will never change. But you might still get it wrong (e.g., gender). Legitimate Uses of the Antipattern: create a lookup table. Solution: Phantom files When you want to store Store Images or Other Bulky Media (when you reference them via the DB), and you use the FS directly, instead of the DB. These need to be specifically backed up, and the DB does not manage them. Antipattern: when the data files might affect how your DB behaves. Legitimate Uses of the Antipattern: Use BLOB Data Types As Needed Solution: Index shotgun When you want to Optimize the performance of the DB, and you throw indexes here and there without thinking too much about it (no indexes, too many indexes, queries that cannot benefit from indexes). Antipattern: none Legitimate Uses of the Antipattern: Solution: Measure the query times Get the query execution plan (QEP) Analyze the QEP and find where the index needs to be added
after creating the index, test Optimize: covering (compound) indexes could speed up things even more; use in mem vs. on disk indexes Rebuild: because indexes become fragmented as rows are deleted/created/updated Query Antipatterns Fear of the unknown thinking that null is just another value. Actually, SQL treats null as a special value, different from zero, false, or an empty string. As a general rule, all operations with null will return null. Or using a value to represent null. Antipattern: Treat null as a unique value. Declare Columns NOT NULL when it makes sense. Also, consider if using default makes sense. Solution: Ambiguous groups What you're already doing: Get Row with Greatest Value per Group. When you're using group by and are trying to get extra information from the row that you selected (like other columns that are not included in the group by expression), in other words, referencing Nongrouped Columns in the select list of the query. Antipattern: Every column in the select list of a query must have a single value row per row group. This is called the Single-Value Rule. Columns named in the GROUP BY clause are guaranteed to be exactly one value per group, no matter how many rows the group matches. none because most DBs will throw an error Legitimate Uses of the Antipattern: Solutions: Don't add an extra column to the select list Using a Correlated Subquery (own note: seems pretty hard to implement) Using a Derived Table: use the group query as a subquery of a query that selects the columns that you need. But merging their results with left join https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column Using an Aggregate Function for Extra Columns -> apply a function to the extra column in the select list so that you ensure that there's only one value coming back (e.g., max or GROUP_CONCAT) Random selection When you must fetch a random sample from one of your tables, you sort data randomly -> ORDER BY RAND(). This operation does a full table scan. Antipattern: only when you know for sure that the size of the data that you're randomizing will not be bigger than 50-100 rows. Legitimate Uses of the Antipattern: Solutions: Pick a Random primary Key Value Between 1 and MAX; only when primary keys are contiguous Choose Next Higher Key Value: just like before, except you'll be picking the next available key (assumes that the keys are non-contiguous). The results will not be evenly distributed Get a List of All Key Values. Choose One at Random (do it in the application; does not scale well) Choose a Random Row Using an Offset: select random between 0 and total_rows and use it in an OFFSET command Search the docs of your DB of choice Spaghetti query When you want to achieve everything in only one SQL query (solving a complex problem in one step, Antipattern: You might create a cartesian product without wanting to do that It's going to be hard to maintain when you're going to use that query as a data source in a 3rd party app Legitimate Uses of the Antipattern: Solutions: Split your initial query into multiple smaller ones Use the union label (to combine smaller queries) Writing SQL Automatically—with SQL -> use concat to create a list of queries to run; you can also do this with a script in bash Readable passwords Store Password in Plain Text Antipattern: hash your passwords with salt directly in your application. Solution: SQL injection when writing dynamic SQL queries, you end up executing unverified input as code. Antipattern: Solutions: Parameterize Dynamic Values (use query parameters) Filter Input Do your best not to write standard SQL. Rely on your data access framework as much as possible Diplomatic immunity when you want to code fast, completely skip engineering best practices. Make SQL a Second-Class Citizen, not giving the DB the same importance that the code gets in terms of quality. Antipattern: Solution: Forget about self-documenting code. It's a myth. Use Entity-relationship diagrams, break them down into functionalities, and mention triggers and stored procedures Use source control Have tests that test the interaction with the DB Magic beans when designing an MVC application and you don't consider enough how to separate the app logic between the M, V, and C. (Own note: I'll probably need to re-read this chapter from the book) Antipattern: Active record is a design pattern that maps objects to DB tables. The Model Has an Active Record -> basically, build services that compose the models. Aim to make your model a domain model, not a database model. Solution: Rules of normalization Should be considered only in the context of your app. How much do you want to normalize/denormalize? It's a tradeoff. And it would be best if you benchmarked it. The objectives of normalization: To represent facts about the real world in a way that we can understand To reduce storing facts redundantly and to prevent anomalous or inconsistent data To support integrity constraints The forms First Normal Form: no repeating groups (no multicolumn attributes, no multiple values in one column) Second Normal Form: when you're repeating values on a column, instead of creating a dependent table Third Normal Form: single responsibility principle for tables (put the column in the table that it belongs to) Advanced 3rd form (Boyce-Codd Normal Form): table doesn’t contain any field (other than the primary key) that can determine the value of another field. Example: teacher, subject, student. The subject is always dependent on the teacher. Fourth Normal Form: BCNF without compound keys Sixth normal form: It’s typically used to support a history of changes to attributes (e.g., an audit log). See no evil discarding DB error messages or not looking at the raw SQLs that get run when debugging the application. Antipattern: Recover from Errors Gracefully -> log potential exceptions every time. Solution: Antipatterns that are not that current anymore? Implicit Columns when you want to reduce typing (like explicitly mentioning all columns in the select list), you use: Antipattern: For insert without specifying the columns, when you refactor you might break the insert or, even worse, the database A select with * will fetch and display all the data when you're writing queries to try things out Legitimate Uses of the Antipattern: Name Columns Explicitly Solution: Poor man’s search engine when you want to do a full-text search, you use pattern-matching predicates (e.g., LIKE, REGEXP). Antipattern: don't use SQL. Elasticsearch has made this one easy. If you have to use SQL (for simple cases, each vendor has a solution for this already) Solution: Pseudokey neat-freak when you want to have contiguous keys in a table (e.g., when you have deletions in the DB), you want to fill in the gaps. Antipattern: Instead of allocating a new primary key value using the automatic pseudo key mechanism, you might want to make any new row use the first unused primary key value Renumbering Existing Rows -> changing the existing keys so that they are contiguous. none. Legitimate Uses of the Antipattern: just let it be. You can see the primary key id as UUIDs. The fact that they are consecutive is just a coincidence of the implementation. Solution: Also published . here

Fetch

Super

Read My Stories

Too Long; Didn't Read

In your car, at home, or at work — Bosch technology shapes many areas of life.

An Overview of SQL Antipatterns

Too Long; Didn't Read

Companies Mentioned

Horia Constantin

About Author

TOPICS

THIS ARTICLE WAS FEATURED IN...

An Overview of SQL Antipatterns

Too Long; Didn't Read

Companies Mentioned

Horia Constantin

About Author

TOPICS

THIS ARTICLE WAS FEATURED IN...

RELATED STORIES