Jump to content

Sky Slate Blueberry Blackcurrant Watermelon Strawberry Orange Banana Apple Emerald Chocolate
Photo

Database Table Design Help


  • Please log in to reply
5 replies to this topic
fischgeek
  • Moderators
  • 1074 posts
  • Last active: Jul 07 2015 06:27 PM
  • Joined: 20 Apr 2009

I'm setting up a table and I've been scratching my head along with Google on how to structure this. Maybe someone here can lend a thought.

What I'm doing is setting up a skills matrix. It contains a list of employees and their respective skills. I can handle the UI development once I decided on what my tables are going to look like. I don't want to create redundancy so I'm looking for 2NF or 3NF if possible. Here's my skills table (mock up):
 

Skill_ID | SkillName
1        | A
2        | B
3        | C
4        | D

Employee table (mock up):
 

Emp_ID | Skills
1        
2
3
4
5

So, that's one way I've thought of doing it, but how do I keep it "neat" when it comes time to give an employee "skill id's"? Another way I thought, which seems to work but it's definitely not 2NF is:
 

Emp_ID | Skill
1      | 1
1      | 2
1      | 3
2      | 1
2      | 2
2      | 3

that doesn't seem very efficient to me, but it would work I guess. I don't know. Any thoughts?



strobo
  • Members
  • 359 posts
  • Last active: Mar 10 2015 08:13 PM
  • Joined: 19 Jun 2012

Assuming you are using a DBMS, I'd prefer sth like Your second approach:
EmpHasSkill(Emp_ID,Skill_ID)
primary key Emp_ID, Skill_ID.
Both IDs are foreign keys related to Emp/Skill table (if it exists) with cascade as ref action.
my2c


Regards,
Babba

fischgeek
  • Moderators
  • 1074 posts
  • Last active: Jul 07 2015 06:27 PM
  • Joined: 20 Apr 2009

Great! Thank you. You don't think that's a sloppy design though because of all the entries for each employee? For example, there will be 1 entry for each skill for each employee.

 

So, if there are 10 skills and 10 employees, that's 100 entries. Does that seem right? I feel like there is a cleaner way, but I could be wrong. :S



strobo
  • Members
  • 359 posts
  • Last active: Mar 10 2015 08:13 PM
  • Joined: 19 Jun 2012

EmpHasSkill only contains valid pairs, i.e. up to 10*10 in your example.
Indeed, it is wastefull, but in your example it would be only up to 2*10*10 integers(?). So, no I don't think it is sloppy design, imho it is just a tradeoff between convenience (when working with this DB) and hdd-space.


Regards,
Babba

fischgeek
  • Moderators
  • 1074 posts
  • Last active: Jul 07 2015 06:27 PM
  • Joined: 20 Apr 2009

Fantastic. Thank you very much for the input Babba.



VxE
  • Moderators
  • 3622 posts
  • Last active: Dec 24 2015 02:21 AM
  • Joined: 07 Oct 2006

Just a nitpick: using a 2-column table to implement a many-to-many relationship between two other tables is absolutely 2NF because both columns are a candidate (composite) key.