On 3/8/2011 3:42 PM, Darren New wrote:
> Tom Austin wrote:
>> I know that we will want to refer back to the data - either to 'copy'
>> it for another job or to just look at historical statistics - like you
>> point out.
>
> Then keep it in one database.
>
>> I've run across some database engines that support it, but it seemed
>> not to be a pointed feature that is used often.
>
> Right. Because most people don't do things that way. There's no good
> reason to make things a separate database unless you want to do
> administrative things differently like having different access or store
> it on different machines.
>

thanks, point made :-)


>> Our data structure is not too large in comparison to most databases.
>> Currently we have about 30 tables with maybe 5-10 more required if we
>> keep on the current design path.
>
> There's no need to denormalize, then. It also doesn't sound like you're
> doing anything highly transactional, so a quarter-second transaction is
> unlikely to bother anyone using it.
>

Having unoptimized programs makes it very slow.  Programs written as 
fast as possible do not benefit from being efficient.

But I think with optimization of how to access the data it can be made 
very quick.  Also, moving away from a Accedd .mdb file will speed things 
up considerably.


>> With any but the most simple database you will need automated systems
>> to access and keep the data straight. You have to decide where you are
>> going to put the work.
>
> The point of ACID (at least the C part) is that you declare the "keep
> the data straight" part in the database as data. Then a mistake in your
> application cannot corrupt your data in that way.
>

Thanks for the acronym - more info to go on :-)

I don't know if we will try or need to set up transactions, but they 
could be helpful if something is not a simple insert or update.

>> I guess the question is more:
>> It is good or bad practice to use text as the 'key' rather than
>> dereferencing to an integer type?
>
> The key is an identifier that won't change. What text do you have for
> the entity that won't change? The company name? The road the pole is on?
> The name of the person you should talk to about it? All these things can
> change.

point made - thanks :-)


>> Something you said here might work for us.
>> The poles that we collect do not change and can be reused for other
>> jobs - so keeping them around could be good.
>> The cable heights are more job specific as when the job is built the
>> height are now wrong.
>
> You're doing it wrong. The cable heights don't change. Instead, you have
> a range of dates when the cable was *this* high, and another range of
> dates when the cable was *that* high.
>

OK, you want a job surveying utility poles :-)  You have good insight.


> If you overwrite that information, then the customer later comes back
> and asks about it, now you have a problem. Instead, the primary key for
> the cable should be "company id, pole number, first date valid". Then
> your queries can find the height of the cable on any particular day by
> finding the latest date where that company's pole is listed.
>

At this point I am not used to setting up primary keys with multiple 
columns - but this is making some sense.  The question we have is if we 
have time to implement some such beast :-)


>> Exporting the data for archival purposes could be a good idea. Even
>> exporting a fully functional database with only the particular job
>> data required could be good.
>
> All those things are not-too-hard if all the data is in the database.
>

Yes, and if the database schema is identical, the same programs can use 
any database - from mySQL to .mdb files.


>> We are a small show so we cannot afford a ton of overhead to keep a
>> system running, yet we also do not want to set up a $20,000 db system
>> that is overkill for what we do.
>
> Fortunately, since free RDBMs have come out, the price of a database
> engine has dropped from $100K/user to free. ;-)
>

Well, almost free - just takes time to set up.  By the time all is said 
and done I figure that mySQL will take about $1k - $2k in my time to set 
up reliability.  Need to get it installed on our server and set up 
connection methods and backup methods.  Not too difficult, but I don't 
do it every day.

Post a reply to this message

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.

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

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

Tom Austin wrote:
> Yes, the data needs to be well understood before starting the schema. 
> There should be no anomalies to 'fit in' after the fact.

The absolute best way to make this work out, then, is to record "reality" in 
the database. Ignore how you're going to use the data while you're designing 
what's in the database (to the extent possible). Instead, simply record facts.

Of course you need to know the height of the poles and their locations and 
such, because that's what you do your calculations with.

What I'm trying to say is don't say "our calculations would be easier if our 
data was arranged like *this*."  If you stick with the reality of what's out 
there, you will never run into the situation where you have to *change* data 
you already have in the database.

I know this sounds kind of vague, and it can be hard to get away from it if 
you already have a model of something up and running and you know how that 
works.

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

Tom Austin wrote:
> Having unoptimized programs makes it very slow.  Programs written as 
> fast as possible do not benefit from being efficient.

Modern database systems are designed to take normalized tables and make 
accessing them very fast. That's the fundamental operation a relational 
database management system performs.

