Member Avatar for 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

  • 3 Contributors
  • 3 Replies
  • 438 Views
  • 8 Hours Discussion Span
  • Latest Post Latest Post by azapovjednik

Dani AI

Generated

As correctly suggested, avoid adding both student_id and professor_id columns to the same language row and leaving one NULL. That pattern makes referential integrity awkward and forces lots of NULLs. A normalized many‑to‑many model is simpler and safer: either a single canonical person (or people) table with a join table for languages, or role‑specific join tables if the domain truly requires strictly separate student/professor records.

Recommended approach (practical and flexible): keep one person record per human, a languages table (store an ISO code to prevent duplicates), and a single junction table such as person_language that maps person -> language and holds proficiency data. Enforce a composite primary key (person + language) so duplicates cannot occur, add foreign keys for integrity, and choose a small, documented proficiency model (either a numeric scale 0–5 or a lookup table that maps codes to descriptions). This makes queries like “which languages does a person know?” and “who speaks French at level >= 4?” straightforward and indexable.

Tradeoffs and quick rules:

  • If keeping separate students and professors tables is required, ’s two role‑specific skill tables work as a short‑term solution, but they duplicate schema and complicate cross-role reporting (UNIONs become common).
  • Avoid polymorphic FKs (owner_type + owner_id) if DB‑enforced referential integrity is desired — most engines can’t enforce those constraints.
  • Use ON DELETE rules deliberately (CASCADE vs RESTRICT) and add indexes on language columns used in lookups.

Example sketch (illustrates uniqueness and simple level instead of multiple binary columns):

CREATE TABLE person_language (
  person_id INT NOT NULL,
  iso_code CHAR(2) NOT NULL,   -- ISO 639-1
  level TINYINT UNSIGNED NOT NULL DEFAULT 0, -- 0..5 documented
  PRIMARY KEY (person_id, iso_code),
  FOREIGN KEY (person_id) REFERENCES person(id),
  FOREIGN KEY (iso_code) REFERENCES languages(iso_code)
);

Document the proficiency scale, add an index for reverse lookups (language + level), and consider migrating existing student/professor rows into a unified person table if long‑term maintenance and queries across roles will be needed.

Recommended Answers

All 3 Replies

Member Avatar for 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.

Member Avatar for 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:

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

Member Avatar for 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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.