0

I'm trying to make my first app with flutter/dart and a rest-api webservice with a connection to a mysql database. The app should contain a list of employees that a company has and a list of projects that the employees are working on. Besides that, the list of projects should also have another list that shows the employees that are working on that project.

My thoughts at first: table employees: employee_id | name | projects

but then i realised that employees can work on more than one project and i tried to store multiple values in the projects column, but that didn't work. Can someone help me with this issue?

I'm fairly new to mysql, so please try to explain it in a noob-friendly way. Thanks! :D

Quantum
  • 17
  • 1
  • 1
  • 5
  • Please read https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad it explains why it isn't a good idea – nbk Mar 21 '20 at 14:51

1 Answers1

0

How about create a table name projects and employer_projects, when you need all the projects that user has joined, just query from table projects JOIN with table employer_projects. Table employer_projects will store the connection between employees and projects;

employees <--> employer_projects <--> projects

Example:

Query all project of an employer_id with employer_id

SELECT * FROM projects p JOIN employer_projects ep ON p.id = ep.project_id WHERE ep.employer_id = ?

That could be easier for your to search & query in the future!

Here is some sample sql to insert new record (ID is auto increment by DB right?)

Add new employees

INSERT INTO `employees`(`field_1`, `field_2`, `field_3`, ...) VALUES ('value 1', 'value 2', 'value 3', ...);

Add new project

INSERT INTO `projects` (`name`) VALUES ('test');

Add new employer_projects connection

INSERT INTO `employer_projects` (`employer_id`, `project_id`) VALUES ('1', '2');

Are you using PhpMyAdmin? All of the sample sql will available in tab SQL

One last thing. If the value and field data type in DB is int, you dont need an quote ' around value like '1', just 1 is okay.

Van Tho
  • 618
  • 7
  • 20
  • You can also store the project as string like `project1|project2|project3` in table `employees`, but it's not an good idea and difficult to add/remove data from this field. – Van Tho Mar 21 '20 at 13:51
  • Thanks, but how do I store these things now? For example. if someone makes a new project, where do i have to save that project? In projects or employer_projects? – Quantum Mar 21 '20 at 15:03
  • Employer with id 1 create a new project name "test", you insert a new record to table projects. Then you have the inserted id as `project_id` (maybe 2). Then insert another record to table `employer_projects` with value project_id (from previous query - 2) and employer_id (from user created this project - 1) – Van Tho Mar 21 '20 at 15:06
  • And how do I add more employees to that project? – Quantum Mar 21 '20 at 15:21
  • If employees with id 5 want to join the project `test` above, just add an record to table `employer_projects` with value project_id is 2 and employer_id is 5. With this database structure, you can manage all current projects by table `projects`, and all users joined project by query join from table `employer_projects `. Moreover, how to delete a project? Simply delete it from `projects` table, and all records has that project_id in table `employer_projects ` – Van Tho Mar 21 '20 at 15:27
  • Thanks! One last question: how does the SQL Syntax look like for the add employee function? Like how would u write it in the Query Tab? – Quantum Mar 21 '20 at 15:33
  • I've edited my answer (adding some sample sql). If you have `id` is `AI - auto increment` in your DB (MySql), you can use the above sql command – Van Tho Mar 21 '20 at 15:43