>>> 2. How is creating references across databases viewed?
>>> Example - one database with definitions of data with many other
>>> databases referencing the definitions.
>>
>> Again, it varies by engine as to how hard it is to set up, how reliable
>> it is, and any other gotchas involved.
>
> I've run across some database engines that support it, but it seemed not
> to be a pointed feature that is used often.

If you're a large company with legacy systems that you need to integrate 
with, database references can be very useful. Personally, I would avoid 
them unless you "really need to".

>> That said, denormalisation can work. If you have /automated/ systems to
>> keep all the copies in sync, it can be reliable.
>
> 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.

Well, for example, apparently people use Access as a front-end. (Even if 
Access isn't the database. You can apparently set up, say, an Ingress DB 
and put an Access front-end on it. Apparently it isn't especially hard.)

I did 6 months' work experience at a small place with a big complicated 
Access database. I don't know how normalised it was, but I know that 
Access certainly can hold data normalised and denormalise it for when 
you want to look at it, renormalising it again behind the scenes.

>>> Do you have some thoughts on joins using text data types instead of
>>> integer numbers?
>>
>> If those are *supposed* to be integer numbers, you should store them as
>> such. If you don't, you're just asking for trouble some day.
>
> 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?

In terms of efficiency, it makes virtually no difference.

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.

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.

>> What our main project system does is this: The "jobs" that we are
>> currently working on are held in an Oracle database running on a big
>> powerful DB server. When a job is completed, the job is "archived" into
>> an Access database file. If we ever want to go back and do something
>> with the job, it is "dearchived" back into the Oracle database.
>
> 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.
> 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.

I try to be helpful. ;-)

I also presume that you have a far better idea of what your goals 
actually are than I do. So I give you the logic and let you figure out 
the best answer.

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

Yeah, I kind of figured that by reading between the lines. ;-)

I would suggest not setting up an Oracle database, for example. It 
sounds like it would be utter overkill here.

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

Post a reply to this message