On 3/8/2011 10:01 AM, Invisible wrote:
> On 08/03/2011 02:21 PM, Tom Austin wrote:
>
>> 1. Is the practice of creating separate databases for each 'job'
>> considered good or bad?
>
> That's going to depend on the database engine in question. (Different
> engines have different ideas about what constitutes a "database".) It's
> also going to depend on your idea of what a "job" is - and also what you
> actually want to use the data *for*.
>
> For example, if each job is a separate database, you're going to have a
> lot of fun trying to compute (for example) the average number of days
> spent working on a job. If all the jobs were in a single database, this
> would be a trivial SQL query, but if each job is a separate database,
> you now have a logistical nightmare figuring out how to open and query
> all the jobs, one at a time.
>
> But then again, if you never need to do this kind of computation, the
> fact that it's really difficult to do becomes irrelevant.
>

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.


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

>
>> 3. Normalizing vs Flattening? At what point is a database 'too'
>> normalized and does it matter?
>
> Usually the /only/ reason for denormalising a database is performance.
> So you could say a database is "too normalised" if it's taking too long
> to answer your queries. You might perhaps also argue that a database is
> "too normalised" if it has become too complicated to understand - but
> normally you won't be interacting with tables directly, you'll put
> something pretty over the top to make it "look simple".
>

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.


> Q: Why do we even care about normalising?
> A:
>
> - If the data is not normalised, you are duplicating data in some sense.
> - If data is duplicated, it is extremely likely that the duplicates will
> eventually get out of sync with each other.
> - When this happens, you now have conflicting information, and no idea
> which version (if any!) is actually correct.
>
> That said, denormalisation can work. If you have /automated/ systems to
> keep all the copies in sync, it can be reliable. But now you are making
> your job "more complicated". Usually, a fully normalised set of tables
> means less things to go wrong. (But it also means more tables,
> typically, which may make the thing harder to understand...)
>

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.


>> 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.
>
> On the other hand, if those are *codes* then they probably should be
> text. E.g., telephone numbers, invoice numbers, product codes, anything
> like that, probably wants to be text.
>
> Ask yourself this: are 7 and 007 the same thing, or different things? If
> you want them to be the same, you want integers. If you want them to be
> different, it's text.
>
> Don't even talk to be about storing dates as anything that isn't a date...
>

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?


>> Our core business is surveying utility/telephone poles to put new fiber
>> optic cables on them. We propose changes to the pole (moving cables) and
>> prepare reports/applications for our client to get on the pole.
>>
>> We collect and store our data in databases.
>> In the past we have used separate MS Access .mdb databases for each
>> 'job'.
>> We are developing a new database system to take care of some severe
>> shortcomings of the old system.
>> 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).
>
> MS Access is a poor excuse for a real database system, but it *does*
> have the distinct advantage of being /simple/. An Access database is
> just a file. You can manage it as such. A database using a "real"
> database engine such as Oracle or PostgreSQL or whatever usually
> involves configuring server daemon processes and installing multiple
> files and setting up authentication and so on. This is far more secure
> and provides much better performance, but it's also far more complicated.
>
> 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.

>
> Of course, we have [expensive] software that handles all of this
> automatically, needless to say... You have to balance how much
> functionality you actually need against how hard it's going to be to set
> up and run all this stuff. Just like any other computer project.

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.



FYI,  my may seem a bit vague and non informal as I am trying to keep my 
personal bias out of what you guys feedback.


Thanks

Post a reply to this message