What Are the Most Common SQL Anti-Patterns

What are the most common SQL anti-patterns?

I am consistently disappointed by most programmers' tendency to mix their UI-logic in the data access layer:

SELECT
    FirstName + ' ' + LastName as "Full Name",
    case UserRole
        when 2 then "Admin"
        when 1 then "Moderator"
        else "User"
    end as "User's Role",
    case SignedIn
        when 0 then "Logged in"
        else "Logged out"
    end as "User signed in?",
    Convert(varchar(100), LastSignOn, 101) as "Last Sign On",
    DateDiff('d', LastSignOn, getDate()) as "Days since last sign on",
    AddrLine1 + ' ' + AddrLine2 + ' ' + AddrLine3 + ' ' +
        City + ', ' + State + ' ' + Zip as "Address",
    'XXX-XX-' + Substring(
        Convert(varchar(9), SSN), 6, 4) as "Social Security #"
FROM Users

Normally, programmers do this because they intend to bind their dataset directly to a grid, and its just convenient to have SQL Server format server-side than format on the client.

Queries like the one shown above are extremely brittle because they tightly couple the data layer to the UI layer. On top of that, this style of programming thoroughly prevents stored procedures from being reusable.

Do these database design styles (or anti-pattern) have names?

Well the first is nothing more than a one-to-many relationship (one employee to many products). This is sometimes referred to as a O:M relationship (zero to many) because it's optional (not every product has a product manager). Also not every employee is a product manager so its optional on the other side too.

The second is a join table, usually used for a many-to-many relationship. But since one side is only one-to-one (each product is only in the table once) it's really just a convoluted one-to-many relationship.

Personally I prefer the first one but neither is wrong (or bad).

The second would be used for two reasons that come to mind.

1. You envision the possibility that a product will have more than one manager; or
2. You want to track the history of who the product manager is for a product. You do this with, say a current_flag column set to 'Y' (or similar) where only one at a time can be current. This is actually a pretty common pattern in database-centric applications.

Relational DB's View of View of View AntiPattern?

I'd start with this answer which covers the violation of First Normal Form.

I also found this free ebook that might be of value.

I understand that what you are facing is something on a grander scale that just putting a couple of values in a field with a comma or other token to separate them, but I don't know of any antipattern that covers such a baroque mess.

Finally, here you can find more about "replacing SQL logic with Views" as an antipattern (just look for "Views as SQL Building Blocks Anti-Pattern" in the article) but take in account that in this case the problem seem to be about inefficient access to the data.

Last minute edit: maybe this is just a special case of the general Golden Hammer antipattern? (see also: http://en.wikipedia.org/wiki/Golden_hammer)

Is it normal to share one table's data with many other tables without directly using foreign key

The concern to have "fewer tables" just for the mere sake of it is usually completely misguided. In fact, when columns named "table_name" are included, that leaves no doubt that the designer was quite aware that he was actually combining multiple tables into one. In your case : text_values to be associated with the stuff in table1, text_values to be associated with the stuff in table2, and text_values to be associated with the stuff in table3.

Relationally speaking, it's indeed a complete antipattern but several "features" of the SQL language sometimes force designers into these patterns. (E.g. the lack of support for "distributed keys" - enforcing uniqueness of key values across [distinct-but-similar] tables.) Furthermore, the designer of that "shared_table" was probably thinking more of whatever UI would be needed to populate it, saw three tables that were "completely identical" (they aren't but for reasons he failed to see) and decided that it would be "better" to unify them so only one program/window/... would be needed for updating "shared_table". Waterbed theory. He saved some costs in terms of volume of code to be written ("duplicated" to his mind, no doubt) and new costs now arise elsewhere in the system in the form of more complex manipulation and integrity enforcement.

---

Related Topics