- Feature Articles
- CodeSOD
- Error'd
-
Forums
-
Other Articles
- Random Article
- Alex's Soapbox
- Announcements
- Best of…
- Best of Email
- Best of the Sidebar
- Bring Your Own Code
- Coded Smorgasbord
- Mandatory Fun Day
- Off Topic
- Representative Line
- News Roundup
- Editor's Soapbox
- Software on the Rocks
- Souvenir Potpourri
- Sponsor Post
- Tales from the Interview
- The Daily WTF: Live
- Virtudyne
Admin
Is a nullable Boolean actually a Boolean? I guess it could be if they used null for false and non-null for true, but that's its own wtf.
Admin
True for "yes", false for "no", null for "don't know yet/not applicable". You could use two boolean fields for that, but then you run into the equally esoteric difference between "yes/don't know" and "no/don't know".
Admin
A nullable CHAR(0) can act as a Boolean with really only two values, while a nullable BOOLEAN has 3 values (cue FILE_NOT_FOUND).
I disagree about using (any form of) bool for date/time format. While there are only two formats now, more could be added later. E.g., AFAIK, UK is distinct from both US and EU (talking about date format only, what else?) if there's even such a thing as a EU-wide format (I guess not). An enum or so would be appropriate here.
Otherwise, the table is obvious:
Admin
Don't know much about database design, but it strikes me as a bit odd that the default value for the not nullable "id" field is NULL. Is that really how you design an ID?
Other than that I wonder what "date_format = eu" looks like. AFAIK France and the UK (which at least until very recently was part of the EU, but I'm not sure if "date_format = eu" means the European Union or just Europe in general) uses the format dd/mm/yy, whereas Germany, Austria and Switzerland prefer dd.mm.yy. The Netherlands seem to use dd-mm-yyyy or ddd. dd-mm-yyyy, and in Norway (not part of the EU, but definetly part of Europe) they use dd/mm-yyyy, at least in handwriting.
Admin
The NULL value reported as default for NOT NULL column actually means "use the value from sequence" and it's allowed by the
auto_incrementvalue in the "Extra". This is just a quirk of the database engine and one of the very minor one. This is, after all, mysql - open source database bought by Oracle to totally dominate the market of "wierd-ass database engines".Admin
That's what I thought would happen, so thank you for confirming my suspicions. ;)
Admin
"yes/don't know"=> I don't know but lets assume "Yes"
"no/don't know" => I don't know but lets assume "No"
Admin
Calling a tinyint "boolean" in a database that does actually have a boolean datatype is .... interesting
I'm guessing the date format thing is just about which way around dd mmm is .... important to know, but not the way to do it. But squashing that into a boolean field would be an even bigger WTF. They are for yes/no questions, the tangled monstrosities that emerge when people try using them for options where two choices (currently) exist would drive Mother Theresa to drop kick orphans into a volcano.
finding out where those so-called boolean fields are being used, and whether they are working properly should be trivial for anyone with access to code ... hell even playing with the UI preferences screen and looking at the data would do it. They should have better names, but that doesn't make any difference.
Ditto the irrelevant waffle around object-relational impedance. So programmers are crap at database design/interfacing, who knew? Tables are not objects, thinking they are will cause you no end of pain, trying to solve that pain by throwing an ORM at it is just technical debt. If you need good OO design, make sure there's people on your team good at it, if the database is critical, make sure you've got a good database designer AND a coders who know how to work with databases. Unfortunately OO people are a bit different to databasey people and the two types will fight like rats in a sack, but that's what you need, rather than one half of your app being built in a really dumbass way (like here). Otherwise, just keep it simple.
Admin
I have real trouble with this assumption (and indeed, understanding even) that "developers don't know how to design databases."
There is absolutely no reason on earth that it should not be one of the basic tools in the developer's toolbox. It's a really, really simple concept, like object-oriented programming, and enumerated types, and the handling of round-off errors and such.
What is unacceptable is the propagation of such conceptual knowledge by those who say, "You're a developer, so you won't have any involvement in the structure of the database, you will be given the API to the various tables. What, this doesn't do what you want it to? It doesn't allow you to get the data you need? Work around it!"
We had an application which did not allow you to retrieve the number of rows in a table, as a matter of design principle. In order to get this number, you had to get all the elements out and count them. Oh, and if there was more data than you were allowed to retrieve in one go, you had to get it out in chunks and count the number of chunks you were able to get before getting a "no more data" error (and thence having to write more code to trap and process the 404 error that resulted). It took 3 years of constant reminders to the DB team before we could finally get them to add a facility for just retrieving the count of rows in a table.
But oh no, we lowly software engineers who were tagged as "programmers" were not allowed any input into an aspects of the design of the database, on the grounds that we were insufficiently qualified to work on database design -- whatever our background and previous experience. Wouldn't be so bad if database designers were at least basically literate, competent and above sub-human intelligence themselves, but I have never met a DB engineer who was clever enough even to use cutlery.
Admin
Another weird design is 'user_id' being nullable. What are preferences without a user ?
Admin
Defaults
Admin
BOOL and BOOLEAN are synonyms for TINYINT(1). https://dev.mysql.com/doc/refman/5.7/en/numeric-type-syntax.html
Admin
From that table, how can you tell what data is held in the boolean_val* fields? Or are we just being told that's what they hold as part of the narrative?
Admin
This is a database theory argument that people have been having for years. One side points out that NULL isn't a value, it's a missing value. The other side is based on a nullable boolean having 3 states. It comes up because values and states are so conceptually related for boolean values.
Admin
I have to say this article was rather disappointing. I mean none of this is unusual in terms of the RDBMS abuse by the uninitiated. Anyone who has worked in developer support roles or as a DBA for any amount of time has probably encounter stuff pretty similar.
The commentary was disappointing too. The whole date code thing isnt even really a WTF. There are more styles than just US and EU so using a boolean for that would be restrictive where as the approach taken probable means an 'if' can become a switch/case statement with a default clause and more date styles can be added in the future easily without a lot of changes to the database or code. Its perhaps the case a normalization n-zi would insist on a dateDisplayCodes table which might be good to enforce only known codes being inserted but the cost benefit there is somewhat situational.
We also had some commentary about the mismatch between relational models and presumably object models but got no code example to really laugh at. I have seen some hilarious stuff were reading a single property of a single object resulted in 10s of queries or required entire tables to be returned to the application so it could some logic that SQL aggregate would have done without the massive transfer; but we got none of it.
I read this site almost every day for a chuckle and this selection disappoints because there is really no WTF here, its just run of the mill poor implementation; likely by someone who simply did not know any better rather than a "what were they thinking?"
Admin
One thing that particularly annoys me with this is that id and user_id are 2 fields. There are reasons why it might have to be done but those are generally WTFs themselves. Usually it's because somebody thinks every table should have it's own auto increment id even if the table already has an unique id.
Admin
It's a horribly designed table, but what does this have to do with alterations/migrations? Or, for that matter, with being a table designed by developers? If the developers created a class like this, that would be just as much of a WTF, even without a database involved.
How much would a DBA have done outside of what simply a good developer would have done? They'd make sure the table was properly indexed, and they might question whether you need 255 characters for a date/time format. Depending on their stance in the natural key vs surrogate key holy war, they might get rid of the autoincremented key and make the user_id the primary key. Is there really anything else that wouldn't spring naturally out of decent programming practices?
Admin
Not sure that I would use a NULL user_id for the default settings. The problem is that the Unique index will allow multiple rows with NULL user_id. So then there would be multiple definitions of the 'default' settings.
I would instead use a user_id value of 0. Since auto-increment typically starts at 1, that wouldn't cause a conflict. And the Unique index would ensure that there is only one set of 'default' settings.
Admin
DBA's are morons when it comes to application design and coding. Programmers are morons when it comes to designing databases. The only time the twain shall meet is when a programmer is allowed to or determined to do so (I've never known a DBA to do so from his side except perhaps for scripting objects in DDL). I grew up in a Project Lead/ Business Design Consulting environment where I had to do both (IBM's IMS DB/DC), Many times I'd have to argue with the local DBA's to get what the project needed from a business perspective. Once in a blue moon I'd actually appreciate an alternate suggestion from a DBA and add it to my repertoire. But usually it was a catastrophe. I remember one time on a tight timeline at one of the big 3 Hartford insurance companies, I was told it would take 4 weeks to get a new column added to the test environment. I couldn't take a chance on trusting them. I copied the database (new and small) into a third environment, modded the schema myself, and finished testing in a day or two. 4 weeks later they delivered the test environment and it was all screwed up. I printed my DDL script for them and told them to do it correctly, as requested. They started complaining and saying it would be another 4 weeks. Not good for me and my clients so I whirled up a shitstorm and bumped it up all the way up the management ladder until a VP stood up and tell them to do it correctly, TODAY, and it happened. For another 20 years I had the same responsibilities. Eventually I gave up consulting and landed at a small manufacturer for a sunset career. This was heaven on earth. I was the only programmer and the only DBA (SQL Server). There is nothing better than designing databases from a BUSINESS perspective rather than a Technical perspective. Too bad it so rarely happens.
Admin
Interesting design in a few columns. Indeed feels like developers doing something quick.
Other interesting design solutions also happens in Enterprise software. Primary key columns of varchar(255) and the key values are prefixed with 64 or more the same value before it ends with a short unique string. A number prefixed with a character or two
How to get the number? Of course retrieve and update a table tracking the numbers in one transaction.
Then there are a column for the prefixed unique number (is indexed) and another column to keep the 64 characters ...
Admin
Just because something has only two values today doesn't mean it should be a boolean. There are obviously more than two possible date formats. Actually the real WTF there is calling a date format (presumably D/M/Y?) 'eu' - there are different default date formats in different European countries, and on the other hand the English language standard D/M/Y applies in lots of non-EU and non-European places too.
Re discussion about nullable booleans - it's a perfectly cromulent concept. "Is condition X true?" has 3 values: yes, no, and I don't know. It's the same as boolean? in C# or Optional<Boolean> in Java which both have valid uses.
This schema is something you get when changing the database schema is controlled by some irritating process, so you leave yourself some placeholder columns for future yes/no preferences so you can avoid having to go through the DB update process for what should be a trivial requirement. TRWTF there, of course, is the process. (Isn't it always?)
To be honest user preferences aren't really a good fit for a relational database at all and I'd be tempted to store a preferences object as a JSON blob or something like that. Having to add columns to a database table to add extra user options always feels wrong. I'm happy to defend that when one of my examples ends up on the front page :)
Admin
Yes, entirely agreed. There is a weird idea these days that 'developer' = 'person who knows OO programming and nothing else'. It's the fault of Java and C#, which are OO languages and have become extremely common, but everyone should know about other areas too. Sometimes functional techniques are better than OO, or message based architectures better than virtual method dispatch. Sometimes your application data should be in object stores, and you should know how to use lists and maps and whatever - but data persistence is almost always better done some other way, and knowing about those techniques (RDBs still being the main one) is important too.
If you're scared of SQL or Hibernate queries then you're not a full stack developer, you're just a Java guy.
Admin
"yes/don't know"=> I don't know but lets assume "Yes"
No, that definition would be for "don't know/yes". "yes/don't know" translates as "Yes, but I'm really just guessing"
Admin
Fair points and well made, I don't think all are terrible at database design. And I agree with your view about siloing stuff being bad.
At the same time, assuming a developer knows what to do with a database without actually checking is a bad assumption, and many happily admit they hate that aspect.
I have met decent DB "engineers", although yes, I've met many useless ones, but they usually come out of the DBA teams rather development, and I've never understood what design or development was doing in there. I'm a coder who understands databases, so I've also met other coders who do and who don't, I'll freely admit I'm not so hot on OO, but I can smell a bad implementation of that when I'm asked to deal with it.
Put a different way, I'd like to see diversity of skillsets in teams. People then either learn important stuff from each other, or learn there are important things they are not so good at and don't want to bother with, or turn out to be a bit useless unless really well managed.
Admin
Whoaaaa! True / False / File not found lives!
Admin
The real WTF here is MySQL and its successors. Friends don't let friends use these. At least you get almost entirely standards-compliant SQL with Postgres and SQLite.
Admin
A good developer doesn't really care what DB tool he uses for his database. A rubbish design is rubbish in MySQL same as it is in OracleSQL, and however much you blame your bad workmanship on your tools, your design will still be rubbish when you port it over onto whatever other DB you get lucky enough to pick.
Admin
Does using that alias prevent storing any tinyint in that field? Or do MySQL booleans really range in value from -128 to 127?
(The precision specifier in tinyint(1) only affects the display width, not the range of values that can be used from the underlying type.)
And " finally migrating its schema definition into scripts that can be source-controlled" followed by MySQL DESC commands. Isn't that what mysqldump is for?
Admin
Y'all never heard of ENUM?
Admin
Id argue that null is rarely useful, even in this case as null just means no value assigned (nothing more, nothing else), and if there is a 'null', there is behavior that is not understood or uncaptured in the logic of the application. Developers have an innate ability to ascribe 'meaning' to null outside of its intent. A null, should be considered a bug that needs fixed. In this case, default the inputs to true or false, which ever makes sense, and let the user change it before creating the record. Or, if you need to allow, and know, the user hasn't selected something yet, that could be modeled with an enum of choices for the 'preference', one being 'NO_SELECTION'.
Admin
Interesting fact: There was a time period where the highest performing solution for storing booleans was a nullable tinyint(0). null as false, 0 as true.
Admin
Null as a default on a non-nullable fields isn't unusual or bad at all. It just means that there isn't a single value that should be used, so when you create a record you always have to supply that field (which might be by way of an autoincrement as noted, or not). For example the application might generate record IDs and then this just says you can't create a record with no ID.
Another example is, say, a mandatory email field, which many sites use as a primary identifier these days. You don't want it to be null but there's no useful default value - it simply must be provided.
Admin
I'm disappointed nobody pointed out that the time format and date format fields should only have one of two values: RFC3339 or ISO8601. ;)
Well, someone has now.
As others here, I feel like the issue is most "developers" haven't had a class in database design, most databases don't attempt to store anything like the objects that programmers might actually want to use in their code (because that's hard), and most database administrators aren't programmers.
All of that said, I don't think I've met a single database admin who wasn't clever enough to use cutlery.
IMHO, every professional programmer should understand database basics, because they're too common and too useful to not use, but too problematic if we don't know how to use them. Similarly, every DBA should be trained in how to program in a few distinctly different programming languages. I don't care too much which ones, just so long as they're not obscure and are not all more or less the same.
But then again, I think we should require all of our cops to at least be familiar with the criminal portions of the law in their jurisdictions and should be held accountable if they break them, so clearly I'm a crazy loon.