SQL table relationship diagram

[azapovjednik]

Hello

I have two tables: Students and Professors (both with usual info like name, last name, email... etc)

I also need to have info about their knowledge of foreign languages, and I guess that should be a third table (languages for example)
it should look like: Language_id, Language, Speaking, Writing, Listening,
Now, the tricky part for me is, how do I connect Language table with Students and Professors...
If I make another column foreign key Student_id.... that way I can connect with Students
but how do I also connect it with Professors table?
Do I also put foreign key Professor_id?
but what if I am inserting the data for some student, and his languages... and I insert student_id.... what of professor_id in the same row? can I leave it empty? Or is there someother way to accomplish it?

thanks

[Kaza_1]

I probably wouldn't have separate tables of students and professors, since some graduate students might have status as both student and faculty. Also, some professors might be signed up for a class or two. I'd have a person table with id (key), name, isstudent, isprofessor -- the latter two being boolean.

The table of languages wouldn't be the place for speaking, writing, listening, as those are not properties of languages. That would be the many-to-many relationship in a table called person_language, with foreign keys for person and language (and a unique index on that pair of columns), as well as the columns for speaking, writing, etc.

[cereal]

I would go reverse: a table for students, another for professors, a single language table and then a language_skills table for students and another for professors. For example:

create table students(
    id int unsigned not null auto_increment primary key,
    fname varchar(100),
    ...
)

create table student_language_skills(
    student_id int unsigned not null primary key,
    language_id tinyint unsigned not null,
    writing tinyint not null default 0,
    reading tinyint not null default 0,
    speaking tinyint not null default 0,
    listening tinyint not null default 0,
    ...
)

create table languages(
    id tinyint unsigned not null auto_increment primary key,
    name varchar(100) not null,
    ...
)

create table professors(
    id int unsigned not null auto_increment primary key,
    fname varchar(100),
    ...
)

create table professor_language_skills(
    professor_id int unsigned not null primary key,
    language_id tinyint unsigned not null,
    writing tinyint not null default 0,
    reading tinyint not null default 0,
    speaking tinyint not null default 0,
    listening tinyint not null default 0,
    ...
)

An example:

• http://sqlfiddle.com/#!2/62072/8

Then you could also use UNION to create a single result set.

[azapovjednik]

hello,
I want to keep this database as simple as possible :)

Thank you for your suggestions. I ll take this person_language advice of yours. Thanks a lot