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