Programming Stupidity: Database Schema

I’m often working on little personal projects alongside the projects for work. Personal projects though, gives me a chance to try new things, experiment, learn new techs, etc. I’m also much less careful so end up doing really stupid things (in hindsight). So I thought it’ll be nice to share some of those… adventures. Keep in mind, I’m no pro… so if you know of better ways of doing something, let me know in the comments!

Working on a trouble ticket system the other day, calling it Tick-IT ha! Get it????? From experience, whenever I write projects that uses a database I always end up adding new features that require modifications to the table schema. The changes usually involves one of two options: 1) adding a new table that shares a key with the existing tables in the best case 2) requiring the adding of a new column which means migrating all existing data in the table into a temporary table, deleting the original, then creating the new table, moving the data from the temp table to the newly created one. A total pain in the fucking ass.

In an attempt to be smart, I decided that I would only have 3 columns in the tickets table which holds all ticket information: ID (unique ticket ID), ticket number, and ticket data (JSON blob that stores all other information on the ticket). Brilliant right? Need to add new fields? Just add to the JSON object! Nope… See, the problem that I didn’t think about was the JSON data isn’t searchable using SQL. It would require a full text search. So… Not so smart after all. 

Till next time…

Leave a Reply

Your email address will not be published. Required fields are marked *