Relations with another table using Array

Question

how I can use array in cell related with another table in database like ?

Users table:

Name | languages_id
Anas | 1,2,3

Languages table:

id | language
1  | English
2  | Arabic

it’s work or not ?! and do you know what can I use in yii to do this ?

It is better to create a new table `UserLanguages` with `UserId` and `LanguageId` and remove the `languageid` from the users table. — Mahmoud Gamal, Dec 11 '12 at 11:22
@Mahmoud Gamal ... And create an "id" field in the Users table. — MUY Belgium, Dec 11 '12 at 11:27

score 3 · Accepted Answer · edited May 23 '17 at 11:58

3

Don't do this.

Don't store mutiple items as comma separated column, it is really bad.

You should keep your tables normalized, by creating a new table UsersLanguages as a many to many table between the USERS and Languages table. Somrthing like this:

Users:

UserId,
UserName,
... other details.

Languages:

LanguageId,
LanguageName,
...

UserLanguages:

UserId foreign key references Users(UserId),
LanguageId foreign kery references Languages(LanguageId).

edited May 23 '17 at 11:58

Community

1
1

answered Dec 11 '12 at 11:46

Mahmoud Gamal

78,257
17
139
164

@Anas This approach is better regardless the front end application you are using, and it will make the work required from you more easier. – Mahmoud Gamal Dec 11 '12 at 12:13

score 1 · Answer 2 · answered Dec 11 '12 at 11:27

1

You can use FIND_IN_SET.

SELECT
    name,
    language
FROM
    users
INNER JOIN
    languages ON FIND_IN_SET(languages.id, languages_id) != 0
GROUP BY
    name

Although Mahmoud Gamal's comment would perhaps be the better way to go about it.

answered Dec 11 '12 at 11:27

Michael

11,912
6
49
64

Relations with another table using Array

2 Answers2