| Goal | Find, based on the data that users have recoreded in a database, cases where a multi-valued attribute in a conceptual data model is implemented as a textual column of a base table. Expected values in the column are strings that contain attribute values, separated by commas or other separation characters. |
|---|---|
| Notes | The test creates a user-defined function that should be dropped in the end (see the garbage collection). |
| Type | Problem detection (Each row in the result could represent a flaw in the design) |
| Reliability | Low (Many false-positive results) |
| License | MIT (opens in new tab) |
| Fixing Suggestion | Create a separate table with a foreign key constraint referencing to the present table. |
| Data Source | INFORMATION_SCHEMA+system catalog |
| SQL Query |
|
Categories
This query is classified under the following categories:
Data at the database logical level
Queries of this category provide information about data in base tables.
Database design antipatterns
Queries of this category provide information about possible occurrences of SQL database design antipatterns.
Structure of base tables
Queries of this category provide information about the structuring of base tables at the database conceptual level
| Name | Description |
|---|---|
| Data at the database logical level | Queries of this category provide information about data in base tables. |
| Database design antipatterns | Queries of this category provide information about possible occurrences of SQL database design antipatterns. |
| Structure of base tables | Queries of this category provide information about the structuring of base tables at the database conceptual level |
Further reading and related materials:
This is one of the antipatterns from the Bill Karwin's book of SQL antipatterns. See Chapter 2: Jaywalking.
Smell "Compund attribute": Sharma, T., Fragkoulis, M., Rizou, S., Bruntink, M. and Spinellis, D.: Smelly relations: measuring and understanding database schema quality. In: Proceedings of the 40th International Conference on Software Engineering: Software Engineering in Practice, pp. 55-64. ACM, (2018).
Factor, P.: SQL Code Smells. Redgate, http://assets.red-gate.com/community/books/sql-code-smells.pdf, last accessed 2019/12/29 (Packing lists, complex data, or other multivariate attributes into a table column)
| Reference |
|---|
| This is one of the antipatterns from the Bill Karwin's book of SQL antipatterns. See Chapter 2: Jaywalking. |
| Smell "Compund attribute": Sharma, T., Fragkoulis, M., Rizou, S., Bruntink, M. and Spinellis, D.: Smelly relations: measuring and understanding database schema quality. In: Proceedings of the 40th International Conference on Software Engineering: Software Engineering in Practice, pp. 55-64. ACM, (2018). |
| Factor, P.: SQL Code Smells. Redgate, http://assets.red-gate.com/community/books/sql-code-smells.pdf, last accessed 2019/12/29 (Packing lists, complex data, or other multivariate attributes into a table column) |