Android Question Store an array of ints in a DB

[udg]

Hi all,
I know there are very good reasons to record each item on a separate row of a table, but my question arises from a specific need and I'd like to hear what you think about it.

We should record for each of about 500 employees, each day, their selection for a complete lunch (four to ten items, if they order for a guest too). There's no need to search for an item or manage it (update, delete..) once stored.

Traditionally I would think about a table like below:
id, lunch_date, fk_employee_id, fk_item
where fk_xxx are foreign keys to tables Employee and Items (where a few info about an item are stored).

But what about "cheating" and storing all the items for a specific employee/date in a BLOB field?
I would convert the array object to an array of bytes with B4xSerializator's ConvertObjetToBytes, then store this group of bytes to a BLOB data field.
Later on, I would read it back and convert it again to an array of ints. What do you think?

About DBes, I would have Sqlite locally on mobile and MySql/MariaDB on remote server.
I read about the addition of a JSON datatype to MySql but I guess it could cause problems to store it plainly to a TEXT field in Sqlite. Anyway I never used it before so, eventually, some tests should be coducted on this kind of solution if it's to be considered.

Any other good and efficient strategy? TIA

udg

 

[Jeffrey Cameron]

I guess it depends on why you are storing the data. If you only ever need it in your B4X app and never need to report on it or analyze the contents (e.g. show me how many item "x"'s were consumed between dates "A" and "B") then your "cheating" method would probably be fine.

If you ever need or want to be able to search or report on the contents of the information you are storing I would not recommend stuffing it all into one column.

 

[Erel]

It is better to post technical questions in one of the questions forum. The chit-chat forum is not indexed by the search engine.

 

[udg]

@Erel: please move it to the B4A section or lock this thread so I can start it again in that section, avoiding possible duplicate posts by members finding it in both locations. Thank you.

 

[udg]

@Jeffrey Cameron , thank you for your interest.
My plan is to use the "cheating" method locally with Sqlite since in that context I don't need any search/statistic or other operation on the array data other than storing ad reading back. Once data are trasmitted to the server (where I use MySql/MariaDB) I'll revert back to the traditional method so to be able to operate more widely on that same data.
It's even possible that on the server I will use temporary tables with aggregated (cheating-style) data and other tables where I could store simple or aggregated data for further analysis. Sincerely I did'nt yet discussed the point with the customer.

 

[Jeffrey Cameron]

FWIW I always store the smallest integral values of data gathered. I never store any aggregated data because inevitably the customer will change their mind at some point and ask for something I cannot reproduce because I've not saved the data. In your example, let's say you aggregate the number of items sold into weekly totals and just store those. If the customer ever goes "well, now I need to know how many we sell every Wednesday" then you will be unable to answer that query.

SQL is designed to quickly and efficiently give you aggregate totals from data (COUNT, SUM, AVG, etc.) so why not take advantage of it? If it's cumbersome to work with the data, you can always write helper classes that make managing the data easier in your code.

Just my 2 cents

 

[KMatle]

I fully agree with @Jeffrey Cameron. Another one: If your "internal" structure is broken (bug) you get into big troubles. If you need to update all rows (e.g. due to legal changes) you have to migrate all the data. "Raw data" can be "edited/repaired" very easy. Like I never use NAS drives with Raid. Nice idea but... Better: Backup the data to multiple locations/drives.

 

[udg]

Thank you all.
Talking about aggregated data probabily I wasn't so clear about what I had in mind.
I envisioned a temporary table where for each employee is stored his/her reservation for the period (day, week..) in the form of cheating data because is anyway a block of costant information. Once the order is placed (and payed) no further operation is done on it.

Another, log-term and classical table would store aggregated data in the form:
date - food_id - total_ordered or something like this, where to place queries for statistical purposes (There's no need to place a query like "what did john ordered on july 1st?" but something like "how many sandwiches on saturdays?" or "how many pastas last week".)
This will take the statistics table form potentially 500*10*5 records a week to 20*5 records a week (20 is the expected offer on a single day menu)
A big difference indeed.

 

[Robert Valentino]

I know I am late to the party on this one.

But I had/have a similar need ("cheating data") and decided to store the Int's as a string and wrote two routines

I call ListToString (could be Array to string) when I need to update the record with these fields
and call StringToList when I read the record and need the data back as a List (could be array)

This way I can store 20 or 30 fields in one string value

I use the New Line character as my field separator

I also needed to store arrays of data in a string I that case I would use Chr(10) as data separator and use Chr(13) as array separator

Public  Sub StringToList(StringPassed As String) As List

           Dim StringList             As List
   
           StringList.Initialize

           If StringPassed.Length = 0 Then Return StringList


           Dim Delimiter              As String = "\" &Chr(10)

           Dim strArray()             As String = Regex.Split(Delimiter, StringPassed)

           If strArray.Length <> 0 Then StringList.AddAll(strArray)
   
           Return StringList
End Sub

Public  Sub ListToString(ListInfo As List, ListIsNumbers As Boolean) As String

           Dim ListString As String = ""
           Dim Delimiter  As String = Chr(10)

           Dim ListLoop   As Int

           If ListInfo.IsInitialized = False Then Return ListString

           For ListLoop = 0 To ListInfo.Size-1
               If  ListIsNumbers Then
                       ListString = ListString &NumberFormat2(StrToInt(ListInfo.Get(ListLoop)), 1, 0, 0, False) &Delimiter
               Else
                       ListString = ListString &ListInfo.Get(ListLoop) &Delimiter       
               End If
           Next

           Return ListString
End Sub

Public  Sub StrToInt(InString As String) As Int
   
           If  InString.Length    = 0      Then Return 0
           If  IsNumber(InString)    = False Then Return 0
   
           Return InString
End Sub

Not sure if this helps

BobVal

 

[udg]

Thank you. You're not late at the party since everything is still on the design board. I appreciate that you shared your experience and showed that sometimes cheating could be not so bad.