CODEWITHSUNDEEP
mysqllaravel
user15389576
user15389576

Reputation:

Is store array in database in efficient way?

I have a column called langs in the users table that contains the languages that the user chose to make the website show only the text translate based on the langs that the user had chosen. like

john doe - ['en','le']

Does store an array in the database is the right way? if not what is the datatype should I use?

Upvotes: 0

Views: 46

Answers (1)

David
David

Reputation: 218847

"The right way" is often an opinionated thing, as an argument could be made that this approach may be "simpler" for small applications with few use cases. But if you're asking how to further normalize the database then you're looking for three tables:

  • Languages
  • Users
  • UserLanguages

The third is what supports the many-to-many relationship between Users and Languages. For example, consider the Languages table:

id | code
---------
 1 | en
 2 | le
 3 | es

And the Users table:

id | username
-------------
 1 | bob
 2 | sally

Then if bob wants to select the languages en and le, then the UserLanguages table would have:

id | userId | languageId
--------------------------
 1 |  1      |  1
 2 |  1      |  2

An argument can be made that UserLanguages doesn't need the id column, since the combination of userId and languageId creates a natural key. That's up to your own preference, or possibly to whatever ORM or other tool(s) you may be using.

But the point is that the relationship between a User and a Language is itself an entity in the relational data. Changing language selections would simply involve updating that table.

Upvotes: 2

Related Questions