3

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?

WELSH_SKILLS_TABLE
----------------------------------------------------
|  SKILL_ID   |                SKILL
----------------------------------------------------
|      0      |            CAN'T SPEAK
----------------------------------------------------


WELSH_STATS_TABLE
----------------------------------------------------
|  SKILL_FK   |                COUNT
----------------------------------------------------
|      0      |                 235
----------------------------------------------------

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

Thanks!

4

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.

| improve this answer | |
4

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.)

| improve this answer | |
  • +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. – Max Vernon Sep 28 '13 at 1:16
  • 2
    Have you measured the efficiency? (I have. You'll probably be surprised.) – Mike Sherrill 'Cat Recall' Sep 28 '13 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? – Max Vernon Sep 28 '13 at 2:14
  • @MaxVernon 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 Sep 28 '13 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). – Mike Sherrill 'Cat Recall' Sep 28 '13 at 19:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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