Tom Austin wrote:
> I think most of our slow downs is currently in the applications that 
> access it.  Basically inefficient routines and use of objects.  A 
> required approach when coding time as of the essence.

Yeah, but now you hve some luxury to do it right, I'd wager, since you 
already have a working system.

> Does it make sense to set up a transaction wrapper on even simple single 
> updates?

If you're modifying more than one row at a time, yes. In most database 
engines, each statement you submit becomes its own transaction. This isn't 
true on all types of MySql tables. For example, if you have a MyISAM table 
and you say "update blah set x = 50 where y = 3" and it gets interrupted 
half way, you might update only half the rows where y = 3.

A single append of a single row is atomic. You're not going to corrupt the 
database per se. Just the consistency of the data inside the database.

So, yes, there's really so little overhead in using a transaction around 
anything that affects more than one row that it makes no sense not to do so.

>> That said, it's surprisingly common that if you structure your queries
>> right, you can make every operation essentially an "append to this
>> table", followed by an eventual "update that table in a way that if it
>> fails, we can reconstruct that table entirely from scratch". Think
>> Journal -> General Ledger.
>>
> 
> OK, I've been dealing with databases for 15 years now - on mostly a low 
> level.  that just went right over my head.  Makes sense, but I have no 
> concept of how it is implemented.

I've usually done financial databases. So, basically, all the history of 
everything in and out of the database is recorded in one set of tables, 
while the current state is in a second set of tables.

Like your checkbook: Every check you write gets a row appended, and you 
never change that row except to set a flag that you never unset (the "this 
check cleared" flag).  But there's another table that lists each account and 
says how much money is in it. In theory, you can construct the latter from 
the former, if you keep all your old checkbook registers around.

> for some reason I am hesitant to present the key as something that the 
> end user interacts with.  Maybe I'm a control freak :-&

No, there's almost never a reason to present the key that way. That doesn't 
make it less useful as a key. :-)

>>> OK, you want a job surveying utility poles :-) You have good insight.
>> I've just done this dozens of times in the last couple decades.
> what - survey telephone poles?

No, I only did that once. :-) Design databases from scratch.

> that is very correct.  Our first stab was fast and mostly dirty.  We 
> spent a lot of time to try to get the data right, but we missed some 
> pieces of data.

That's not unusual. The trick is to have the database structured in a way 
you can add the missed data later without *changing* what's already there. 
The best way to do that is to primarily record into the database exactly 
what's outside the database, reflecting reality as closely as possible.


>> What about your current system is so broken that you can't afford the
>> time to fix it?
>>
> 
> It has layers upon layers of fixes.  The end users had to fight with it 
> to get it to put out data correctly.  it was an ad-hoc rich system to 
> the point where I refused to make any more changes because of the risk 
> of data corruption/loss and our client getting crap for a deliverable.

Been there done that. Along with "before I start, has this ever been backed 
up?" Answered by "No, it has never been reliable enough we could get a 
backup of the data."  Followed by "Thanks, but I like my current job enough."

>> The other possibility is to hire someone who actually does this sort of
>> stuff for a living for long enough to show you how to set up the basics
>> of the database.
>>
> 
> It's a balance.  Depending on our requirements it could take just as 
> much of our time working with the guy and doing it ourselves.

This is true. I don't think it would take any less time, but you'd probably 
have a more robust system when you were done.

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

> yes, I don't think it will take much of a machine.  We have an old 
> server box (PIII 600 I think) that would likely do just that.  Or I 
> might incorporate MySQL on our current file server (Linux) to simplify 
> things and use the old box for development.

Yeah. And you'll likely want a replicated server as well.

> Luckily none of them were damaging.  Just annoying and cost a couple of 
> hours of everyone's time.

Yeah, that's much better than the place catching on fire. :-)

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

