Store vector in SQLite

1

I'm creating an application in C# that must have a database. At first I'm thinking of using SQlite for simplicity. But for each data inserted in my bank must have:

  • Identifier ( int , char , bool ) ...
  • Vector (which can be as large as 2,10000)

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

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

1 - Store as a string:

Is there any problem storing very large strings? Anyway my values are float or double , and converting to string , I'll 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 might be interesting, but could a database with many tables (10000 tables) be slow?

I would like one of these two options above if any of them might work fine, or else I would suggest another alternative to a database so I can put vectors as table elements.

    
asked by anonymous 17.03.2016 / 21:02

1 answer

2

The solution can be to use a% type of% same. Or CHARACTER to give more freedom to create a format that allows any data without specific serialization, and possibly saving space. You can not say which is best for each case. It's not a problem because of the size, they both accept very large data.

Obviously, the queries SQL syntax is not prepared to manipulate the internal data of what would be the inserted vector within another type. If you need to manipulate the elements individually in SQL queries you will need to create custom (possibly C) functions 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 database philosophy to have only the basic types and use them to solve all the more complex needs, such as this vector.

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

I do not really see why I would need 10,000 tables. It does not make sense to do this, it would be difficult to manage. I see the need for 2. One main and one that would hold the vector data.

This second table would have a column indicating which row of the parent table it is linked to and a column with the number of the vector element, these together form the primary key. Eventually it could have a single column that contained a key mounted with that data, but I doubt that'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 multiple members in each element).

In many cases this solution is simpler and more standardized. But I do not guarantee it's the best for everything.

    
17.03.2016 / 21:35