How to save list in db column

Question

I would like to store the FOO object in the database. Let's say that FOO contains three integers and a list of "Fruits".

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

Is it possible to save the list of fruits in a column?

Answer 1

In a normalized relational database, this situation is unacceptable. You should have a connection table that stores one row for each individual FOO object identifier and Fruit identifier. The existence of such a string means that the fetus is on this list for 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 must:

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

Answer 2

If you are absolutely sure of what you are doing (for example, you will not need to look for the values ​​of the list, for example), you can also serialize your object or just the list object and save it in a binary column.

Simple separation of characters into values ​​can be very good and cheaper from the point of view of saving and loading, but be careful that your data does not contain a separator character or delete it (and, accordingly, process escape sequences during loading, etc. Your language of choice can do better than you, though.))

However, for the “right” decision, do what Mehrdad described above.

Answer 3

Its technically possible, but there will be a very bad design, imo.

You can do this by building a string and saving it in the nvarchar (max) field (when using the SQL server or its equivalent).

Answer 4

Some databases allow you to store multiple values ​​in one column of one row, but it is usually more convenient to use a separate table.

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

Answer 5

You can, but most likely it will be considered as text, which complicates and slows down the search in this column. It is better to use a linked table.

Answer 6

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