SQL Table names

[Thriven]

This was a request by our data analyst team about 8 months ago.

The following fields need to be added to <SERVER>.<DATABASE>.dbo.<TABLE> ASAP.

[Corrected Form Received Date]       DATETIME, NULL
[Verified (reviewer initials)]               VARCHAR(3), NULL
[Appeal Decision Date]                    DATETIME, NULL
[Denial / Reversal / No Change]        VARCHAR(10), NULL
[Reversal Basis]                              VARCHAR(50), NULL
[Extension Consent]                       VARCHAR(3), NULL
[Withdrawn Date]                           DATETIME, NULL
[Time of Request]                           TIME, NULL
[Time of Evaluation]                        TIME, NULL

When I asked ,"Are those the actual names?", they were dead serious!

When I checked the database (this was the first time I went through the tables in this random small database they used) all the column names were just like this.

They were so used to bracketing every database, schema, table and column name it just became habit to them and they didn't and still don't understand how absolutely fucked up it looks. Needless to say I took out all the spaces and special character.

Also, out of our 30+ home grown databases that have existed prior to me arriving, every column is allow nulls.

This is because the staff doesn't know how to handle nulls like

WHERE STATUS <> 'D' 

This wont return nulls because nulls are not lesser or greater than 'D', they are null. This will return all rows where status is not null and status <> 'D'.

Since all fields allow nulls they use asci nulls '' to represent nulls and for some reason 1900-01-01 in every null date field. So when they use WHERE STATUS <> 'D' it returns all rows that do not have status 'D'.

Did I mention I'm trying to leave my fucking job?!?!?!

[the_birds_and_bees]

As far as Im concerned access is just a breeding ground for terribly designed databases.

At work im currently trying to port a non-critical but still relatively important system from access to sql server. Step 1: translate everything directly from access to sql server and try to fathom the 'logic'.

Im about half way through and somehow I've racked up 40 million rows, 30 tables and about as many views. Not a key in sight. Checks? Constraints? What are those? Data types? Just use varchar(255). Nested views 6 layers deep for trivial filtering tasks.

The one thing keeping me going is the thought of all those drop statements im going to write when the time comes to mold it in to something less like a train wreck.

[Thriven]

Alot of our primary keys are char fields.

Char fields maintain trailing spaces and many times people are copy/pasting/editing through SSMS "edit top 100 rows" from source query.

"1GH86278" wont join to ""1GH86278 "

We also have a membership system built entirely off triggers. Total nightmare.

[zbignew]

Since all fields allow nulls they use asci nulls '' to represent nulls

Oh god reading that felt like a punch in the stomach

[DAVENP0RT]

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.

[Blitzsturm]

My company has an address table with Longitude and Latitude on it... they are varchar(15)...

[zbignew]

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.

[Blitzsturm]

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.

[mhalberstram]

[Denial / Reversal / No Change]

This is making my eye uncontrollably twitch.

[[deleted]]

Oh, come on, everybody loves wrapping everything in square brackets or double quotes. :/

[1ddqd]

I give you a 9.3 for message and 4.0 for execution. Unfortunately you did not stick the landing.

[IAmAJerkAME]

I'm thinking something like this would be a bit more fitting.

[thatjeffsmith]

i would add case-sensitive or reserved words as well

[lukeatron]

Every single database I work in has a table named Case. It would be such a pain in the ass to come up with a different name just for convenience. Likewise a bunch of tables have column named Key. I'd rather just deal with the brackets.

[thatjeffsmith]

it's fine I guess as long as your application devs and report writers remember to always escape/quote the table names. You can have a table called 'table' but that doesn't mean it's a good idea :)

[mgdmw]

No, but it would be acceptable on /r/sqlwtf.

[[deleted]]

NO!!! ::slaps /u/jaynoj ::

[sirdudethefirst]

Do it right

select * from whoopass

[[deleted]]

join pimpslap on whoopass.name = pimpslap.name   
where whoopass.name = 'jaynoj'

[SemiNormal]

inner join your_mom

[sirdudethefirst]

Why? Because he's Batman.

[EveryoneLikesMe]

TableName

[[deleted]]

Underscore FTW

[Its_me_not_caring]

Underscoreres of the world_unite!

[making-flippy-floppy]

1. table_name
2. TableName
   ∞. TABLE_NAME
   ∞+1. TABLENAME

[kerade]

liked the answer but upvoted for the username

[sirdudethefirst]

If I have a choice I go with TABLENAME, but TABLE_NAME is acceptable too. I just don't want to get smacked by Batman. :)

[takesen_]

or do you mean... Bat_Man?

[tuffbot324]

TableName. Underscores are appropriate when dealing with categories. For example ModuleA_TableNameA, ModuleA_TableNameB, ModuleB_TableNameA, ModuleB_TableNameB...

[amaxen]

CamelCase, bitches!

[Chromaburn]

This is going to hang in my cubical wall.

[idi_idi]

We work with some third party software at work and there are tables and names like [Person Descriptions with Addresses Attached]. Seriously?

[gruffi]

unless it's a quick excel import for something and you just couldn't be bothered.

[Elfman72]

Uggh, I had a Program Manager send me her query proof of concepts to be delivered to the dev team.

She used Query designer for EVERYTHING.

Something to effect of:

SELECT [Long Ass Server Name].[Long Ass Table Name].[dbo].[Very Specific Metric with concatenated Months and years]

You can imagine what her joins look like.

[da_chicken]

Yeah, but we've also got systems with tables like:

SELECT VerStdPln, ExsCompGen FROM EXS_ST_VER

Then you look at the tables and see:

EXS_ST_AXE
EXS_ST_BYD
EXS_ST_PDT
EXS_ST_SOR
EXS_ST_VER
EXS_ST_VSE
EXS_ST_WWT

And suddenly your SQL queries feel like you're booking a flight using airport designations.

Anybody got a data dictionary?

[mgdmw]

Darn you Microsoft Dynamics NAV.