Data with dynamic web applications

Let's say you have a system that is designed to manage bands and their albums, and then the songs on each album.

The system's UI will show you what's already in there, and will allow you to edit existing names for things (band or album name, for example), add new things (maybe a new song).

The data is stored in a RDBMS, retrieved by a server-side language, and then sent to the browser via a JSON object.

That's all relatively easy to manage when you're just displaying data. But when you need to start keeping track of saves and updates, I start getting a little stuck about the right way.

This is how I have it now:


         RDBMS 
           ↓↑
   Scripting Language
      ↑|
    (json)   
      |↓               
   jsModel <---------(user input)
       :                |
   (dispatched events)  |
             :          |
        jsController----+
           ↑    ↓
   DOM events  jsUI

My confusion happens when I start getting into the updating and saving of data.

1. Do I need to be keeping track of what has changed in JS? Or do I just make changes to the whole JSON object, and shuttle that back and forth? If so, who (which layer) is responsible for tracking those changes?

2. When it comes back to the scripting language, how do I break the JSON object up into the SQL commands I need, particularly when UPDATEing and INSERTing? Do I just write one big query and bind every available, or should I be dynamically building the query based on the data the script receives¹? Or is there some other solution, involving the separation of files or writing different functions or something, such that no queries are built; rather, the program procedure gets routed differently depending on the data it gets? Also, this probably depends on the answer to question one.

3. Anything you'd like to add is appreciated.



<sub>_________________

1. I had previously been using an ORM solution, which I wrote myself, and I realized that I was either constantly changing it do more things or using the hooks I wrote in to code over the wall. It occurred to me that it wasn't really a solution so I dropped that in favor of just writing custom prepared statement queries myself when needed. But now that query building might be an option, a layer responsible for doing that might be a smart idea.

2. Just in case you're curious, the RBDMS is MySql, and the server-side scripting language is PHP.</sub>

>>1
#1: Easiest way is just send up the whole JSON object, and store the old objects somewhere else. You only really need to figure out what changed when you actually display the change history. It is usually easier to store each copy and calculate differences as needed than to come up with a system to store and index change information.

If you need something more complicated, there are plenty of things you need to consider, such as how in-depth does change information need to be, how often are you fetching it, is it typical to request differences across a wide range of revisions, etc. There are lots of different ways to do change tracking and there isn't just one correct way.

#2: Let an ORM do this.

Why on earth did you attempt to write your own ORM, only to scrap it later? Not using an ORM seems a little silly, because it would solve almost all of the issues you raised in your post. What scripting language are you using? You should be able to find a good ORM that will do everything you want.