Store vector in Sqlite

Question

I am creating an application in C# that must have a database. At first I’m thinking of using Sqlite for simplicity. But for each data entered in my bank must have :

• Identifying (int, char, bool)...
• Vector (which can be of size as 2.10000)

The problem is how to store this vector, and Sqlite does not accept vectors.

I have already researched some solutions, but I have doubts about these options and regarding their problems.

1 - Store as a string:

Is there a problem storing very large strings? Anyway my values are float or double, and converting to string, I will easily be losing information, so I do not know if this is a good solution.

2 - Create new tables for each new entry:

So I would have a main table and for each new input I would need to create a new table. The solution may be interesting, but a database with many tables (10000 tables) could not be slow?

I would like one of these two options above if any of them can work well, or else indicate me an alternative to a database that I can place vectors as table elements.

Answer 1

The solution may be to use a type CHARACTER same. Or BLOB to give more freedom to create a format that allows any data without specific serialization, and possibly saving space. It is not possible to say which is best for each case. It is not a problem due to the size, both accept very large data.

Obviously the syntax of darlings SQL is not prepared to manipulate the internal data of what would be this vector inserted within another type. If you need to handle the elements individually in SQL queries you will need to create custom functions (possibly in C) for use, adopting the format you adopt.

Remembering that Sqlite has dynamic typing and the columns have affinity and not fixed type.

It is part of the philosophy of the database to have only the basic types and use them to solve all more complex needs, such as this vector.

The choice between this solution and tables normalized depends on each case. In most cases I would only adopt normalization to resolve the issue if the queries ask this, if the domain indicates that it should be separated and that the performance is not affected.

I really don’t see why it would take 10,000 tables, nor does it make sense to do that, it would be hard to manage. I see the need for two, one main and the other that would contain vector data.

This second table would have a column indicating which row of the main table it is linked to and a column with the number of the element of the vector, these form together the primary key. Eventually I could have a single column that contained a key mounted with this data, but I doubt it’s a good solution. It also has, of course, the column where it actually holds the value (it could even be several columns, if necessary, if the vector holds a complex object with several members in each element).

In many cases this solution is simpler and more standardized, but I do not guarantee that it is the best for everything.