How big is your total collection of active databases right now? If it fits 
in (say) a gigabyte, chances are you can't possibly make it too slow.

If it *is* too slow when done "right", *then* you get out the query analyzer 
and look to see what it's spending its time doing, and you add the one index 
that cuts that time from minutes to miliseconds.

> Thanks for the acronym - more info to go on :-)
> 
> I don't know if we will try or need to set up transactions, but they 
> could be helpful if something is not a simple insert or update.

You will need transactions. Trust me on this one. :-)  If nothing else, 
you'll need transactions when you're archiving out an old job.

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.

>> The key is an identifier that won't change. What text do you have for
>> the entity that won't change? The company name? The road the pole is on?
>> The name of the person you should talk to about it? All these things can
>> change.
> 
> point made - thanks :-)

That siad, btw, it's often the case that you *can* find text that won't 
change often enough to make a difference. Stock ticker, street name, radio 
station call letters. If it changes rarely enough that you're willing to 
treat it as an "delete all the old, insert all the new" sort of transaction, 
using text is OK. One generally doesn't do this with people, but saying "if 
you change your stock ticker, you have to treat it as selling all the old 
shares and getting the same number of new shares from the new ticker name" 
isn't too unreasonable.

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

> At this point I am not used to setting up primary keys with multiple 
> columns - but this is making some sense.  The question we have is if we 
> have time to implement some such beast :-)

Look at it this way: This is the data that is vital to your company, in the 
strictest sense. Your company's job is to manage and evolve this data. You 
don't have time to get it wrong.

What about your current system is so broken that you can't afford the time 
to fix it?

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.

>>> Exporting the data for archival purposes could be a good idea. Even
>>> exporting a fully functional database with only the particular job
>>> data required could be good.
>>
>> All those things are not-too-hard if all the data is in the database.
>>
> 
> Yes, and if the database schema is identical, the same programs can use 
> any database - from mySQL to .mdb files.

That's the goal. As you get more sophisticated, you find that breaks down. 
When you use views in your "real" database and you archive it to something 
that has no views, you find yourself with a problem.

That said, even a cheap machine nowadays has more than enough processing 
power to do whole boatloads of database processing.

About how much data do you have, in total and in active jobs?  Many people 
seriously underestimate just how much processing even a $1000 machine can do 
on a SQL database.

> Well, almost free - just takes time to set up.  By the time all is said 
> and done I figure that mySQL will take about $1k - $2k in my time to set 
> up reliability.  Need to get it installed on our server and set up 
> connection methods and backup methods.  Not too difficult, but I don't 
> do it every day.

Be sure to factor in some ongoing maintenance time. Occasionally your 
replication will break, or something like that, and you'll have to fix stuff 
in a panic. :-)

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

The recommendation comes not so much from me, but from Tom Kyte. (He's 
an Oracle expert. I'm not entirely sure, but I believe he actually works 
for Oracle Corp.) The assertion being that "stuff that doesn't change" 
is rarer than you think it is.

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.

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

Agreed.

> A road
> intersection has a fine PK of the GPS coordinates of the intersection.

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

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

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

Quite.

> The drawback of this is if you get a reference to the wrong value,
> you'll never notice.

Well, perhaps. But if somebody gives you the wrong phone number, you'll 
"never notice" in the same sense...

-- 
http://blog.orphi.me.uk/
http://www.zazzle.com/MathematicalOrchid*

Post a reply to this message

Tom Austin wrote:
> Interesting way of looking at building a schema - just break it down to 
> objects.  I think this fits our data very well.  I think we were doing 
> this anyway, but not on purpose - more by accident.

Trust me on this: It's the only way you ever get a schema you don't have to 
change with every software update. Record what is "out there", and to the 
extent sensible, never overwrite data. If you find yourself thinking "We'll 
need to keep copies", you've done something wrong, because what that really 
means is you need the state of something at two different times, but you 
didn't record the times.  Record *everything*, in as raw a format as you can.

I am looking at a financial system right now where I never overwrite data. 
When someone swipes a card, I put into a table exactly what the card machine 
gave me: The card machine ID, the credit card number, the amount the clerk 
typed in, and the timestamp I got the record.  I don't record the merchant 
ID in that table, or the ID of the cardholder. That all comes later, when I 
process the records.

Why? Because the merchant might change merchant accounts before telling us. 
The cardholder might lose the card. Heck, we might even accidentally create 
two cards with the same number.

I'm blathering. Nevermind.

