Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question

2 Answers 2

up vote 4 down vote accepted

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.

share|improve this answer
1  
Thanks, just the clarification I was after. –  BlackBox Sep 27 '13 at 17:07

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

share|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
1  
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
    
I am in the middle of doing natural key vs identity() join tests on a reasonably large dataset. Once I have completed these tests, I will publish my results. –  Max Vernon Sep 28 '13 at 16:41

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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