Am I over complicating things by using Storage instead of Sqlite or NoSQL?

Ionic Framework ionic-v3
1

I am going to try and keep this as short as possible so its easy to understand.

I am making an app that allows driving instructors to enter details for a driving lesson for a student

Typical Scenario:

  1. Student gets in the car, Instructor asks for the students license number, first name, and last name. Based on this an ID is generated in SessionInterface and along with StudentInfo
  2. At the end of the driving lesson the Instructor adds info to the SessioInfo array.
  3. At some point in the day the instructor adds Notes and Files.

Now this is where I’m confused.

If I was using a database and tables I could easily do this. Create a Session table that holds the id, sessionID, and studentInfoID. Create tables for StudentInfo that holds all info for the student, and another table for Session that holds all the sessionInfo and an id linking to the files, grades, and notes table. Or I could use the NoSQL document approach

I would have to create something like this and store it

      session = {
        id: 123,
        SessionInfo: {
          created_at: "",
          //.......
          files: {
            name: "",
            // .....
          },
          notes: {
            title: "",
            // ......
          },
          grades: {
            code: "",
            .....
          }
        }
        //......
      };

BUT I wouldn’t be able to create this at once. I would first create the SessionInterface add the id, later on i would add SessionInfo and at the end SessionInfo.

export interface SessionInterface {
  id:                    any;
  session?:              Array<SessionInfo>;
  studentInfo?:          Array<StudentInfo>;
}

export interface SessionInfo {
  created_at?:           DateTime;
  edited_at?:            DateTime;
  sessEndTime?:          Time;

  files?:                Array<Files>;
  grades?:               Array<Grades>;
  notes?:                Array<Notes>;
}


export interface Files {
  name:           string;
  uri:            string;
}

export interface Notes {
  title:           string;
  note:            string;
}

export interface Grades {
  code:         string;
  grade:        number;
}

export interface StudentInfo {
  firstName?:            string;
  lastName?:             string;
  license?:              string;
}
2

I’ve released a couple of apps using raw SQL before realizing the power and simplicity of Storage, and have been kicking myself since then. So for my money I’d say stick with Storage.

The only time it seems like it’d be better to use SQL is when you’re going to have a “lot” of records. It’s unclear what a “lot” means in this regard, though. For most apps Storage will be more than sufficient.

So I’m not entirely sure the full extent of your question here, but you’d pretty much treat Storage as a NoSQL DB.

1 Like
3

The way you are doing it now (with Storage) is the “nosql” way, you’re just storing json directly.

The reason to use sqlite instead of storage is primarily if you have relational data and lots of it.

You clearly have relational data, and you have a number of nested relationships. To me, this is worth the overhead of sqlite, provided you’re already familiar with sql, as it’s built for exactly this type of data. Using a nosql approach, whether you store it as one large object or lots of smaller ones will be cumbersome.

1 Like
4

Thanks for the answer. Let me simplify the question a bit.

Lets say my structure is still the same as above (like in the interface).

Now at first I create a new record for SessionInfo

session = {
  id: 1,
  // Array<SessionInfo> info has not been filled out yet
  studentInfo: {
    firstName: 'John',
    lastName: 'Smith',
    license: '123456'
  }
}

Now I save this. As noted Array has not been filled out. Later on how would I add sessionInfo to the same SessionInterface object? If I was using sqlite I would just have another table that holds everything just for sessionInfo and it would have a sessionID (from SessionInterface) so I could easily insert the record.

5

I would add an initial value, just an empty array.
i.e.

session = {
  id: 1,
  sessionInfo: [],
  studentInfo: {
    firstName: 'John',
    lastName: 'Smith',
    license: '123456'
  }
}

Then save that sucker.

storage.set(`session-${session-id}`, session);

Then the general idea (General. Idea, *salutes), is later on you grab it from storage

storage.get('session-${session-id}')
.then(session => {
  session.sessionInfo.push(mySlickSessionData);
  return this.save(session);
});

