CODEWITHSUNDEEP
sqldatabase-designanti-patterns
Hans
Hans

Reputation: 2910

Relational DB's View of View of View AntiPattern?

I have inherited a database that's causing me issues.

I'm in the need of describing something horrible to stakeholders. So far using the names of anti patterns and sending them away pointing them to a google search on this has been the most efficent to buy me some time.

Trouble is, I have not come across this before. Here's what's happening.

I have a simple single table, with a couple of columns. One of these columns contains values like:

660x90_SomeCity_SomeCountryISO_ImageName_SomeRubbish

or

SomeIataAirportCode_SomeCountry_660x90_SomeRubbish_ImageName

Now the database contains an (admittedly so far and on current data) faultless logic to extract and lookup things so that the output has additional columns such as:

The trouble is that this is achieved through gradual conversions implemented in a labyrinth of 50 (not joking) different views. I've now got to formalize the logic to something like

To me this is a horrible antipattern and should all be done either in custom functions, or preferably during the ETL process, in one place so the logic can be captured.

However I'm not being given the time and wonder if this is a known anti pattern. Usually I can then use the credibility of a Google search to buy a little time to really sort this out.

Upvotes: 0

Views: 345

Answers (2)

Erik Funkenbusch
Erik Funkenbusch

Reputation: 93444

Why not simply rewrite the SQL how you would rather do it, then print out the execution plans of both, and show the performance and timing of both. That should be enough to show them that it needs to change (and if there is no major performance difference, then your only other argument can be one of maintainability and that's something you're going to have to argue by showing them what it takes to make changes).

Upvotes: 0

p.marino
p.marino

Reputation: 6252

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)

Upvotes: 1

Related Questions