How To Drive Rusty Screwdrivers Into Your Nemesis’s Eyeballs with Database Naming Conventions
Sorry, bro, but this is shitcode
In the world of software development, never let your focus stray, friend, or you will be pummeled to the ground by this:
SELECT “creatives”.* FROM “creatives” INNER JOIN “creatives_statuses” ON “creatives_statuses”.”creative_id” = “creatives”.”id” INNER JOIN “creatives_status_types” ON “creatives_status_types”.”id” = “creatives_statuses”.”creatives_status_types_id” WHERE "creatives".user_id=17445 ORDER BY creatives_status_types.status = ‘Approved’ DESC,creatives_status_types.status = ‘Rejected’ DESC,creatives_status_types.status = ‘Scheduled’ DESC,creatives_status_types.status = ‘Blocked’ DESC,creatives_status_types.status = ‘Published’ DESC,creatives_status_types.status = ‘In Review’ DESCJEEZUS.
Welcome to the latest installment of my #ShitcodeWednesday series, #ShitcodeWednesday ep. 4. Or shit SQL, to be precise. Because today we are going to take on such grandiose subject as database architecture and how to ruin in properly for everyone else to come on board after you *cue evil laughter*. Also, please, note, that it is indeed Sunday, and I’m evidently temporally impaired, and also should have never hard-coded specific day of the week into my series name. Anyways. Let’s kick the bull in the balls now, or however the saying goes.
Suppose, we are writing a social network. Oh shit, you’re gonna be a billionaire with that! It’s like nothing like that EVER existed before. Brilliant. It’s SO viable. We’ll call it ShitFace.
So it has, like, likes, followers, profile, feed, and posts. So of course, we need data models for all of these. But let’s not call a post “Post”. Let’s come up with a weird a$$ name that is twice longer, and that nobody knows what the fuck it means. Say, “Creative”, because it’s umm a creation of your fucking brain.
And because we are definitely planning to sell our trusty user data to advertisers for cold hard cash, let’s introduce a data model for “Ad”. But let’s not call it “Ad” because that would make perfect sense. Let’s call it “Initiative”, because some clueless individual took initiative and ruined our database. “Initiative” is essentially our client-approved template used to generate sponsored posts on the system.
Sponsored posts to ShitFace must be approved by our ad manager before showing up in the user feeds. Hence introduction of statuses. Approved. Rejected. Scheduled. You got it.
With this in mind, let’s connect to our shitface_db:
shitface_db=> select * from creatives_status_types;What’s in the table?
id | name | created_at | updated_at----+---------------------+------------+------------1 | Approved | |2 | Rejected | |3 | Scheduled | |4 | Blocked | |5 | Published | |6 | In Review | |(6 rows)
No more new records would ever be introduced to this table, and this poor thing would be left questioning its life choices at to why it is not an enum. You know, what’s better though? This.
shitface_db=> select * from approval_requirements;id | status | created_at | updated_at----+---------------------+------------+------------1 | influencer approval | |2 | post approval | |(2 rows)
This table would go on to have two ducking records until the decommissioning of the app. You won’t believe how many times I’ve seen, including very recently, tables-that-should-have-been-enums. Sorry, bro, but this is shitcode.
Now, let’s look at the models, supporting this database architecture:
class Creative < ActiveRecord::Base
has_many :creatives_statuses
endCreative has many statuses:
class CreativesStatus < ActiveRecord::Base
belongs_to :creatives_status_type, foreign_key: 'creatives_status_types_id'
endCreativesStatus is one deliciously phat model. Note the expert application of the unconventional foreign key column name here. It wouldn’t require an explicit declaration if we stuck to the conventional naming recommended by Ruby on Rails, which would be creative_status_type_id. This deviation from the norm will be really, really hard to detect. It’s the type of stealth self-sabotage your main Chinese competitor secretly hopes your uber-creative devs plant inside your codebase. Also seen, including very recently, at my many gigs.
But most importantly, let’s pay attention to CreativesStatusType model:
class CreativesStatusType < ActiveRecord::Base
has_one :creatives_status, :foreign_key => 'creatives_status_types_id'
endCue the seven-record creatives_status_types table.
And now, let’s take a look a this rad line of code:
Creative.find(1).creatives_statuses.first.creatives_status_type.statusHow terrifuckingly annoying is that. Being forced to type such gruesome multi-word brain fart every single time you are tying to do a very natural type of thing. And next thing you know you are getting checked into a mental health institution.
Instead of creating a column on Creative model that would be string “status” ie. “Scheduled” “Published” “Approved” “Rejected” “In review” we are creating a whole redundant hierarchy here that is absofuckinglutely unnecessary. Basically, an exercise in copy-paste. In order to follow this data structure we would need to create two(!) extra data models. We need to create two extra tables in the database. Cherry on top — we also need to have two extra joins in our every find call in order to pick something as simple and straightforward, something that needs to be as handy as humanly possible, a status of the record. Shitface_db is ridden with occurrences like this.
SELECT “creatives”.* FROM “creatives” INNER JOIN “creatives_statuses” ON “creatives_statuses”.”creative_id” = “creatives”.”id” INNER JOIN “creatives_status_types” ON “creatives_status_types”.”id” = “creatives_statuses”.”creatives_status_types_id”PostgreSQL even conveniently has ENUM type to help you make better decisions:
ALTER TABLE “creatives“ ADD COLUMN ENUM(‘Approved’,’Rejected’, ‘Scheduled’, ‘Blocked’, ‘Published’, ‘In Review’);But unfortunately, evil always has a way of getting in. Which is why to change post status we would need to type this anti-pickup line:
creative_status_type = CreativesStatusType.find_by_status(status)
cr.creatives_status_types << creative_status_type if creative_status_typeSorry, bro, but this is most definitely, shitcode!
That is not all, however. According to the pervy naming rules in ShitFace, any column in shitface_db must satisfy any and all of the following:
a minimum of three words to make it extra lengthy and bootiful
two plural, one singular to make it extra confusing and error-prone
when in doubt redefine every convention recommended by the language platform you are using
Examples:
“initiatives_initiative_campaigns” — right. “ad_campaigns” — wrong, too short.
“initiatives_accounts_invites “— right. “invites” — wrong, boring!
Just imagine typing this every time:
account.initiatives_accounts_invites.create!(offer_price: rand(100...900))What a feel good experience!
Couple more real-world examples from shitface_db:
class UserProfileCategory < ActiveRecord::Base has_many :user_profiles_user_profile_categories
has_many :user_profiles, through: :user_profiles_user_profile_categories
end
This:
class UserProfilesUserProfileCategory < ActiveRecord::Base belongs_to :user_profile
belongs_to :user_profile_category
end
And what about this??
class InitiativesAccountsInvitesStatus < ActiveRecord::Base
attr_accessible :initiatives_accounts_invite_id, :invites_status_type_id, :rejected_reason
endHow about this?
class NetworkAccountContentCategory < ActiveRecord::Base belongs_to :network
belongs_to :account
belongs_to :content_category
end
And now we are finally getting closer to solving the riddle of the giant blob at the top of the page. You probably want to know what it accomplishes. All it really does is finding every Po..Creative for User with ID 17445 with status. That is all.
So much for the blob suspense.
Subscribe to read more awesome articles about web development. If you like what you just read check other fun pieces in this publication too:
#Shitcode Wednesday Ep.1
#Shitcode Wednesday Ep.2
#Shitcode Wednesday Ep.3
Or tweet me at @ftert.
