New to DB's - How to set out tables given requirements

Hi all,

I’m very new to databases and was wondering if you could help with how to set out the tables and their entities given the following scenarios (I’ve included where I’ve got to so far - so please correct me where I’ve gone wrong and any links to why! :slight_smile: )

Requirements:
Each employee works in a department.
An employee may possess a number of skills.
A department may participate in none/one/many projects.
At least one department participates in a project.
An employee may be engaged in none/one/many projects.
Project teams consist of at least one member.

So far I have something like this:
Employee Table [employeeId, departmentId, teamId]
Projects Table [projectId, departmentId, teamId]
Department Table [departmentId, projectId]
Skills Table [skillId, employeeId]
Team Table [teamId]

But the longer I spend on it, the more wrong I get it - can someone please help? :slight_smile:

first thing to do is set up tables for the entity types, such that each has a Primary Key and one or more attributes such as name –

  • Employees [ employeeId , employeeName ]
  • Departments [ departmentId , departmentName ]
  • Projects [ projectId , projectName ]
  • Skills [ skillId , skillName ]
  • Teams [ teamId , teamName ]

now do the relationships, which are either one-to-many or many-to-many

one-to-many relationships can be implemented easily by adding a Foreign Key to the “many” entity table, but many-to-many relationships require a"junction" or relationship table

this is many-to-one, i.e. one-to-many, so add the FK to Employees

  • Employees [ employeeId , employeeName , departmentId ]

presumably several employees can have the same skill, so this is many-to-many and we need a junction table

  • EmployeeSkills [ employeeId , skillId ]
  • DepartmentProjects [ departmentId , projectId ]
  • EmployeeProjects [ employeeId , projectId ]
  • TeamMembers [ teamId , employeeId ]

the only thing we haven’t covered yet are the two “at least one” conditions, but i’ll postpone talking about those for the time being

2 Likes