SQLite Table Assistance

[jcfuller]

What is the best method to store matrix data in an SQLite Db?
The data is spread sheet type data consisting of cols and rows of primarily
numerical data.
I could have literally hundreds of the same type so I don't really want
to use a single table for each type and then have to id each row.

My first thought was Tables for each type group:
TYPE1
TYPE2

Storing the data as a csv BLOB.

Each Table will also have columns for Client, Project, and Case so I can
search on a combination of those three.
TABLE: TYPE1
rowid,CLIENT,PROJECT,CASE,CSVDATA

Am I in the ballpark??

James

[Michael Mattias]

I'd check your DBMS for "searchability" ("WHERE..." ) of BLOB or other 'long binary' data... I know Oracle can't search it or substring it, and generally if Oracle can't do it nothing can.

But if you don't want to search on the data values themselves...that is, you only search on client and/or case and/or project, then a 'long' type is as good as anything.

I am having trouble grasping the data concept. Is what you have "for any unique client + project + case there is exactly one NxM 'matrix' ? if so, what is wrong with one table row for each row, column of data? eg

client,project,case,X,Y 
client,project,case,X,Y 
client,project,case,X,Y 
...

MCM

[jcfuller]

No I do not need to search on the data itself.
Each client/project may have several cases of TYPE1 data where TYPE1
data is a col/row table of data.

Blobs were the only way I could figure out how to store the data, but being
an SQLite novice, I was inquiring if there might be another (better?) way.

I am having trouble grasping the data concept. Is what you have "for any unique client + project + case there is exactly one NxM 'matrix' ? if so, what is wrong with one table row for each row, column of data? eg

Yes but each unique data table could have hundreds(possibly thousands) of rows so instead of returning just one row on a query row with a blob you have hundreds.

James

[jcfuller]

Another method I had thought of and is basically what is done using flat files:
Client_ID_Project_ID_CASE_ID_TYPE1_TABLE where _TYPE1_TABLE is a specific
Filename Ext. I just felt the BLOB method was better.

James

[Michael Mattias]

Well, then a' long binary' sounds like a good plan.

I would not store as comma delimited, however, Just store it as "the array data" ...

   REDIM Sheet (rows, cols) AS SINGLE/DOUBLE/INTEGER WHATEVER 
   REDIM SHeet (0,0)          ' just so it exists 
   CALL FillSheet (Sheet())   ' will redim to correct size whilst filling
   BlobDataAsString = PEEK$ ( VARPTR(Sheet(0,0), ARRAYATTR(sheet(),4) * ARRAYATTR(Sheet(),5)

TABLE: 
client
case
project
nRow 
ncol   (not strictly needed, but will be handy .....
Blobdata

haing BOTH nRow and nCOl will be handy as in when you retrieve the data....

Sql = " Select nrow, ncol, CAST (blobdata AS VARCHAR) from mytable  " _
    &  " where client = X and project=Y and case = Z"
Execute  SQL

REDIM   Sheet (nRow-1, nCol-1) AS thedatatype AT STRPTR(blobdata)

(Assuming you can get back the blobdata as a character string using CAST or something else).

[Bern Ertl]

I could have literally hundreds of the same type so I don't really want to use a single table for each type and then have to id each row.

What do you mean by "have to id each row"?

TABLE: TYPE1
rowid,CLIENT,PROJECT,CASE,CSVDATA

Am I in the ballpark??

If you are just looking to store/retrieve the data and not do any processing with SQL, that should work.

You might consider changing it like so though:

TABLE: TYPE1
rowid,CLIENT,PROJECT,CASE,CSVDATA_ID (foreign key on CSVDATA(rowid))

TABLE: CSVDATA
rowid, CSVDATA

In case you are reading many rows from TYPE1, this will save you from reading all the BLOB data.