0

I was just thinking about the following:

I have a table called employees and also a table called projects. Every employee has an id and in the projects table there is a cell called staffedEmployees in which I want to store the respective employee IDs. I could just insert the IDs as a string separated by comma like this 1,2,3,4, ... but this would require more work on the processing side as I would have to retrieve the string, separate it and then work with the array/object. So I was wondering: Can I store something like an array/object of such IDs in the staffedEmployees cell?

I am working with PHP and SQL/MySQL.

JoansLangLas
  • 19
  • 2
  • 1
    Never, ever store data as comma separated items. It will only cause you lots of problems. – jarlh Apr 19 '22 at 10:32
  • 1
    Rather create a separate table, e.g. employee_per_project which holds the project_id and the employee_id – DarkBee Apr 19 '22 at 10:33
  • If you're ever going to try for some "store many related data in some column of a single row", do it in something that's natively supported, like json, xml or arrays. And even then, thing twice about it, because it still makes it a pain in the ass to use – Caius Jard Apr 19 '22 at 10:53
  • Now I understand why "Database engineering" is a thing. Such structures can become complicated I guess. But good to know that I need to work like this rather. – JoansLangLas Apr 19 '22 at 11:02

3 Answers3

1

You have to use a third table, in which you store the correspondig IDs that can join together the two tables.

For example:

CREATE TABLE staffedEmployees(
  `EmployeeId` INT NOT NULL,
  `ProjectId` INT NOT NULL,
  KEY(`EmployeeId`),
  KEY(`ProjectId`));

Then store every pair in this table, for example:

1 - 1
1 - 2
2 - 2

Then when you need to fetch which project connected to an employee, simply join the tables:

SELECT employee.Id, employee.Name, projects.Id, projects.Name 
FROM staffedEmployees
LEFT JOIN employee ON (staffedEmployees.EmployeeId=employee.Id)
LEFT JOIN projects ON (staffedEmployees.ProjectId=projects.Id)
WHERE staffedEmployees.EmployeeId=1;

You can filter on projectId, or any other field from the two original tables as well.

Fenistil
  • 3,734
  • 1
  • 27
  • 31
  • 2
    For "have to" read "most DB professionals would strongly recommend to" – Caius Jard Apr 19 '22 at 10:56
  • That would work but I find that extremely inconvenient, even moreso than storing the IDs in strings. I have about 5 or 6 tables that include a cell where IDs are to be stored which means that I have to create 5 or 6 more tables to connect the respective tables? Well, okay, I will do it then. – JoansLangLas Apr 19 '22 at 10:57
  • 1
    @phili if you think creating a few tables is inconvenient, then please read [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) to discover how storing data in a comma-separated string will cause you a lot more inconvenience in the long run. And then learn about database normalisation, to understand that the solution in this answer is the correct, canonical, standard, working approach to this scenario, and has been for decades. – ADyson Apr 19 '22 at 11:13
  • 1
    Dupe applied. @ADyson – mickmackusa Apr 19 '22 at 11:14
0

You need to convert your "projects" table into 3NF format. That will solve your problem

Roshan Birar
  • 151
  • 1
  • 3
0

I agree with the Comments you got up there.

  1. Storing data (id's) in comma separated format makes it difficult to retrieve the data in most of the cases.
  2. Creating a separate table and storing the respective projects and employee id's would be more convenient.

Ex. employees table-

emp_id emp_name
1 Peter
2 Tom
3 Ronald

Projects table-

project_id project_name
1 Project 1
2 Project 2
3 Project 3

Solution: projectEmployees table -

id emp_id project_id
1 2 3
2 1 1
3 3 2
4 2 2
5 1 3
Anup Patel
  • 74
  • 7
  • and I'd call the table ProjectEmployees or EmployeeProjects. Whether there would be a surrogate key or whether the PK would be the compound of emp_id and project_id is another debate.. – Caius Jard Apr 19 '22 at 10:54