Anyway, yes, record everything from *outside* your company in one set of 
tables, as *raw* as you can. Changes to these tables should be idempotent. 
(I.e., you *should* be able to read the CSV file the telco gave you listing 
the pole information multiple times into the same table without corruption. 
This implies no automatically-generated primary key on those tables. That 
said, again, this is just a guideline and not always possible.)

Have a second set of tables, completely created from the first set, that 
holds any calculated information that might be difficult to recalculate 
(e.g., running account balances in a financial app), but which can be 
checked against and recreated from the raw data.

Once you have stuff working, figure out where it's slow, *then* fix that. 
Usually "fix that" involves changing the declarations of some indexes, not 
rearranging the actual data (which, if you have data for which you can 
follow the rules above, you can't really rearrange anyway).

> this might be worth considering, but I don't know how easy it will be to 
> implement and keep straight.  Seems like the a user created key like 
> this could lead to duplicates - but if it is a key it will prevent 
> duplicates anyway - just prompt for another key.

Oh, yes, this isn't for user keys. I meant where in a programming language 
you'd use an enumerated type.

So if you have a "status" row for jobs that says "in negotiation", "paid for 
but waiting for someone to be assigned", "that person is working on it", and 
"ready to be delivered", you could use this sort of coding to make your 
programs more readable. "NEGO", "PAID", "WORK", "DONE" rather than 0, 1, 2, 3.

> Most of the data in question is a simple map from an integer key to a 
> piece of text.  I do have some queries on the text with joins to pull 
> data from other tables.
> 
> The text is not designed to be changed.
> 
> The alternative that we have is to flatten the relationship and put the 
> text directly in the table instead of having a key reference.
> 
> ie
> go from
> 
> tablePole        tableOwners
> Pole   OwnerID        ID    Name
> 
> 
> to
> 
> tablePole
> Pole    OwnerName
> 
> 
> 
> Any thoughts on this?

If OwnerName shows up in multiple places, then these bits would need to be 
in a PK/FK relationship to properly cascade changes.

I assume each owner has more than one pole. Hence, duplicating the name for 
every pole is inefficient space-wise and dangerous update-wise. (Especially 
if you don't use transactions.)

Is the "owner" a real independent entity? Is it rational to speculate there 
might be an owner with no poles? Or an owner who has poles but hasn't told 
you about them yet? What about an owner who just sold his last pole to pay 
for the pole he's going to buy next week?

So, yes, here, "owner" is an actual entity, not just some field about a 
pole. The name of the owner isn't associated with the pole at all.

Indeed, the *best* way to structure this would be

table Pole: GPS coords, height.  (PK: GPS)

table Owner: ID, contact name, contact phone, etc etc. (PK: ID)

table Ownership: PoleGPS, OwnerID, StartingFromTime. (PK: GPS+StartingFromTime).

table Cables: PoleGPS, cable height, fiber height, power height, 
StartingFromTime. (PK: GPS + StartingFromTime)

Later, you can go back and ask questions like "Did AT&T ever change the 
height of the power lines on the pole at Fifth and Main, or was that 
Cingular, after they bought that line from AT&T?"


*That* might be a bit too normalized for your purposes, but you'll never get 
in a situation where someone sells a bunch of poles, then comes to you and 
asks you to recreate a report from before they sold the poles or something.

See if you can look at the columns you have and come up with a breakdown 
like this into "things really happening out there". You might find yourself 
a natural at it. :-)

Now, the "groups" kind of thing I was talking about, the "secondary" set of 
tables, might be tables that take a group of poles owned by the same 
company, that constitute a single "cable run". Large stretches of cable run 
might have the cable at the same height, so your calculations could be 
faster if you had a table that put each pole in a group, and then had the 
height of the cable on the group specified, or something like that.

It's hard coming up with that sort of thing when I don't know how your 
business works, tho, but that's the approximate sort of thing I was talking 
about.


>>> We are developing a new database system to take care of some severe
>>> shortcomings of the old system.
>>
>> What are the shortcomings?
>>
> 
> Let's just say is was a single table database with about 250 columns - 
> limited only by the fact that Excel at one time only allowed about 250 
> columns.

Oh, so the shortcoming is that it was rushed and grew instead of being 
designed. Makes sense. :-)

> Putting data in and getting data out was very difficult and required the 
> sacrifice of too many brain cells.

