Design Scenario

I am currently developing a mentorship application where mebers are mentor, mentee or both who work on some projects and possess unique member id. Most of the projects contain only one mentor and one mentee where the mentor helps the mentee during the project life cycle.

Now in my project table, I have kept a column named proj_opener who may be mentor or mentee. As every project may have more than one mentors and mentees, I was thinking how should I keep the provision for storing mentor and mentee ids. I found the following options.

Options to Choose From
1. Keep two columns named proj_mentors, proj_mentees and store the mentor/mentee ids as comma-separated values (like "mnte-0001, mnte-0003, mnte-0005" etc. for mentees column and "mntr-0007, mntr-0009 for metnors column) .

2. I keep two foreign key fields in project tables and create two separate tables and store the values in the later tables that will contain one or more records.

3. Any other option that you may suggest here.

Comment
Second option seems reasonable but I am already using a member table where all the members info are stored and I am confused whether creating two new tables will unnecessarily duplicate data.

I want to the theory behind your sugesstions and practical considerations. Any help will be apprecaited.

Option 1 is a definite no-no as far as relational databases are concerned.
The correct solution to have 1 or 2 separate tables for the mentors and "mentees" (is that a real word?):

create table projects( project_id ... primary key, ... );
create table mentors( project_id references projects, mentor_name ...,
primary key (project_id, mentor_name), ... );
create table mentees( project_id references projects, mentee_name ...,
primary key (project_id, mentee_name), ... );

So if a project has 2 mentors and 3 "mentees" you will insert 1 projects record, 2 mentors records (linked to the project) and 3 mentees records (linked to the project).

A variation would be to combine mentors and mentees into a single table project_people:

create table project_people( project_id references projects, person_name ..., person_type varchar2(6) check (person_type in ('MENTOR','MENTEE')),
primary key (project_id, person_name), ... );

Now you will insert 5 records into project_people, i.e. 2 mentors and 3 mentees.

Tony is right on the money, but as another variation I would add that I think the Mentee should have their own table that is constructed so as to be a child to the Mentors table (which is itself a child to the project table). This way you could link back to the project and see all the players, but you could also link to discover which Mentee were being watched over by which specific Mentors.

Excellent response! Thanks.