In raw form, I have data with 2 columns: skills in welsh and stats for that skill. For storing these information in a database, would it be best to combine the stats with the categories, or split them up as below?

|  SKILL_ID   |                SKILL
|      0      |            CAN'T SPEAK

|  SKILL_FK   |                COUNT
|      0      |                 235

Instinctively, the laid out method seems to be correct, but for such a simple task, also seems like overkill?


2 Answers 2


Without knowing anything else about your system, that seems like total overkill. You'd be better off with a single table that has ID, Skill, and SkillCount.

Having two tables for such a simple schema will result in unnecessary work for both the DBA and developer. The database engine will need to perform extra work to JOIN the two tables whenever you require reading both Skill and SkillCount at the same time.

If you had a really, really large dataset (think hundreds of millions or billions of rows) and you regularly needed to access just the Skill and rarely needed to show the SkillCount field, then perhaps you might consider using your design.


You ask about normalization.

If you start with this

skill        count
Can't speak  235

it should be clear that the only candidate key is "skill". This relation is in 5NF.

There is no normalization guideline that introduces new columns like "skill_id". (There's no such thing as "now I have an ID number" normal form.)

  • +1. I added the ID field since I assume (we both know where that goes!) the system will need the ability to join this table with other data, and in a big enough set of data, an INT field is far more efficient for joins than a string-based field.
    – Hannah Vernon
    Commented Sep 28, 2013 at 1:16
  • 2
    Have you measured the efficiency? (I have. You'll probably be surprised.) Commented Sep 28, 2013 at 1:44
  • I have not measured it. I guess the level of difference would vary dramatically with the length of the natural keys. I'd be very interested in hearing about your results - do you have a blog entry somewhere or something?
    – Hannah Vernon
    Commented Sep 28, 2013 at 2:14
  • Your assumption was correct, I do need to connect this data with approximately 3 million rows, so I'm using a "char" (note the quotes) as a lightweight and fast index to the data. But again,I'd be interested in the same insight that Mike has. Thanks again.
    – BlackBox
    Commented Sep 28, 2013 at 12:08
  • 1
    I've written about it here and on SO. Think about a "lookup" table--one column of data and an id number. Now drop the id number, write foreign keys to reference the data, and you avoid a join altogether. With other tables, you avoid a join every time the data you need is in a referenced key. (I was surprised how often this happens.) The tradeoff is between join performance and disk I/O. Prototypes indicated that natural keys would perform better in the test suite (dozens of queries) for decades without improvements in hardware (like SSDs). Commented Sep 28, 2013 at 19:45

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.