I wanted to store List values as a array in MYSQL column. Whats the best practice?

Question

Hi there,

I have a registration form with common registration fields and two multiple selection Lists with heading "services". I wanted to store form data to database into a table. Currently i am having only one table "registrations", also i am having second thoughts whether i should create another relation table with name "registration_services" and store the regid and serviceid

I am expecting at least 10000 records in this table Is it better to store the multiple selection list values in one column of the same table?

Or Shall i create another table and store as parent child relationship?

Whats the best practice?

Please advise Thanks SIA

Answer 1

Don't store a list of values in a single column.

What you're describing is a one-to-many relationship between registration and another entity. Use your relational database as a relational database.

The big problem with putting multiple values in a single column is it becomes near-impossible (and certainly not performant) to query for records that have a specific one of those values.

Answer 2

If these services are used elsewhere, it is best to create a registration_services table and use JOINs to obtain the data. It should be efficient enough. 10000 isn't a big number at all.

If it is purely informative, and you don't have other references to these services, you can store them in one column as CSV, but that is a rare case and I doubt you don't need them anywhere else.

Answer 3

It's much better to have the data in a separate table. I doubt it'll ever cause you a slow down unless your dataset becomes truly, truly massive, at which point you could probably use some form of caching anyway. It'll make maintaining the data much easier in the long run and you'll be able to benefit from indexes as well as well as using FK constraints to help maintain the integrity of your data.