Synopsis: Jaywalking
Explore the Jaywalking antipattern, a common logical flaw in SQL where multiple values are improperly stored in a single column as comma-separated lists. Understand why this approach limits scalability and complicates data integrity. Learn how to manage many-to-many relationships properly using intersection tables to maintain flexibility and prevent errors as system requirements grow.
We'll cover the following...
Let’s imagine that you are developing a feature in a bug-tracking application to designate a user as the primary contact for a product. You originally allow only one user to be the contact for each product. Unsurprisingly, however, you are soon requested to support assigning multiple users as contacts for a given product.
At the time, it seems simple enough to change the database to store a list of user account identifiers separated by commas in place of the ...