sql - How to store array or multiple values in one column

Question

Running Postgres 7.4 (Yeah we are in the midst of upgrading)

I need to store from 1 to 100 selected items into one field in a database. 98% of the time it's just going to be 1 item entered, and 2% of the time (if that) there will be multiple items.

The items are nothing more than a text description, (as of now) nothing more than 30 characters long. They are static values the user selects.

Wanted to know the optimal column data type used to store the desired data. I was thinking BLOB but didn't know if this is a overkill. Maybe JSON?

Also I did think of ENUM but as of now I can't really do this since we are running Postgres 7.4

I also wanted to be able to easily identify the item(s) entered so no mappings or referencing tables.

question from:https://stackoverflow.com/questions/6360739/how-to-store-array-or-multiple-values-in-one-column

Answer 1

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.