0

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.

4
  • 2
    One way to look at it: There is a many-to-many relationship between employees and skills, and skill level is a property (attribute) of such relationship.
    – mustaccio
    Jan 9, 2019 at 19:26
  • Hmmm - I forgot about that type of relationship (obviously novice!). I will start there and re-create my data model to reflect this..hopefully this gives me a better start. Thank you! Jan 9, 2019 at 19:33
  • Basics for a many-to-many relationship: stackoverflow.com/a/9790225/939860 Jan 9, 2019 at 20:57
  • Thank you, Erwin. I will go through this now for any takeaways I can get. Jan 9, 2019 at 21:02

1 Answer 1

0

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.

1
  • Thank you, Walter. I will look into the star schema and see if it is something I can wrap my head around. If it'll be the best solution, I'm more than willing to take the time to learn it. Jan 10, 2019 at 16:11

Your Answer

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

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