That's exactly where SQL queries between multiple tables will help. 
Thinking in SQL instead of in terms of iterating over things is tough, but 
it's almost always possible to come up with a statement in SQL that will 
pull out all and only the data you need. If you ever get to the point where 
you're saying "for each result from *this* query, submit *that* query", 
you're almost 100% sure you're doing something wrong. In my entire career, I 
only ever did that once, and that was after I'd written it the right way and 
figured out MySql was not optimizing things in the obvious way and I didn't 
know how to make it do so.

>>> Our data has a lot of common 'choose from' items - such as the owner
>>> of the pole.
>>> The data consists of pole specific information that doesn't change
>>> (owner, height, etc) and information that will likely change (the
>>> height of cables on the pole).
>>
>> These last two should probably be in separate tables that you join at
>> runtime. (Especially if you're using MySql, where differnet "engines"
>> give you different capabilities.)
>>
> 
> That's what we are trying to figure out - how to make it make sense to 
> have them in separate tables.

Let me know if the description of tables above does or doesn't make sense.

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

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.

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.

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

> GPS is only 
> accurate to a few hundred yards of course,

Uh, no. What are you smoking?

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

None of your customers identifies themself with your internal ID number, either.

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

>> The drawback of this is if you get a reference to the wrong value,
>> you'll never notice.
> 
> Well, perhaps. But if somebody gives you the wrong phone number, you'll 
> "never notice" in the same sense...

Except I'm not using a phone number as a primary key. :-)



(*) The 300 terabyte TURKS database. The size is probably bigger 15 years 
later, too.

-- 
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 4:43 PM, Darren New wrote:
> Tom Austin wrote:
>> Having unoptimized programs makes it very slow. Programs written as
>> fast as possible do not benefit from being efficient.
>
> Modern database systems are designed to take normalized tables and make
> accessing them very fast. That's the fundamental operation a relational
> database management system performs.
>
> How big is your total collection of active databases right now? If it
> fits in (say) a gigabyte, chances are you can't possibly make it too slow.
>
> If it *is* too slow when done "right", *then* you get out the query
> analyzer and look to see what it's spending its time doing, and you add
> the one index that cuts that time from minutes to miliseconds.
>

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.


>> Thanks for the acronym - more info to go on :-)
>>
>> I don't know if we will try or need to set up transactions, but they
>> could be helpful if something is not a simple insert or update.
>
> You will need transactions. Trust me on this one. :-) If nothing else,
> you'll need transactions when you're archiving out an old job.
>

I've worked with transactions some in the past and they make a lot of 
sense.  I'll have to try to incorporate them as we move forward.

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

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

>
> That siad, btw, it's often the case that you *can* find text that won't
> change often enough to make a difference. Stock ticker, street name,
> radio station call letters. If it changes rarely enough that you're
> willing to treat it as an "delete all the old, insert all the new" sort
> of transaction, using text is OK. One generally doesn't do this with
> people, but saying "if you change your stock ticker, you have to treat
> it as selling all the old shares and getting the same number of new
> shares from the new ticker name" isn't too unreasonable.
>

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


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

>
> Look at it this way: This is the data that is vital to your company, in
> the strictest sense. Your company's job is to manage and evolve this
> data. You don't have time to get it wrong.


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.

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

There were some cool features that I implemented - like a GIS type 
interface - before GIS systems were popular.


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

>>>> Exporting the data for archival purposes could be a good idea. Even
>>>> exporting a fully functional database with only the particular job
>>>> data required could be good.
>>>
>>> All those things are not-too-hard if all the data is in the database.
>>>
>>
>> Yes, and if the database schema is identical, the same programs can
>> use any database - from mySQL to .mdb files.
>
> That's the goal. As you get more sophisticated, you find that breaks
> down. When you use views in your "real" database and you archive it to
> something that has no views, you find yourself with a problem.
>

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.


> That said, even a cheap machine nowadays has more than enough processing
> power to do whole boatloads of database processing.
>
> About how much data do you have, in total and in active jobs? Many
> people seriously underestimate just how much processing even a $1000
> machine can do on a SQL database.
>

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.


>> Well, almost free - just takes time to set up. By the time all is said
>> and done I figure that mySQL will take about $1k - $2k in my time to
>> set up reliability. Need to get it installed on our server and set up
>> connection methods and backup methods. Not too difficult, but I don't
>> do it every day.
>
> Be sure to factor in some ongoing maintenance time. Occasionally your
> replication will break, or something like that, and you'll have to fix
> stuff in a panic. :-)
>

yes, just like last week with out servers.

In the past 3 years I have had 1 or 2 incidents that required immediate 
attention.

The past week brought no less than 5 of them.

 From full OS drives to failed RAID mirrors.

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

Post a reply to this message

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