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.

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

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

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

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

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.

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

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

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