On 3/9/2011 1:43 PM, Darren New wrote:
>
> 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.
>

The problem is that we have to get jobs out for the client now and might 
not physically have the time to do it right.

>> 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.
>

yes - great application of 'not' on a sentence :-)


>> 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.
>

Yes, but makes the application have to perform less and changes where 
the work is done.

> 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.
>

That works if the GPS location used in each instance is exactly the same 
- get off by a bit and you have a duplicate pole 1' away from the 
existing pole.  But a good interface could check for that and prevent it 
from happening.

>> 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.
>

Yes, you just can add another 'property'.  But not if you miss a 
fundamental requirement that alters the data you already have organized.

>> 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, but wrong direction.

I was referring to the situation where a piece of data was not accounted 
for in the database.  You must define ground rules for the database so 
you can define the piece of data and know where it actually should get 
stored.  We have been lacking in such definition.


>
> 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."
>

yes, several alternatives can achieve the same outcome.  Our group 
sometimes has trouble seeing that.  And the solution provided can cause 
many many problems.

> 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.
>

No, not a single database.  We take many pictures and produce some 
pretty large 3D pointclouds for some of the work.  Each job has a .mdb 
database file, but that is pretty small.


Here's a question:

We have pictures (sometimes more than one) of each pole.  We do have 
some applications that require that the image be in a file format and 
not be fed from a stream.
What is the practicality of storing the images in the DB as a field 
(blob or something else)?

Post a reply to this message