Just as an example. You could always save all of the sessions in a single array and then save that to storage. I’m not sure which one would make the most sense for your use case.

6

If I were to use NoSQL (like pouchDB) wouldn’t I just need to create a primary ID for each record (in this case) SessionInfo… and than couldn’t I easily update everything for that record using the generated _id… For example I create the Session with an _id, later on when I wanted to insert data for StudentInfo so I couldn’t I just retrieve Session and insert StudentInfo into that same record? And the same way for SessionInfo.

I’ve never used NoSQL so I’m not sure if this approach would work

7

Well, sure, it’s just that afaik, that doesn’t exist on an actual phone. The storage plugin just stores json into a sqlite database, so you can’t really do that ID concept with it alone.

There’s also the option of a hybrid approach. Make sql tables with only two columns, ID and JSON. You could still do sql joins too if you wanted to for your relational data, or you could just query each table for the matching id in your json.

This might be the idea solution for you because you get the power of sql joining and unique ids, but with just only an additional column which is just json.

8

Okay that makes sense.

Initially I was thinking of just saving all Sessions in a single array into storage. But than if I wanted to edit or add to a single record how would I find that single record. Would something like this work?


let sessions: SessionInterface[] = [
    {
    	id: 1, 
    	sessionInfo: [], 
		studentInfo: {
		  firstName: 'John',
		  lastName: 'Smith',
		  license: '123456'
		},
    },
    {
    	id: 2, 
    	sessionInfo: {
    		created_at?: '2018-03-15';
  			edited_at?:  ''
  			sessEndTime?: '02:03 PM'
  			files:  []
  			grades: [],
  			notes?: [],
    	}, 
		studentInfo: {
		  firstName: 'Jason',
		  lastName: 'Chan',
		  license: '567890'
		},
    },
];

this.sessions.find(x => x.id == 1); // 1 is just an example I would probably use a datetime stamp that uses milliseconds to avoid duplicates
9

From what I’ve read pouchDB (noSQL) can automatically generate an id for you, or you can create your own (i.e. using a milliseconds datetime stamp)

10

Yes, absolutely. So does sql. The difference is sqlite is a thing that exists on a phone, and pouchdb is not.

11

I believe it can be used on iOS and Android devices if you use the sqlite plugin. At least according to this

In PhoneGap/Cordova, you can have unlimited data on both iOS and Android by using the SQLite Plugin.

12

Hmm, very interesting. I don’t see anything within the sqllite plugin docs about how it can communicate. I definitely can see how pouch db itself would work in the browser, which an ionic app is…but as far as actually syncing to more permanent storage…not sure. To me kind of seems like another extra layer, but it could definitely work nicely. Sorry, that’s as much as I know, I’ve always just used either Storage or sqlite and json.

14

Do you notice any performance issues with using sqlite over storage?

15

Depends on if you have relational data or not. If you do, sql will be faster. The storage plugin uses sqlite in the background (when running on a phone), so for non-relational data it’s identical. But, when you have table relations sql will join that data for you as part of the table layout (i.e your SessionInfo table would have a one-to-many relationship with your Grades table).

The whole point of sql is that it’s insanely fast at querying relational data, even at millions of rows.

So, non-relational data: no difference
Relational data: sqlite will be faster

Here’s the thing though, unless you are returning tens of thousands of entries or more it’s almost certainly completely irrelevant. I’d be more concerned with data structure, data reuse, and code clarity than with performance at this point, unless you know you’ll be pulling thousands of records.

To me, sqlite in this case provides clearer code because my relationships are enforced by the sql engine and my ids are guaranteed to be unique by the sql engine. I can simply wrap my sql calls in a service and makes some matching models for it to return and I’m good to go.

Using the json way, you are either storing a giant json object where each property could have a list of child properties that could each have a list of child properties etc…and you can’t re-use any of that data. For instance three different sessions couldn’t link to the same File object, because they would be unique entries in the giant parent object.

