sql - Normalizing Table Data -

currently, have setup tables , relationships:

situation:

there employees take courses. sign in on roster sheet unique course. these courses can taught several times different instructors. each course may have multiple rosters different students (different rosters different sessions). there other specifications part, employees/instructors table care about.

my problem there can instances instructor employee. difference between 2 tables usage of instructorid rather employeeid. creates bit of redundancy data. want avoid , try build out based on best practices. there way combine data in 2 tables @ point, query tables instructors teach class employees?

i thought of adding in "isinstructor" field yes/no in employees table. check see if person has yes or no. however, feel though terrible idea. suggested adding in prefix employeeid designate instructor. suggested perhaps add in field identifier instructors employees.

essentially, want know best practice approach problem be. should keep data separated , in 2 tables or should combine them , add in? suggestions rest of structure appreciated well. first question on here if more details needed let me know.

both instructors , employees people. (i deliberately used varchar(5) can't use in production without @ least appearing think names.)

create table people (   p_id integer primary key,   first_name varchar(5) not null,   last_name varchar(5) not null,   dept_id integer not null references departments );  insert people values (1, 'robin', 'mings', 1), (2, 'ora', 'black', 1), (3, 'sheri', 'johns', 2), (4, 'dex', 'sims', 3); 

best practice: if want use plurals table names, use plurals. if want use singulars table names, use singulars. (i use plurals.)

use tables capture details whether person employed , whether person instructor. least need know id number. robin mings , sheri johns instructors.

create table instructors (   p_id integer primary key references people );  insert instructors values  (1), (3); 

sheri johns, instructor, not employee. others are, though.

create table employees (   p_id integer primary key references people );  insert employees values (1), (2), (4); 

in tables "employees" , "instructors", choice use "p_id" (the column name in "people") or column name "emp_id" application-dependent.

these courses can taught several times different instructors.

no, can't. design allows 1 date per course. distinguish course class. class course that's offered under particular instructor, , starts on particular date.

create table courses (   course_id integer primary key,   course_name varchar(5) not null unique,   course_desc varchar(5) not null,       -- unique?   course_objectives varchar(5) not null  -- think whether deserves own table. );  insert courses values (1, 'ap101', 'desc', 'obj'), (2, 'ar101', 'desc', 'obj'); 

best practice: don't use words list in table names. nobody says, "are supposed in class? let me check roster list." also, semantics count. if use "roster_list" , "course_list" (which in case not lists of courses or of rosters), why refrain using "employee_list", "department_list", etc? list noise here. choose better words.

create table classes (   course_id integer not null references courses,   instructor_id integer not null references instructors (p_id),   start_date date not null,   -- have no idea completion_time means, omitted it.   primary key (course_id, instructor_id, start_date) );  insert classes values (1, 1, '2015-04-01'), (2, 3, '2015-04-15'); 

in experience, employees sign classes, not courses. (you used word sessions, didn't use in design.) depending on application, might need more tables one.

create table class_rosters (   course_id integer not null,   instructor_id integer not null,   start_date date not null,   employee_id integer not null references employees (p_id)     on update restrict on delete cascade,   primary key (course_id, instructor_id, start_date, employee_id),   foreign key (course_id, instructor_id, start_date)     references classes (course_id, instructor_id, start_date)      on update cascade on delete cascade );  insert class_rosters values  (1, 1, '2015-04-01', 2), (1, 1, '2015-04-01', 4), (2, 3, '2015-04-15', 1), -- instructor taking class. (2, 3, '2015-04-15', 2), (2, 3, '2015-04-15', 4); 

is there way combine data in 2 tables @ point, query tables instructors teach class employees?

-- instructors teach class has ora black in it. select distinct cr.instructor_id class_rosters cr inner join people p on p.p_id = cr.employee_id p.first_name = 'ora' , p.last_name = 'black';  -- instructors teach class has both ora black  -- , robin mings (an instructor) in it. students (   select p_id    people   (first_name = 'ora' , last_name = 'black')      or (first_name = 'robin' , last_name = 'mings') ) select instructor_id class_rosters cr inner join students s on s.p_id = cr.employee_id group course_id, instructor_id, start_date having count(*) = (select count(*) students); 

 instructor_id -- 3