Many to Many relationship implementation

For these types of scenarios I usually find it safer to just make 3 separate tables and handle their relationship entirely in the DB. Let's look at the existing example of employees and projects. You would make one table for employees, one for projects, and an additional mapping table. The mapping table will have at least 3 columns, a primary key, a foreign key to the employee table, and a foreign key to the projects table. These should be enforced through foreign key constraints. You can then make a CDT for each table.

I also recommend making views that join through the mapping table. For example, let's say you wanted to get all the projects for a given employee without a view. You could query the mapping table by employee ID, which would yield a list of project IDs. Then you could query the project table by these IDs. This would require two queries though. A better method would be to join these tables together through the mapping table, and query this view by employee. You will also need to make a CDT for this view.

For these types of scenarios I usually find it safer to just make 3 separate tables and handle their relationship entirely in the DB. Let's look at the existing example of employees and projects. You would make one table for employees, one for projects, and an additional mapping table. The mapping table will have at least 3 columns, a primary key, a foreign key to the employee table, and a foreign key to the projects table. These should be enforced through foreign key constraints. You can then make a CDT for each table.

I also recommend making views that join through the mapping table. For example, let's say you wanted to get all the projects for a given employee without a view. You could query the mapping table by employee ID, which would yield a list of project IDs. Then you could query the project table by these IDs. This would require two queries though. A better method would be to join these tables together through the mapping table, and query this view by employee. You will also need to make a CDT for this view.

This is basically the only way to do it if you expect data to change. Otherwise, you won't be able to delete one employee without deleting all of his projects, and then deleting all of the employees associated with those projects, and then all of the projects associated with those employees, and all employees associated with those, on and on until all or nearly all of your database is gone.

Or you could delete one employee, all of his references in the mapping table, and you're done.