On 08/03/2011 10:34 PM, Darren New wrote:
> Orchid XP v8 wrote:
>> The assertion being that "stuff that doesn't change" is rarer than you
>> think it is.
>
> I'll grant that it's rarer than most people think it is. The trick is to
> consider whether if the PK changes, you're willing to treat the object
> as a new entity, or whether it's rare enough that you're willing to
> expunge the old ID entirely.

Seems reasonable. And I'm guessing that Mr Kyte was talking about huge 
multi-TB data warehouses where updating or removing all references to 
something would be a major PITA.

> I contend that most primary data in the database should be historical.

This also seems like a sensible way to go.

>> Until the GPS coordinates turn out to have been inaccurate.
>
> Then you fix it. There never *was* an intersection with those GPS
> coordinates. So you fix it. The point is to avoid collisions, not to
> necessarily have a PK that never changes.

The point is *totally* to have a PK that never changes, since changing a 
PK is a tricky business which you want to avoid. (Duplicate PKs would of 
course be much, much worse, but that's fairly easy to avoid.)

> Given that the BIGGEST TRANSACTIONAL DATABASE IN THE WORLD (*)
> identifies street intersections with GPS coordinates, I'm gonna have to
> go with GPS coordinates on this one. ;-)

OK, well I'm gonna have to say they must be doing something more precise 
than "stand in the general vicinity of the intersection, take down the 
GPS coordinates, and we'll just use those".

>> GPS is only accurate to a few hundred yards of course,
>
> Uh, no. What are you smoking?

Hey, the satellites are 20,000 km up in the sky. It's astonishing that 
GPS can even figure out which city your in, never mind fixing your 
location to a few hundred yards.

>> so different people will probably get different measurements depending
>> on where they're standing.
>
> Sure. But you're not asking someone to identify a real intersection
> based on their GPS coordinates. You're using it as a primary key.

So, what, you pick a GPS coordinate at random that's approximately in 
the right area? (So long as its unambiguously not near any *other* 
intersection.)

>> Then again, most intersections probably already *have* a synthetic
>> key. I don't know about your country, but around here major roads tend
>> to have uniquely numbered markers on the roadside...
>
> Assuming those don't change, they're probably based on geometry also.

They seem to just be numbered sequentially along the length of the road.

> The point of using GPS coordinates is that they are guaranteed not to
> change.

Sure. Unless the GPS coordinate system moves, which can't possibly 
happen. Oh, wait...

Post a reply to this message

Le 09/03/2011 10:29, Invisible a écrit :
> 
>>> GPS is only accurate to a few hundred yards of course,
>>
>> Uh, no. What are you smoking?
> 
> Hey, the satellites are 20,000 km up in the sky. It's astonishing that
> GPS can even figure out which city your in, never mind fixing your
> location to a few hundred yards.

GPS computation is accurate, with military access, to less than a yard.
One of its military purpose is guiding rocket/missile between buildings,
with a flight near the ground as close as possible to avoid radar detection.

Civil usage of GPS is accurate to 30 yards/meters.

Some area are covered with DGPS, an additional signal on radio which
provided the offsets measured between the GPS signal and the actual
location of reference points (so that a civil application can compensate
the delta...)

Remember that GPS is operated by the army of the USA (or whatever you
name it), and they can switch it down whenever they want. (or rather,
keeping it on only for the happy fews with the unscrambling devices...)

-- 
Software is like dirt - it costs time and money to change it and move it
around.

Just because you can't see it, it doesn't weigh anything,
and you can't drill a hole in it and stick a rivet into it doesn't mean
it's free.

Post a reply to this message

> GPS computation is accurate, with military access, to less than a yard.

So I've heard. This is only useful if you're in the military. ;-)

> Civil usage of GPS is accurate to 30 yards/meters.

That sounds more like it.

> Remember that GPS is operated by the army of the USA (or whatever you
> name it), and they can switch it down whenever they want. (or rather,
> keeping it on only for the happy fews with the unscrambling devices...)

