Database Design - multiple tables

Question

I am trying to build a database that has all of our employees, their skills, and the level of their skills. The idea is that a manager could go in, type in a skill, and relevant employees are returned. I am finding that I am getting stuck on PK's and FK's - and really how to go about it altogether.

So far, I believe I need to create 3 tables (Employees, Skills that will be separated into 4 general areas, and Skill Level which will be 5 different options).

With that, I am at a loss for how I can connect them in a way that each employee can be attached with multiple skills that also shows at what level they're at.

Not necessarily looking for someone to do the work for me, but if someone can clarify whether or not this logic will work and how to get on track - I probably can research the sql code for it.

Thanks!

---EDIT---- Someone raised a good point. A many-to-many relationship seems like the best, but the skills table is something I am not understanding.

If I create a table/entity [Skills] that has the four attributes (financial, technology, creative, other) with specifics listed in each column (i.e. financial would have accounting, recon, etc. and so on), would the skill level be another table? OR Would there be separate tables for the four skills and the specifics listed as columns with the skill levels under each? I'm going crazy trying to figure out the best way to go out this.

Answer 1

I hesitate to recommend this answer to a self described novice, but it seems to fit the case pretty well.

If were working this problem, I would go with a star schema approach. There would be three dimension tables: Employees, Skills, and Levels. And there would be one fact table, with foreign keys to all three tables, and no other columns (aka fields).

This particular fact table only conveys a count, and no other measure.

I might go ahead and make the hierarchy of skills a snowflake off of the skills dimension.

If you are new at database design none of this would be familiar to you at all. When people begin to work with star schemas, they usually have a few years of database design under their belt. But just in case you have access to a good tutorial on star schema, you might want to follow through here.

In addition, if I could fit it into the budget, I would want to buy some sort of business intelligence software that would allow the managers could use to browse this data without learning the table structure. Failing in that, I would just try to build in the kind of drill down capabilities the managers need into my app.

I hope all of this isn't mystifying. Don't worry if it is. Basically the fact table is capturing a many to many to many relationship, which probably isn't covered in your learning materials.