On 3/8/2011 3:39 PM, Orchid XP v8 wrote:
>>>> 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".
>

thanks for the input

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

Yes - we used to use access for the front and back ends - until we 
needed a bit more flexibility on how to present/use the data.

I've also seen access used with other db engines - can be useful, but 
painful at the same time.


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

We only use access .mdb files - not access itself.  We interface the 
databases with custom programming.

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

makes sense - any text may need to eventually change which can break a 
lot of things if reference integrity is not completely automatic with 
the text.

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

that's a good way to get people to follow you :-)


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

OK smarty - just how many people do you think we have here :-?

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

yes, even MySQL might be overkill  :-)

Post a reply to this message