24

Here we are in 2010, software engineers with 4 or 5 years or experience, still designing tables with 96 fracking columns.

I told him it's gonna be a nightmare.
I showed him that we have to use ordinals to interface MySQL with C#.
I explained that tables with more columns than rows are a huge smell.

Still, I get the "It's going to be simpler this way".

What should I do?

EDIT *

This table contains data from sensors.
We have sensor 1 with
Dynamic_D1X
Dynamic_D1Y
[...]

Dynamic_D6X
Dynamic_D6Y
[...]

EDIT2 *

Well, I finally left that job. It is a sign when the other programmer goes dark for months at the time, it is another sign when management does not realise this is a problem

20
  • 5
    Uggh, might as well be the dark ages. When will people learn how to use databases? Oct 25, 2010 at 17:09
  • 50
    What is your alternative? You can't just be exasperated over a problem if you don't have a solution.
    – TGnat
    Oct 25, 2010 at 17:11
  • 1
    I'm curious as to what's stored in each row. Oct 25, 2010 at 17:17
  • 1
    @ChaosPandion, only long after the use of traditional databases is itself a design smell. Oct 29, 2010 at 0:25
  • 3
    Well he's clearly overdesigning your database. We used to have a database with a single table with only 4 varchar columns: CLASS, OBJECT, ATTRIBUTE, VALUE. All data fit in there. Beat that! :)
    – Lukas Eder
    Dec 11, 2010 at 19:31

11 Answers 11

33

Maybe he did that for a good reason, such as performances or ROI ?.

The best thing to do, is asking him questions. With a certain amount of "why" you will certainly make him understand he is probably wrong by itself (if he really is).

I had one case myself that is not related to performances but return on investment (ROI). I had a table containing objects that had a specific value for each hour of the week (168h in a week). We had the choice to create a ObjectHour table that would contain the value, but also a key to the Object and the day number of hour number. But we also had the opportunity to put the 168 values right in the row. Probably like what your coleague did.

The developers estimated both solutions. The simple solution (168 columns) was a lot cheaper to do than its well designed counterpart. For the exact same result for the customer.

We decided to go for the simple/cheapest solution to focus our efforts of more important stuffs such as security.

We will have many opportunities to improve that in the future. Time to market was the priority for us at the time.

5
  • 3
    I agree - without additional context to 'the why', the number of columns really doesnt matter. There might legitimately be 96 things to track... or, he may be using addtional columns for 'arrays' of data (name_1, name_2) that should be broken up into other tables. Oct 25, 2010 at 17:57
  • Oh you make me remember one thing... I will add it to my answer
    – user2567
    Oct 25, 2010 at 18:01
  • 1
    "normalized" does not necessarily imply "well designed". I would consider the denormalization you describe here, to be a perfectly fine design decision.
    – user1249
    Jan 8, 2011 at 13:50
  • 1
    I agree entirely with @GrandmasterB. The number of columns is not something that can be judged independently. There are times when a great deal of related data must be stored about a single thing. What should people do? Make a tagged-data table, (id, tag, value) and INSERT ninety odd rows? If the information belongs in a table and is justified then the column stays, unless it is causing a horrendous performance issue.
    – Orbling
    Jan 12, 2011 at 21:59
  • +1 Denormalization is necessary for certain applications. I'd argue that databases are not spreadsheets. Just because they have a similar table format doesn't necessarily mean that databases should be human-readable. They're a data back-end storage and should be treated as such. Mar 18, 2011 at 12:46
17

Unfortunately your average developer still thinks of relational databases as big flat files. The only way they will get any better is if someone takes charge and leads by example. Just recently I spearheaded a major redesign of an important schema in our database and followed common relational practices. All of the sudden our stored procedures were more elegant and all of the proper indexes seemed to fall into place like they were born for it. The ego driven developer will never believe you without proof.

5
  • 2
    Sometimes it takes proof to convince someone who is already convinced they are right. +1
    – Chris
    Oct 25, 2010 at 17:47
  • 1
    Really? The average developer does this? Yikes.
    – webbiedave
    Oct 25, 2010 at 21:17
  • Perhaps big flat files is what they need in the first place ;) ?
    – Job
    Dec 11, 2010 at 15:02
  • not necessarily ego, but why should you change? The new stuff must be vastly better to "pay" for the time and effort spent in using it.
    – user1249
    Jan 8, 2011 at 13:51
  • -1 there are perfectly valid reasons for using a denormalized data table. For instance, what if you needed to shard it across many servers because the dataset grew too large or you need extremely low latency access time (say goodbye to using joins). Mar 18, 2011 at 12:39
11

Something similar was discussed previously on StackOverflow.

