How to store a list in a db column

Question

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?

Answer 1

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'

Answer 2

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.

Answer 3

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.

Answer 4

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).

Answer 5

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.

Answer 6

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