0

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?

  • "The right way" depends on exactly how you're going to use the data and what alternative ways if any might be better for your specific set of circumstances. `$_SESSIONS` might be as good, for instance, but it depends on your use-case. – Martin Apr 28 '21 at 21:45
  • 1
    Does this answer your question? [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) – nbk Apr 28 '21 at 21:46
  • @Martin I do not want to the user change them from the browser! –  Apr 28 '21 at 21:47

1 Answers1

2

"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.

David
  • 208,112
  • 36
  • 198
  • 279