0

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:

  • AdSize
  • Country
  • City

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

  • View One: Extract the first column and work out the length of it.
  • View Two: Now split of the 2nd column using the length.
  • View Three: If after replacing the x in the first column the value is numeric, store the value in "AdSize", and place the second value in the "CityCandidateOne" column.

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.

Hans
  • 2,800
  • 3
  • 28
  • 40
  • 1
    Multiple values in one column is an absolute no go. It violates first normal form. Usually relational databases are at least in third normal form. If I were you I wouldn't waste my time with endless views and functions that extract data. Get the data in multiple columns, rethink your database design... – fancyPants Aug 11 '14 at 12:39
  • I totally get this. I see the base table as a staging table for my ETL and a nice normalized table output - which right now exists in the final view. I'm planning to use the last view to populate a new normalized design. However the logic to arrive at this point is so distributed across these 50 odd views, that if any of the rules change, I'd have to look over most of them to make amendments. So I want to create a single place that is responsible to say extract a city from this compound source data column regardless of the format and then do the same for city, ... . – Hans Aug 11 '14 at 12:41
  • why not use a stored proc instead, It is better to havea 1000 line stored proc than to try to trace through 50 views when you nheed a change or to fugure out why a problem is happening. – HLGEM Aug 11 '14 at 15:27

2 Answers2

1

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)

Community
  • 1
  • 1
p.marino
  • 6,244
  • 3
  • 25
  • 36
0

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).

Erik Funkenbusch
  • 92,674
  • 28
  • 195
  • 291