*Other global positioning systems are available. ;-)

Post a reply to this message

> GPS computation is accurate, with military access, to less than a yard.
> One of its military purpose is guiding rocket/missile between buildings,
> with a flight near the ground as close as possible to avoid radar detection.
>
> Civil usage of GPS is accurate to 30 yards/meters.

I thought they removed the civil "randomness" in the GPS signal ages ago?

Post a reply to this message

On 3/8/2011 4:24 PM, Darren New wrote:
> Orchid XP v8 wrote:
>> Pro-tip: Don't use real-world things as primary keys. Because you know
>> what? Real-world things have this nasty habit of *changing*. You don't
>> ever want your primary key to change. This is why databases up and
>> down the country refer to Mr Smith not as Mr Smith but as customer
>> #238523.
>
> I disagree. The only time you should use automatically-generated primary
> keys is when there is no attribute of the item in the table that is
> guaranteed not to change.
>
> A sales receipt whose primary key is the cash register identifier and
> the time to the second when the receipt was printed is an excellent PK
> for a sales receipt. A record recording what song was playing on the
> radio has the obvious PK of location+timestamp+frequency. A road
> intersection has a fine PK of the GPS coordinates of the intersection.
>
> The problem is that people are changing all the time, so it's very hard
> to have a customer record that persists with a PK that makes sense.
>

I have to agree with Darren - A primary key is not necessarily an 
auto-generated value (random or auto-increment).  It only has to be 
unique and not change.

For me, the *easiest* way to guarantee no change is to have an 
auto-generated integer.  Can it be done better, yes.  But without the 
experience of how to look at data, I run too high of a chance that I 
chose the wrong key and it needs changed.

>> Use randomly-generated integers with no real-world meaning as PKs and
>> you'll have fewer problems if/when the thing the key refers to changes
>> in some way.
>
> The drawback of this is if you get a reference to the wrong value,
> you'll never notice.
>

I see the point in having the key have more meaning.  It reduces the 
mental referencing that must go on to understand the data without a 
proper interface.


>> I would suggest not setting up an Oracle database, for example. It
>> sounds like it would be utter overkill here.
>
> Yes. One of the free databases (Postgresql or mysql) or (on windows) SQL
> Server (Express, perhaps) would do fine. You'd probably find the most
> free help with MySql, but it's kind of messy to run on Windows, or at
> least was last I checked. SQL Server Express would *probably* have
> enough performance for you, given you were using Access.
>


I'm on this page fully.  I prefer to stay away from MS database 
solutions.  Not that they are not good, but they are just too ... well 
just 'too'.

Post a reply to this message

On 3/8/2011 5:03 PM, Orchid XP v8 wrote:
>
> Until the GPS coordinates turn out to have been inaccurate. GPS is only
> accurate to a few hundred yards of course, so different people will
> probably get different measurements depending on where they're standing.
> That's probably not a great idea. (Unless you explicitly round the
> coordinates up to some number that makes the size of the actual
> intersection negligable.)
>

I just ran across this where the GPS coordinate was wrong in a 
government data set - likely due to a typo.

Post a reply to this message

Invisible wrote:
> The point is *totally* to have a PK that never changes,

Well, yes, that's the desire. But you can only achieve that by having 
meaningless PKs on everything, which I find to be undesirable if a 
meaningful PK can be found.

If your database is likely to be big enough that an incorrect initial PK 
is 
going to be catastrophic, then you can go for meaningless PKs.

However, in Tom's case, the GPS coordinates being wrong means *everything
* 
is wrong for that pole, so changing the GPS coordinates might as well be 

treated as deleting the pole in the wrong place and creating the pole in 
the 
right place.

> OK, well I'm gonna have to say they must be doing something more precis
e 
> than "stand in the general vicinity of the intersection, take down the 

> GPS coordinates, and we'll just use those".

I don't know.

