18
qwwerty
3y

Let's say you have a MySQL database table for jobs. Each job has 1 associated ticket. You want to keep track if the ticket is closed or not. Every sane person creates jobs table, tickets table, keeps bool value for ticket state and relationship between them.

But because our database is designed by a half braindead amoeba, we have one table only, so each job has to be updated individually with a new ticket number and its state. Beacuse it sooo much faster to update (daily!) 13k jobs than just 100 tickets.
As a bonus - if the ticked is closed, the column "ticket_closed" is "No", if it's still open the value is "FALSE". Yes, both as varchar/strings.

Comments
  • 6
    A few years ago I inherited a couple MySQL databases (they were MySQL v5.0). It was a trainwreck. Some things were not normalized, used varchar for dates/books, no foreign keys, some parts were way over normalized, it was horrible.

    3 years later I finally have it in a state I'm mostly happy with. And also updated the MySQL version.
  • 1
    This sounds ridiculous even to me (and I barely know SQL).
  • 0
    @BobbyTables as someone new to professional use of SQL, what would you consider to be "overnormalisation"?
  • 3
    @TheMatter13 When you normalize a table you are usually doing it with the intent to remove nulls or get rid of duplication.

    If you had an employee table would you create a table of first names then have the employee table reference these? I'd guess it would be incredibly rare to do this unless you had a REALLY good reason to. You'd just put the varchar in the employee table. This is just an example of course.
  • 0
    Job, ticket, ticket_update

    I'd stay away from the bool approach. Will bite hard in the ass when you want to add more business logic in there. With ticket_update (id, ticket_id, type, value, updated_on, updated_by) I'd select the last update of type 'STATE' to know whether it's closed or not. Or perhaps map the last state update id in the ticket tbl itself for speed.. Idk, easy to get out of sync.

    Regardless, I feel your pain.
  • 1
    @netikras i see what you mean but details for the tickets are in a completely separate behemoth database and not mirrored to ours
  • 1
    @BobbyTables what you're saying makes enough sense to me. Never heard of the "removing nulls" approach in my database module at uni, weirdly, but I can see how putting every attribute of something in a separate table isn't useful for anyone, especially because there's rarely s useful relationship between names and anything other than employees, and no useful function of finding employees who share a name
Add Comment