CODEWITHSUNDEEP
mysqldatabase-design
abruski
abruski

Reputation: 871

MySQL design issue

enter image description here

I think the design is straightforward so no explanation is required.

Question: Is there a way to inditcate the language of the name column in courses table? Maybe to link it with the languages table?

Edit: Or maybe separate the name-language pare in another table with id and reference it in courses table? Edit2: Course language and Name langauge may be different

Upvotes: 2

Views: 61

Answers (3)

Johan
Johan

Reputation: 76753

Question: Is there a way to inditcate the language of the name column in courses table? Maybe to link it with the languages table?

There's no need. The following query will give you what you want:

SELECT c.name, COALESCE(l.name,'default') as language
FROM courses c
LEFT JOIN courses_has_languages cl ON (cl.courses_course_id = c.course_id)
LEFT JOIN languages l ON (l.language_id = cl.languages_language_id)

Of source it would be even better if you just rename your column names so the query can be rewritten to:

SELECT c.name, COALESCE(l.name,'default') as language
FROM courses c
LEFT JOIN courses_has_languages cl ON (cl.course_id = c.id)
LEFT JOIN languages l ON (l.id = cl.language_id)

But that's just my preference.

Upvotes: 1

Larry Lustig
Larry Lustig

Reputation: 50998

Assuming that the diagram correctly models the data then no, you do not need any additional relationships.

Instead, you will retrieve the languages for a course by JOINing the tables in a SELECT statement. If this is an operation you will perform frequently, you can encapsulate that SELECT statement by CREATEing a VIEW.

Upvotes: 0

Duke Hall
Duke Hall

Reputation: 582

If I understand you it sounds like you're on the right track. Make language_id a foreign key in the courses table that points back to languages.

Upvotes: 0

Related Questions