Compound Attribute

This smell arises when a column is used to store a non-atomic attribute. For instance, storing comma-separated lists for an attribute to avoid creating an intersection table for a many-to-many relationship or storing a JSON file which is not used atomically.

Each attribute value must be stored and retrieved atomically. If a table does not adhere to this practice, the resultant schema introduces multiple problems. For instance, a user has to write more complex queries (using pattern-matching expressions) to retrieve data from this table. Such complex queries are prone to inaccurate results. Also, such queries cannot exploit available indexes. Even further, these queries are not portable due to vendor specific support to pattern-matching expressions.

Example

CREATE TABLE Books (
book_id SERIAL PRIMARY KEY,
book_title VARCHAR (1000),
authors VARCHAR (1000), -- comma-separated list
--, , ,
};

Tools

The following set of tools detects this smell: DbDeo(for SQL)

Reference

B. Karwin, “SQL Antipatterns: Avoiding the Pitfalls of Database Programming”. Pragmatic Bookshelf, 2010.