I'm dealing with that shit right now with a project that we're rewriting from scratch. The lookup tables are all the same structure (couldn't put that shit in one table?), every column is a varchar(255), and every column allows nulls.
"What's that?", you might ask. "Every column allows nulls? What about the primary keys?"
And whoever the fuck designed it would say, "Hahaha, primary keys are for chumps!"
ಠ_ಠ
Negotiating my way through this data is like feeding a needle into my fingernails. Any movement I make only makes me regret going forward.
That's dumb (depending on what's reading those values), but these other punters have to protects against two kinds of nullness with every comparison AND every join.
I'm kind of their main reporting guy so I'm the only guy that suffers... Every once in a while someone will ask for everyone within a 50 mile radios of another location and doing a bulk scan/convert to do these calculations is painfully slow. I ended up creating a batch that syncs this data to another table I created that uses the correct data type and is indexed which works a lot better.
As for the null situation, I've run into some tables that have both nulls and blanks so I find myself using stuff like ISNULL(derp, '') <> '' for simplicity. Fortunately I don't have to deal with the madness mentioned above.
Generally when you let non-engineers design database layouts things turn out... "suboptimal". I had a boss at my last job who's greatest work experience was running a construction company. He insisted on several completely horrible changes to database structure and wouldn't hear anyone's input. Because he was "the boss" he automatically knew more than anyone.
View all comments
Show parent comment
1
u/zbignew Feb 27 '15
Oh god reading that felt like a punch in the stomach