Menu
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.
791
For storing multiple values in single column, you can have json or jsonb column in your table, so that you can store multiple values as json array in column.
To store multiple values, there are two ways of carrying out the task. One way is to assign each value to a single variable, and the other, much more efficient way, is to assign multiple values to a single variable. That is what we call an array. An array is a way to store multiple values in a single variable.
The INSERT statement also allows you to insert multiple rows into a table using a single statement as the following: INSERT INTO table_name(column1,column2…) VALUES (value1,value2,…), (value1,value2,…), … In this form, you need to provide multiple lists of values, each list is separated by a comma.
An array in Java is used to store multiple values in a single variable, instead of declaring separate variables for each value. Therefore, an array is a collection of fixed elements in what can be seen as a list. Each element in an array consists of the same data type and can be retrieved and used using its index.
You have a couple of questions here, so I'll address them separately:
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.
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.
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.
111
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