>>> GPS is only accurate to a few hundred yards of course,
>>
>> Uh, no. What are you smoking?
> 
> Hey, the satellites are 20,000 km up in the sky. It's astonishing that 

> GPS can even figure out which city your in, never mind fixing your 
> location to a few hundred yards.

"""
The phase difference error in the normal GPS amounts to 2–3 metre
s (6.6–9.8 
ft) of ambiguity. CPGPS working to within 1% of perfect transition reduce
s 
this error to 3 centimeters (1.2 in) of ambiguity. By eliminating this er
ror 
source, CPGPS coupled with DGPS normally realizes between 20–30 c
entimetres 
(7.9–12 in) of absolute accuracy.
[...]
However, 2 millimeter accuracy requires measuring the total phase—
the number 
of waves times the wavelength plus the fractional wavelength, which requi
res 
specially equipped receivers. This method has many surveying applications
.
"""

> So, what, you pick a GPS coordinate at random that's approximately in 
> the right area? (So long as its unambiguously not near any *other* 
> intersection.)

There's a certain level of error of a couple of meters. You pick a standa
rd 
place to measure from (such as the center of the manhole that you're 
interested in) and call that your location.

When you go back to find the intersection, are the GPS coordinates given 

good enough that someone else can tell what intersection you're talking a
bout?

It should be noted, btw, that they're not really interested in the 
intersections, but in the wires running under the roads. The same PK can 
be 
used to track the wires running under or over not-roads as well.

>> The point of using GPS coordinates is that they are guaranteed not to
>> change.
> 
> Sure. Unless the GPS coordinate system moves, which can't possibly 
> happen. Oh, wait...

Have latitude and longitude changed since we invented RDBMs?  I don't thi
nk so.

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

Le_Forgeron wrote:
> Civil usage of GPS is accurate to 30 yards/meters.

I think they turned off the encryption of the low bits back in Clinton's 
timeframe, when other countries started threatening to launch their own 
satellites. I think civilian GPS is quite accurate now.

Especially if you're measuring something that isn't moving, and you can let 
the device sit for a day, you can get down in the milimeter range.

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

On 3/8/2011 5:34 PM, Darren New wrote:
> Is it the same telephone pole if I dig it up and plant it somewhere
> else? Sure, maybe. But am I ever going to need to know that if my job
> involves routing cables? Probably not. Am I going to need to know that
> if my job involves replacing them when they wear out? Most definitely.
>
> How often do you replace the little tags on the side of the pole that
> give the pole number? Not very often. Is there any reason if that ID tag
> gets replaced that you can't just update in the database everyplace that
> appears? Probably not.
>

Poles move all of the time, but they are considered 'new' poles and not 
related to the old poles :-)  pole tags usually do not change, but if 
the tag is missing on one day, it could have a new one the next.  In 
fact, the power companies here tag their poles according to geographical 
coordinates - likely they use it as their primary key.  But I have seen 
some wrong tags that will likely get changed.

>> But sure, especially if you're recording historical data (which is of
>> course especially unlikely to change), there's no real need for a
>> synthetic key.
>
> I contend that most primary data in the database should be historical.
>
>> Until the GPS coordinates turn out to have been inaccurate.
>
> Then you fix it. There never *was* an intersection with those GPS
> coordinates. So you fix it. The point is to avoid collisions, not to
> necessarily have a PK that never changes.
>
> Given that the BIGGEST TRANSACTIONAL DATABASE IN THE WORLD (*)
> identifies street intersections with GPS coordinates, I'm gonna have to
> go with GPS coordinates on this one. ;-)
>
>
> Assuming those don't change, they're probably based on geometry also.
> The point of using GPS coordinates is that they are guaranteed not to
> change. If you enter them in the database wrong, then you have bad data,
> but that's not because the intersection moved.
>

I will stay out of this one....  I think I will stick with auto number 
fields for now - just to keep my head on straight.

Post a reply to this message