How to store array or multiple values in one column

You have a couple of questions here, so I’ll address them separately:

I need to store a number of selected items in one field in a database

My general rule is: don’t. This is something which all but requires a second table (or third) with a foreign key. Sure, it may seem easier now, but what if the use case comes along where you need to actually query for those items individually? It also means that you have more options for lazy instantiation and you have a more consistent experience across multiple frameworks/languages. Further, you are less likely to have connection timeout issues (30,000 characters is a lot).

You mentioned that you were thinking about using ENUM. Are these values fixed? Do you know them ahead of time? If so this would be my structure:

Base table (what you have now):

| id primary_key sequence
| -- other columns here.

Items table:

| id primary_key sequence
| descript VARCHAR(30) UNIQUE

Map table:

| base_id  bigint
| items_id bigint

Map table would have foreign keys so base_id maps to Base table, and items_id would map to the items table.

And if you’d like an easy way to retrieve this from a DB, then create a view which does the joins. You can even create insert and update rules so that you’re practically only dealing with one table.

What format should I use store the data?

If you have to do something like this, why not just use a character delineated string? It will take less processing power than a CSV, XML, or JSON, and it will be shorter.

What column type should I use store the data?

Personally, I would use TEXT. It does not sound like you’d gain much by making this a BLOB, and TEXT, in my experience, is easier to read if you’re using some form of IDE.

Leave a Comment