CODEWITHSUNDEEP
sqldatabase
Nifle
Nifle

Reputation: 11923

How to store a list in a db column

I would like to store an object FOO in a database. Lets say FOO contains three integers and a list of "Fruits".

The list can have any length, the only thing I know is that the all the fruits allowed are stored in another table.

Can I store the fruit list in a column?

Upvotes: 72

Views: 44985

Answers (7)

Hi_World
Hi_World

Reputation: 7

For your case, I would recommend creating a new table. Recently, I have also been handling similar issues, but they didn't require creating a new table. Instead, I used ";" as a delimiter and wrote a Java class to solve it.

public class DatabaseListUtil {
    public static final String SPLIT_CHAR = ";";
    public static final String ESCAPED_SPLIT_CHAR = "\\;";

    /**
     * Convert a list of strings to a database column string with handling for ';'
     */
    public static String convertToDatabaseColumn(List<String> stringList) {
        if (stringList == null || stringList.isEmpty()) {
            return null;
        }

        // Escape each string to handle the delimiter within the string
        List<String> escapedList = new ArrayList<>();
        for (String str : stringList) {
            escapedList.add(str.replace(SPLIT_CHAR, ESCAPED_SPLIT_CHAR));
        }

        return String.join(SPLIT_CHAR, escapedList);
    }

    /**
     * Convert a string to a list of strings with handling for escaped ';'
     */
    public static List<String> convertToList(String string) {
        if (string == null) {
            return new ArrayList<>();
        }

        // Split the string and unescape the ';' characters in the parts
        List<String> unescapedList = new ArrayList<>();
        for (String part : string.split(SPLIT_CHAR)) {
            unescapedList.add(part.replace(ESCAPED_SPLIT_CHAR, SPLIT_CHAR));
        }

        return unescapedList;
    }
}

Upvotes: 0

MarcosJVC
MarcosJVC

Reputation: 

INSERT FOOFruits (FooID, FruitID)
SELECT 5, ID 
FROM   Fruits 
WHERE  name IN ('Apple', 'Orange');

Upvotes: -1

AKX
AKX

Reputation: 168913

If you're quite sure of what you're doing (ie. you won't need to look up the list's values, for example), you could also serialize your object, or just the list object, and store it in a binary column.

Just character-separating the values may be fine too, and cheaper in terms of saving and loading, but be careful your data doesn't contain the separator character, or escape it (and handle the escapes accordingly while loading, etc... Your language of choice may do a better job at this than you, though. ;) )

However, for a "proper" solution, do what Mehrdad described above.

Upvotes: 7

Mehrdad Afshari
Mehrdad Afshari

Reputation: 421978

In a normalized relational database, such a situation is unacceptable. You should have a junction table that stores one row for each distinct ID of the FOO object and the ID of the Fruit. Existence of such a row means the fruit is in that list for the FOO.

CREATE TABLE FOO ( 
  id int primary key not null,
  int1 int, 
  int2 int, 
  int3 int
)

CREATE TABLE Fruits (
  id int primary key not null,
  name varchar(30)
)

CREATE TABLE FOOFruits (
  FruitID int references Fruits (ID),
  FooID int references FOO(id),
  constraint pk_FooFruits primary key (FruitID, FooID)
)

To add Apple fruit to the list of a specific FOO object with ID=5, you would:

INSERT FOOFruits(FooID, FruitID)
SELECT 5, ID FROM Fruits WHERE name = 'Apple'

Upvotes: 115

E.J. Brennan
E.J. Brennan

Reputation: 46849

Its technically possible but would be very poor design, imo.

You could do it by building the string and storing it in a nvarchar(max) field (if using sql server or its equivalent).

Upvotes: 5

Diodeus - James MacFarlane
Diodeus - James MacFarlane

Reputation: 114367

You can, but it will likely treated as text, making searching in this column difficult and slow. You're better of using a related table.

Upvotes: 1

Andru Luvisi
Andru Luvisi

Reputation: 25318

Some databases allow multiple values to be stored in a single column of a single row, but it is generally more convenient to use a separate table.

Create a table with two columns, one that contains pointers to the primary key of the objects table, and one that contains pointers to the primary key of the fruit table. Then, if an object has three fruit, there are three rows in the object_fruit table that all all point to the same object, but to three different fruit.

Upvotes: 0

Related Questions