Tom Austin wrote:
> Exactly - but just how much time we can allocate to it is yet to be 
> seen.  But I think I can push for more time now because it will cost 
> much more time later.  But this only works to a point.

Naturally. I'm just recommending that you make it clear that any benefit you 
spend time on now will reap rewards over and over into the future.

> Makes sense - on anything that affects more than 1 row in 1 table is OK. 
>  Transact wrapper everything else :-)

Well, other way around, yes. Anything that affects more than one row goes 
into a transaction.

> Our bigger problem is getting the data that is entered to be correct. 
> Not just structurally correct, but actually correct.  This has to do 
> with the interfaces that were assembled with too much haste.

That's always a bugger, yes. :-)

> True.  I believe that if the system is built right the user will NEVER 
> have to look at the raw data.  The interfaces should give the user all 
> of the information that they need.

Yep. And if you really get into it, you wind up making views for each 
function of your application, so the applications themselves don't need to 
know how the raw data is organized. But that's kind of overkill for a small 
database.

> The keys can be almost anything and it not change the functionality of 
> the database or how the user interacts with.  it does make it somewhat 
> more readable for the person trying to figure out a problems that 
> somehow got into the data.

Right. For one thing, for example, it keeps you from having (say) the same 
pole entered multiple times in the database by mistake. If a pole is 
identified by the GPS coords (assuming these are precise enough) it keeps 
the database from having three records for a pole all at the same location.

> Yes, agreed.  Depending on how quickly the thing was built, it can be 
> very difficult if a complete aspect was missed. 

No, it's not really that hard to add a complete new aspect to the database 
after the fact, if what exists there is well thought out. You just have to 
avoid putting multiple things in the same table that aren't really the same 
thing in reality; like, avoid putting owner contact info into a table of 
poles, or even ownership information into a table of poles.

> Also, one has to define 
> *what* and *how* base rules before adding the data or you get a mess - 
> which we are approaching.

Yep. That's the "C" in ACID. Way too many people who never actually studied 
the topic think that the "C" means an error updating a row will corrupt the 
database files. No, it means an error updating a row will corrupt the 
*data*, not the container.

>>> yes, one just has to pay attention and not set up things that break.
>>> It could be that applications do not rely on the views (or other
>>> non-common features) But then one could be crippling themselves.
>>
>> Yes. It's better to just say "if we need multiple archives of this
>> database, we'll use the same engine."
>>
> 
> I just have to get this through to the rest of the guys.

Or, alternately, accept that you'll use the same engine, and then make plans 
for how to replace its features. Like, "we'll use views, but if we wind up 
exporting to CVS, we'll export both the raw data and the views, which is OK 
since we won't be updating historical data that we've already exported."

> Not a bad idea - but that can get a bit more complicated.  But I will 
> take it as a strong suggestion.

MySql makes it pretty easy, actually. Just follow the instructions.

Basically, you set up two machines with identical configurations of the SQL 
server. Then you tell one "Hey, you're a master", using the tool that takes 
a snapshot of the database and turns on the master flag at the same time. 
Then you restore that snapshot to the slave, then tell the slave "Hey, your 
master is over there!"

As long as you don't update the slave in a way that makes a transaction 
applied to the master fail on the slave, you're good to go. If you 
accidentally insert a record into the slave in a table that has an 
auto-increment integer, the slave will crash out when the master tries to 
insert the record with the same key, and you'll have to re-do that 
backup/restore process.

> We do have off-site backups :-)   Just 500GB in one big tarball tho :-(

Yah. Your actual data is 500G?  That's actually a pretty large database.

-- 
Darren New, San Diego CA, USA (PST)
  "How did he die?"   "He got shot in the hand."
     "That was fatal?"
          "He was holding a live grenade at the time."

Post a reply to this message