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>

>>8
For the first point, when I was thinking of changing, I didn't mean over time, just during that session, before the save event.

As for ORM, I decided to write my own because I wanted something incredibly lightweight and also MySQL specific, because all the ORM solutions I'd seen were 1)  actually ActiveRecord, which I didn't really care to use and/or 2) way too bloated with RDBMS abstraction. PHP is not the place for that kind of thing. A pretty good one I saw was phpDataMapper, but once I realized that PDO could get my data to me in essentially any format I needed it, and that I could take advantage of my own SQL knowledge, I figured I'd go the non-ORM route.

So my current plan is to have a standard structure for the data to be housed, for example:


data
  artists[]
    name
    ...
    albums[]
      name
      price[]
        qty
        base
        formula
      ...
      songs[]
        name
        ...

and then I'm going to keep two objects during my session, a dataRetrieved and dataNew. The dataNew will be made based on the users actions and sent to a PHP script that builds out a query based on what's there, using the standard structure as a guideline for how to build the query.

I figure this will be the fastest way to do it. Considering that this is JSON work, speed is pretty important. I wonder if using stored procedures in MySQL would be the way to go.

Anyway just writing this all out in question form really helped, thanks /pros/