In general, having lots of columns on a table doesn't necessarily mean you are doing something wrong, but it definitely should raise some red flags so you look closely at the design. Sometimes a huge table is the right choice, but in many cases, other alternatives make more sense. For example, one option is to divide the storage into two tables: one table that identifies your entities and another table that is effectively a key/value store of attributes describing those entities (so you may end up with at most 96 rows for each entity). Other designs are possible as well. Talk with your teammates and figure out which solution is better depending on data normalization, code readability & maintainability (insert statements with 96 attributes to fill in?), performance implications, how often new attributes (columns) could be added or changed, how sparse the data is (how many of the 96 columns will ever be filled and how many remain NULL?), and implications on reporting. Any developer should be able to reasonably justify their design decisions and show that the cost/benefit trade off (and yes, every design decision is a trade off) is in their favor. Your responsibility is not to complain or criticize, but to propose alternatives and make sure they thought through these issues.

2
  • 1
    key value stores are almost alawys the worst choice for perfomance and queriability.
    – HLGEM
    Oct 25, 2010 at 18:00
  • 8
    Care to elaborate? Oct 25, 2010 at 18:01
9

Is it normalised with 96 columns? Does it satisfy 1st, 2nd, 3rd etc NF?

It may be that you have 96 separate attributes for on entity.

Otherwise, make him read Joe Celko on Simple Talk

5

It completely depends.

Normalized / unnormalized DB designs both have their advantages and disadvantages.

My first DB design was a normalized thing of beauty. It was flexible and extensible. It was also an incredible PITA for anyone except myself to deal with on the code level, and it was a mild PITA for me.

My next attempt was a flat structure, and it was (a) a lot faster and (b) a lot easier to code with. And it won't be a huge chore to normalize more later.

So it may be a smell but some other DB design will have its own delightful array of smells.

1
  • +1 For pointing out that often, there is not a right way.
    – Orbling
    Jan 12, 2011 at 22:01
3

Get him to read this article about technical debt. If he still decides to keep it this way, then at least you have offered a constructive opinion.

1

Looking at the (edited) post, it's clear that this is a badly denormalized table. What should you do? As I see it, you've got a few options:

  1. Scream at your coworker to learn how to do his/her/its job. Unlikely to be productive but will probably convince other coworkers not to mess with you. A reputation as a screaming maniac can be useful (don't ask me how I know).
  2. Scream at boss that coworker is an idiot. Predict disaster, then actively work to sabotage project. Blame everything on the database design produced by incompetent coworker. May lead directly to...
  3. Quit. Best if it's your idea, but #2 may lead to involuntary resignation. Try not to scrape knees on asphalt/concrete/gravel if hurled from window by enraged boss and/or coworkers. (Note that prior study is IMPORTANT here. Your chances of survival decrease markedly if bosses office is above the ground floor and you find yourself being propelled bodily out the window. Plan ahead!!)
  4. Drink heavily - or, move to California and light up (assuming prop. 19 (or whatever) passes). Nothing like a few shots and a doobie to improve one's outlook on one's coworkers (or so I've heard). (Public service announcement: KIDS! These people are professionals! DON'T try this at home!)

Share and enjoy.

2
  • I tried #4 but now it's monday and everything will be back when I reach the work place =)
    – Eric
    Nov 1, 2010 at 15:26
  • Read point one. Upvoted. Read point two, undid my upvote. Seriously, dude? Aug 28, 2012 at 8:32
0

I'm going to go out on a limb here and assume he's going to cut and paste a lot of code to work with this "New" table.

If so, you probably won't incur any additional technical debt. He's just split his share of technical debt and may be well on the way to some sort of grand unification of things.

If he's got some tried and true methodology that requires a 96 column thing consider the actual benefits of doing it a different way in this particular case. If there are none, then give him the benefit of the doubt, but remind him that next time you want to be in on the planning phase the next time he makes what we'd all consider to be a pretty dumb move.

0

Depends totally on the use cases of the application that is going to access the schema, what chunk of data is needed at a time. In some way it might justify the table design.

0

I'd send him to the stone ages and force him to use files or at least teach him how to use blobs.

Really, 96columns... That cant be right, ever. Maybe an ORM would help. (Unless you need performance but you then can have someone who understands DB better to handle it)

0

I'm going to get downvoted all to hell for this, but this is exactly why I separated the responsibilities of data modeling and software engineering in my shop. Programmers rarely seem to think in the forms of sets and instead focus on data usage (rather than maintaining 3rd normal form, indexing, or other DB performance issues). We as programmers also tend to disagree more on those DB architectural decisions than we perhaps should, based on our inexperience in pure data modeling/architectural issues. IMHO, I like that I have data architects and modelers that take requirements, build tables/procs/etc., and leave dealing with the outputs up to me.

Without knowing the actual reasons for this design though (I've worked on weather sensors that have well more than 96 different numerical outputs = large number of table columns) ... this just seems like venting a pet peeve.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.