How can I implement a language system in my mysql database?

Question

I am creating a video player application with php and mysql.

The application has videos that are gathered in playlists like this:

Playlists table:

+----+------------------+------+
| id | name             | lang |
+----+-------------------------+
|  1 | Introduction     |   1  |
+----+-------------------------+

Videos table:

+----+--------------+-------------+
| id | name         | playlist_id |
+----+--------------+-------------+
|  1 | Video1       |           1 |
|  2 | Video2       |           1 |
+----+--------------+-------------+

It worked fine until now, because I need to build a searcher that finds videos depending on its name and language.

I though of creating another field called lang in the videos table, but then I realize that this maybe would contradict the normalization database rules. Because I would be repeating unnecessary information.

What can I do to select the videos without creating another field? Or do I need to create a new one with the repeated information?

EDIT: JOIN LEFT both tables is not a solution, because I maybe add in the future a new table that links to playlists such as courses.

What do you mean "repeating unnecessary information"...you need to search by lang, so how would adding lang to this table repeat unnecessary information? — Blue, Jun 25 '17 at 08:59
The videos lang field would have the same value as the playlist lang field. — Xavier Carrera Gimbert, Jun 25 '17 at 09:01
`JOIN` the playlists and videos table to get all fields you need? — apokryfos, Jun 25 '17 at 09:06
I thought about it, but if I add a new table called `courses` that relates to playlists for example? — Xavier Carrera Gimbert, Jun 25 '17 at 09:11

score 0 · Accepted Answer · answered Jun 25 '17 at 09:10

0

You can make LANGUAGE_ID COLUMN in Videos table,which will foreign key references to Playlists.lang .

Try above solution.

Hope this will help you.

answered Jun 25 '17 at 09:10

Sagar Gangwal

7,544
3
24
38

No, that doesn't make any sense. The video's language ID doesn't refer to a playlist -- it refers to a language. – Jun 25 '17 at 09:44
@duskwuff if we make relationship between `Playlists.lang` = `Videos.LANGUAGE_ID ` then it is possible to achieve expected business logic. – Sagar Gangwal Jun 25 '17 at 09:59
@XavierCarreraGimbert Exactly..!! – Sagar Gangwal Jun 25 '17 at 10:00
@duskwuff The playlists and the videos within have the same language – Xavier Carrera Gimbert Jun 25 '17 at 10:01
1

@SagarGangwal ,(https://stackoverflow.com/questions/18435065/foreign-key-to-non-primary-key#18435114) but a foreign key can only refer to a primary key. How can I do it if `Playlists.lang` is not a primary key? – Xavier Carrera Gimbert Jun 25 '17 at 10:37
@XavierCarreraGimbert So if possible then create `unique key` or `Primery key` on `Playlists.lang ` – Sagar Gangwal Jun 25 '17 at 10:41
@SagarGangwal That would make it impossible to have any two playlists with the same language. That's clearly not intended. – Jun 25 '17 at 17:54

score 0 · Answer 2 · answered Jun 25 '17 at 12:13

You need to be clear about what attribute you want to assign to which entity (playlist, video or possibly course). You can assign language ids to both, playlist and video list items independently. Who is to say that you are not allowed to include a video with a language id of 2 in a playlist that carries a language id of 1? (This could, for example be a video in a foreign language that you want to appear in a playlist of your own language).

To search for suitable items you should then definitely use some kind of join (on video.playlist_id=playlist.id). The resulting table will contain both, video.language_id and playlist.language_id, which is not a case of having redundant information, as I have tried to explain above since they refer to different entities.

Excellent explanation!! Thanks – Xavier Carrera Gimbert Jun 26 '17 at 16:49 — Xavier Carrera Gimbert, Jun 26 '17 at 16:49

How can I implement a language system in my mysql database?

2 Answers2