If you stored each one separately as json (like one storage key for sessions, one for files, one for grades), you’d be building out your relationships manually, but then you’re getting into nested loops to make a bunch of async calls and findById stuff…to me, both those solutions are a bit ugly.

For me, simple non relational data goes in plain old Storage. Anything relational goes into sqlite, but I generally only have ID and json columns (and any foreign keys).

2 Likes
16

Thanks for the in depth answer.

However I don’t think I explained what I’m intending to do well enough

Two or more sessions will never be linked to the same files, grades or notes so I dont have to worry about reusing data. One student wont even ever be linked to multiple sessions as far as the app is concerned because when the data gets submitted to the backend a person in the office will be responsible for making sure the data is placed in the correct student (the point of the app is to make life easier for the instructor using the app). Once the data is in the backend 99.9999% of the time it will already be saved to the correct students file bc the license # will always be unique.

If I go with storing it as JSON I will store the entire SessionInterface object as one record. That will include the studentInfo, sessionInfo (files, and grades will be stored as an array under sessionInfo).
So even if a new session is started for the same student, it will always create a new SessionInterface object that will never be linked to the same students previous session(s).

17

I do my best to minimize situations where the same data is stored in two different locations, because it inevitably becomes a source of relatively difficult to isolate, reproduce, and fix bugs involving what happens when they disagree.

So with that in mind, another potentially feasible design strategy is to have no storage whatsoever in the app, relying completely on the backend to be responsible for that. This assumes relatively consistent network availability and assumes that you won’t need instructors to be able to quit and restart the app in the middle of a session. If you do need quit/restart, then I would use ordinary JSON Ionic-Storage to persist only the minimal information required to maintain the currently not-yet-submitted active session.

1 Like
18

Storing it online would be my preference but not all instructors have data on their devices. They sometimes wait to get back to the office before uploading everything. A specific requirement of the app is that the data remain local until it is manually uploaded.

Once the data is uploaded to the server, its deleted from the device. It’ll never be duplicated.

19

In that case, I would consider this uploading procedure to be the proverbial queen of the realm, and structure everything else in the app around making its life as easy as possible. I would make the fundamental storage unit whatever one atomic upload unit is. I’m going to assume that’s one session.

I would stick with Ionic-Storage here, and store each session as a single JSON object, with its key being the session ID. I have written similar apps to what you describe here, and have done just fine using v4 UUIDs as session IDs. This has the benefit that the app can assign them even in offline mode.

The uploader task can use the keys() method to get a list of all sessions in the tank, and repeatedly upload one and then remove it from storage. That way if the upload process fails partially, it is easy to retry it later. You also avoid having a central contention point because each session is completely separate from all others, so you won’t have to worry about the overhead of repeatedly deserializing a big array, adding to it, and then reserializing it.

I don’t see SQL giving you any tangible benefit here, and especially nowhere near enough to justify dealing with the hassle of interacting with it from JavaScript.

2 Likes
20

Thanks. I was planning to put it one large JSON object but you have a point about serializing and deserializing it every time.

Since you have done stuff similar before, any tips on how to handle the “RUD” part in CRUD? For example, when I want to update the nested Files or Grades inside sessionInfo (since that info can be updated at anytime and not necessarily at the time when the sessionInfo object is created)? Should I pass around the entire JSON object throughout the app.

For example

Page 1 has a list of all sessions, user clicks on one, it opens up a second page where they can choose which option to edit (the student, the session, etc), and when the click on session it takes them to another screen to edit the session or the grades (or files, or notes) and just use like this. session.sessionInfo.grades = updatedGrades or have different methods for updateGrades(id) updateFiles(id) where id would look for the main ID at the top level of the session

21

@rapropos Is replying, and he might beat me to this but…you need a service. A single place where you modify this stuff at. Do not pass stuff around.

Edit: Also, given the additional context I totally agree with his point that sql is not at all needed here

1 Like