On 07/23/2013 02:52 PM, Max Vlasov wrote: > I've created a kind of triple storage base with Sqlite db as the container. > Basically it's several tables implementing Object-Propery-Value metaphor. > There's only one field for data so thinking about generality I assumed that > the type for the data field should be TEXT of nothing since most of other > types (let's forget about blob for a moment) can be stored (and easily > visualized) with this field. But there are also indexes involved and here > comes the problem. If I insert natural numbers in some sub-sequence I will > get non-naturally sorted ones (1, 10, 2, 20). But we know that Sqlite can > accept any data in any field, so I can change the type to INTEGER and enjoy > numbered order when there are numbers were added (1, 2, 10, 20). On the > other side, when we look at real numbers, the problem would still exist. So > paradoxically probably the best type for universal field container is REAL > (or NUMERIC) since it will accept data of any type, but has advantage of > best sorting if reals or integers are involved. > > Is this correct or I am missing something?
You could consider having more than one column in the "value" table -- one for each data type that you want to have behave the way you want to. (When using more traditional SQL implementations that require the data to be "right", this lets you have "strongly typed" data -- you put dates into a date[time] column, integers in an integer column, etc.) I do not know if SQLite has the storage behavior that e.g. PostgreSQL and MS SQL Server have, which is that null values take up absolutely no space. If SQLite behaves that way as well, you could put each value in the the proper column for its datatype and not pay any storage penalty for having the extra columns. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
