Member Avatar for azapovjednik

Hello all,

I need to make a simple database, and then some windows application (possibly C#) connecting to it... but first...

First I need to design and model a good and functional database, right?
So I am stuck in it now :) and please help if you can. I dont want you to do my homework, all I need is to get some guidance...

Lets say, I have a table called Students.
Columns in Students table are: student_id, name, last_name, email, and so on....
Columns in Professors table are: professor_id, name, last_name, email ... etc....

there are other columns that should be there, but I am not sure whether they should be put in a separate table... for example: knowledge of foreign languages: which_language, proficieny_writing, reading, talking ....

Both tables, Students and Professors need to have information about the knowledge of foreign languages.... so how do I accomplish that? If I am talking about students, I can make columns in foreign_languages table like: lang_id, student_id, language, writing, reading, talking

but what about PRofessors?? I need to put their knowledge of foreign languages too... but where?? :)

do I have to make a separate foreign_lang_prof table?

I am really confused.... and I hope I explained myself and didnt bore you :) Thanks in advance!!!!

  • 2 Contributors
  • 3 Replies
  • 286 Views
  • 17 Hours Discussion Span
  • Latest Post Latest Post by azapovjednik

Dani AI

Generated

Short answer: model languages as their own entity and link them to people with a junction table instead of duplicating language columns on Students and Professors. As suggested, a shared Languages table is the right direction; the next design choice is whether Students and Professors share a common Person record or remain separate domain tables.

A compact, practical pattern:

Persons
  PersonId PK, FirstName, LastName, Email

Students
  StudentId PK, PersonId FK -> Persons(PersonId), EnrollmentDate, ...

Professors
  ProfessorId PK, PersonId FK -> Persons(PersonId), HireDate, ...

Languages
  LanguageId PK, Code, Name

PersonLanguages
  PersonId FK -> Persons(PersonId)
  LanguageId FK -> Languages(LanguageId)
  Reading TINYINT
  Writing TINYINT
  Speaking TINYINT
  PK(PersonId, LanguageId)

Notes and tradeoffs: store proficiencies as integers (0-5) or FK to a small ProficiencyLevels table for consistent reporting. Use a composite primary key on PersonLanguages to prevent duplicate rows for the same person/language, and add foreign keys to enforce integrity. A single PersonLanguages table is preferable to a “foreign_lang_prof” plus “foreign_lang_student” pair — it avoids schema duplication and simplifies queries. Polymorphic designs using owner_type/owner_id are tempting but weaken FK enforcement and should be avoided in relational schemas.

Operational tips: index PersonId and LanguageId for lookups, normalize language names (or use standard ISO codes), and add an optional timestamp or “verified_on” if language skill must be tracked historically. Since Normal Forms matter here, the model above keeps language data factored out so updates and searches remain consistent and efficient — useful context for the normalization reading mentioned by .

Recommended Answers

All 3 Replies

Member Avatar for pixelsoul

Bulky tables is going to be more a burden in the long run if they keep growing than a bunch of lighter tables, so I would creating more tables, rather than trying to cram a bunch into one. You're most likely going to be working with a relational database, and this is what it is made for.

You can create a language table that is used by both the students and professors tables.

I thought this Nettuts article was pretty informative and easy to understand.
http://net.tutsplus.com/tutorials/tools-and-tips/relational-databases-for-dummies/

Give that a ready through, it may just answer a lot of your questions.

Member Avatar for azapovjednik

yeah, it does help, so I guess I should read more about the normal forms....

Member Avatar for azapovjednik

noone to help?

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.