[Jim notes:] For those jumping into the middle of this on-going thread, our comments are in reference to a webcast found here: http://www.sohodojo.biz/sqlyog.
Jim: Thanks, I think I've got your procedure down. Makes sense. One thing you say in the video is that while you are developing, you are regularly updating changes to your live server. I think you mean to your -dev db, don't you? That way you don't put your ongoing work into the public server until end of day when you've got everything working.
[Jim replies:] That sounds about right... I'll have to give a closer listen and look to the video as I was under some time pressure to get it into the contest and, honestly, it was a "wing-it" (and my first Camtasia) production.
More generally, your summary statement is spot on -- that the basic idea is to always grab a start-of-day copy of the database from the live server -- and this copy serves, first, as your back-up of the remote/live server and second, it is the 'seed' for your my-work-today local copy of the database.
A couple things this helps you do so as not to be your own worst enemy are:
1. You have that today-fresh back-up of your live DB.
2. No matter how close together you _think_ your dev and live sites are DB-wise, don't assume. Because sure enough, there will be days where that assumption is not as good as you think and you end up in an "Oops!" moment.
3. By working against the latest version of your live data, you _significantly_ reduce the delta between your development version and your live version. This is a _big_ deal when you do end-of-day re-synchronization. You will be looking at a limited set of table/record differences that are limited to only several hours of activity. Any DB copy older than that and you can lose yourself in the mucky-muck of trying to figure out record-wise diffs as either 'important', 'not sure', 'toss-able'.
Just a suggestion: The diagrams with arrows and sequence numbers are very helpful. It would be very helpful, in your explanation if you'd give a quick overview. Like, for example: "What I do is make changes working with a WAMP local version where the db is the same name as the public version. Then, I keep 2 backups so that I have a recent public version that isn't touched, and a version that is saved from the previous day's work."
[Jim replies:] Good idea. In fact, since this was a PowerPoint slide show that I ran concurrently with the 'live action' stuff for the webcast capture, I can easily do static slide images and use them for a text-based annotation of the webcast.
I've done a bunch of tutorial work for students, and it helps to let them know the overall structure and philosophy before giving the details. That way any ambiguity might make sense to them.
[Jim replies:] Agreed. There is nothing like foisting some material on the uninitiated to reveal all the assumptions and holes in a presentation... Like, for a trivial-but-could-cause-an-international-incident example, I noticed this morning that I misspelled "Guinness" (as in Stout) in one of the thought bubble 'cheeky annotations' that I threw in -- I was not literally but figuratively drunk with my rapid learning curve of learning and using Camtasia features! :-)
Thanks for the video.
[Jim replies:] You are welcome! Glad it was useful to you.