-1

I am building a Postgres database which has the following two tables:

Projects (id, startDate, etc...) and Employees (id, name, etc...)

I want to keep track of the types of contributions that an employee adds to a project. For example, employee #1 might be an "engineer" on project 1 and a "manager" on project 2. I also don't want to restrict the number of contributions an employee can make to a certain project. So employee #1 could be both a "engineer" and a "manager" for a single project.

My first instinct was just to have a many to many relation between the two titled ProjectEmployees or something and store the projectId, employeeId, and a contributionType as a string which would only take on values from an enum as to not have to deal with misspellings or any related issues.

My main question is just whether or not this is a bad practice. My other thought was to split up each contribution type to its own table. So instead of an EmployeeProjects table, there would be tables such as ProjectEngineers, ProjectManagers, etc... and instead of storing the contributionType as a column, it would be implicit in the table I'm using, and the table only has to store projectId and employeeId. There are many more tables in this database which have a similar sort of relationship where there are many to many relations between different tables, but each relation could be one of many "types" of relations. Is it wiser to split these all into separate tables for each type of relation? Or is it better to just keep track of the relation type in a more general table like my first idea?

My desired result is to just be able to efficiently see which all project contributions (and types) an employee worked on as well as to see all contributors + contributor types for a project.

Timur Shtatland
  • 12,024
  • 2
  • 30
  • 47
Dagman
  • 61
  • 5

2 Answers2

5

Use the many to many relation as in your first idea, which in my opinion is a good practice.

Avoid the creation of one table per contribution type as is not scalable and flexible. I.E. if one day you'll have a new contribution type, with the 2nd option you will need each time

  • to create a new table
  • to write the new table management logic
  • proceed with a new deploy of your sw

About the topic of storing the contribution types on a table (with id and description) or as a constraint with contribution types strings enumerated, in my opinion both are valuable solutions.

But if you think to manage contribution types in your software (in a first release or in the future) maybe having a table with contribution types anagraphics can be better. It depends by your design and requirements

Massimo Petrus
  • 1,881
  • 2
  • 13
  • 26
4

Make a table to store contribution types as strings (manager, engineer, etc) and contribution type id (numeric id). This prevents misspellings.

Make a table to store contributions with columns: employee id, project id, contribution type id (you may want other columns there, but it should be unique on the combination of these 3 columns). Do not store contribution types as strings in a table like this, since, as you correctly mentioned, this may allow misspellings. Another reason is to save disk space. An extra join with a small table of contribution types is a small price to pay.

Timur Shtatland
  • 12,024
  • 2
  • 30
  • 47
  • I disagree with the point about using strings. If space isn't an issue, strings are much more readable than numbers. You can put a check constraint on the column to prevent misspellings. – Blue Star Aug 14 '20 at 04:32
  • Using strings or generated key either works, but you must avoid misspellings as noted. If the number of contributions types is low and stable use a check constraint or an enum, Alternately an FK reference a table a unique constraint of the string value. Use this last only if you expect expansion of the number of contributions types. – Belayer Aug 14 '20 at 20:14