 |
|
|
|
|
|
| |
| |
|
|
|
|
| |
| |
|
|
A change for all of you database experts to shine....
I am working with a group to set up a new database system to handle our
core work flow data.
We have a working model of the database but are considering some changes
that affect the core of how it operates and I am seeking input as to
some best/worst practices.
Any insight into the following issues/questions would be most greatly
appreciated.
1. Is the practice of creating separate databases for each 'job'
considered good or bad? Is this answer different based on using MS
Access .mdb files vs using a MySQL Database engine?
2. How is creating references across databases viewed?
Example - one database with definitions of data with many other
databases referencing the definitions.
3. Normalizing vs Flattening? At what point is a database 'too'
normalized and does it matter? Do you have some thoughts on joins using
text data types instead of integer numbers?
For further reading, some background...
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).
Thanks for any insight or info that you may provide.
Tom
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
| |
|
|
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.
> 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.
> 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".
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...)
> 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...
> 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.
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.
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
| |
|
|
Le 08/03/2011 15:21, Tom Austin a écrit :
> A change for all of you database experts to shine....
>
> I am working with a group to set up a new database system to handle our
> core work flow data.
>
> We have a working model of the database but are considering some changes
> that affect the core of how it operates and I am seeking input as to
> some best/worst practices.
>
> Any insight into the following issues/questions would be most greatly
> appreciated.
>
>
> 1. Is the practice of creating separate databases for each 'job'
> considered good or bad? Is this answer different based on using MS
> Access .mdb files vs using a MySQL Database engine?
>
If the pieces of data are common or shared, even by the tiniest amount,
a single database is the answer.
Otherwise, how would you handle data's coherency ?
Access won't scale, drop it past the template step.
I won't enter holy wars about other SQL engines, but MySQL is rather
light (well, it get better every year).
You might really get benefit from engine which support views.
>
> 2. How is creating references across databases viewed?
> Example - one database with definitions of data with many other
> databases referencing the definitions.
Go back to step 1;
You probably mean read-only views for a job.
>
>
> 3. Normalizing vs Flattening? At what point is a database 'too'
> normalized and does it matter? Do you have some thoughts on joins using
> text data types instead of integer numbers?
Do not optimise before actual tests.
Normalise at least to 3rd form for your data, and keep it so!
The schema of the database should be checked up to 5th form, but that
usually transparent with good database people that are used to the task.
Conceiving the full schema (for all jobs) is needed before writing the
first line, or you will end up rewriting code when a job breaks a
relationship in many...
If you have performance issue, adding some index can help.
If you are normalised to 3++, you should not have blob of data to scan
to achieve your tasks.
Last point: any personal data (such as name of customer) might requires
the clearance of the local law authority (declaring the database, and
getting it allowed, obligation to allow query and modify on basis of
remote people)... For instance, the IP address is such personal piece,
even without name.
--
Software is like dirt - it costs time and money to change it and move it
around.
Just because you can't see it, it doesn't weigh anything,
and you can't drill a hole in it and stick a rivet into it doesn't mean
it's free.
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
| |
|
|
Tom Austin wrote:
> 1. Is the practice of creating separate databases for each 'job'
> considered good or bad? Is this answer different based on using MS
> Access .mdb files vs using a MySQL Database engine?
As others have pointed out, if you're ever going to want to do any sort of
cross-job statistics (like knowing if the same person worked on two
particular jobs, etc) then you need to have one database.
The only time I've seen multiple databases with identical schemas used that
was *not* a f'up, it was because it was a software-as-a-service and each
customer got their own database and essentially managed it themselves. I.e.,
if you can't imagine installing each database at a separate company, you
probably want one database.
> 2. How is creating references across databases viewed?
> Example - one database with definitions of data with many other
> databases referencing the definitions.
You really can't do that. That's exactly why you put everything in one
database.
That said, I think some databases let you make cross-database references,
but ... really, just don't do that.
> 3. Normalizing vs Flattening? At what point is a database 'too'
> normalized and does it matter? Do you have some thoughts on joins using
> text data types instead of integer numbers?
Each table that does not necessarily have any references to its rows is
representing a separate real-world entity. For example, you might have a
customer, a sale, etc. Subsidiary tables such as individual lines of a sale,
might be separate in tables, but they won't represent a separate entity.
(You can usually tell because the row in the subsidiary table will have an
arbitrary primary key (if the PK isn't just a combination of FKs) *and* a FK
that's exactly the PK of what it's a subsidiary table to.) That, IME, is
approximately the right way to think about it and break it down. Build the
DB as tables of real-world objects and events, then hook them together
appropriately. You'll almost automatically be "normalized enough". The time
you get denormalized is when you start thinking about how you're going to be
processing the data before you think about the actual data, and you start
doing joins in your head and encoding them into the schema.
Another good way of working it is to see if there's any combination of
columns that can be a primary key that isn't just some arbitrary number. I
built an entire database designed to track people's financial accounts (on
behalf of a bunch of *our* customers) for a stored-value web site that
didn't have a single auto-increment field in it. (Granted, our customer
supplied the identifier for *their* customer, so without that I would have
needed one auto-increment field.) But everything else was keyed off that.
The credit card number was the primary key for the credit card. The sale's
primary key was the credit card number and the UTC timestamp of when we made
the sale. The end-user's PK was our customer's PK concatenated with the
identifier we had assigned to our customer. In other work, places were keyed
by the three-letter code of the airport closest to the place (i.e., that's
how we identified cities), radio stations were identified by their location
and frequency, etc. It's not always possible to find such a key, but to the
extent you can, it tells you a whole bunch about the meaning of the record.
And if you use that and find it too slow, *then* it's easy to change it to a
simple integer identifier.
As for text, I've found that a very good trick for readability is that a
CHAR(4) will index just as fast as an INT. So if you want the database to
be both fast and readable with codes, you use codes like 'SALE', 'RFND',
'VOID', 'CUST', 'MNGR', 'EMPL', etc. Then, if you want, you can have
another table mapping these codes into a varchar(250) or something to put in
drop-down lists.
If you're talking about text or integer for other things, ask if there's any
possibility the text will ever change. Is it the name of a company or
person? A phone number or zip code? If so, it needs to be in a table and not
part of any key.
> We are developing a new database system to take care of some severe
> shortcomings of the old system.
What are the shortcomings?
> 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).
These last two should probably be in separate tables that you join at
runtime. (Especially if you're using MySql, where differnet "engines" give
you different capabilities.)
--
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
|
|
| |
| |
|
|
|
|
| |
| |
|
|
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
|
|
| |
| |
|
|
|
|
| |
| |
|
|
>>> 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
|
|
| |
| |
|
|
|
|
| |
| |
|
|
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
|
|
| |
| |
|
|
|
|
| |
| |
|
|
On 3/8/2011 11:10 AM, Le_Forgeron wrote:
>
> If the pieces of data are common or shared, even by the tiniest amount,
> a single database is the answer.
>
> Otherwise, how would you handle data's coherency ?
>
> Access won't scale, drop it past the template step.
> I won't enter holy wars about other SQL engines, but MySQL is rather
> light (well, it get better every year).
> You might really get benefit from engine which support views.
>
no, not asking for a holy war :-) tho MySQL is my default if we go the
single database engine direction.
views present an interesting option - possibly ways of flattening the
database without requiring that the data itself be flattened
I also like the 'snapshot' of job data export that Andrew mentioned -
might fix some of our concerns.
>>
>> 3. Normalizing vs Flattening? At what point is a database 'too'
>> normalized and does it matter? Do you have some thoughts on joins using
>> text data types instead of integer numbers?
>
> Do not optimise before actual tests.
>
> Normalise at least to 3rd form for your data, and keep it so!
> The schema of the database should be checked up to 5th form, but that
> usually transparent with good database people that are used to the task.
>
> Conceiving the full schema (for all jobs) is needed before writing the
> first line, or you will end up rewriting code when a job breaks a
> relationship in many...
>
> If you have performance issue, adding some index can help.
> If you are normalised to 3++, you should not have blob of data to scan
> to achieve your tasks.
>
Yes, the data needs to be well understood before starting the schema.
There should be no anomalies to 'fit in' after the fact.
But the fact is that we have limited time to put this together in and a
lot of data that can be rather tightly intertwined. So we have to run
with 90% and figure out how to cleanly fit things in as we go along.
Not the best approach, but it does make it 'usable' earlier.
We are at the point of stepping back and trying to put together a better
schema.
FYI, my questions and information may seem a bit vague and informal as I
am trying to keep my personal bias out of what you guys feed back.
> Last point: any personal data (such as name of customer) might requires
> the clearance of the local law authority (declaring the database, and
> getting it allowed, obligation to allow query and modify on basis of
> remote people)... For instance, the IP address is such personal piece,
> even without name.
>
Thanks for the tip - tho we don't track people or their data. We look
at telephone poles :-)
Thanks :-)
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
| |
|
|
On 3/8/2011 12:30 PM, Darren New wrote:
> Tom Austin wrote:
>> 1. Is the practice of creating separate databases for each 'job'
>> considered good or bad? Is this answer different based on using MS
>> Access .mdb files vs using a MySQL Database engine?
>
> As others have pointed out, if you're ever going to want to do any sort
> of cross-job statistics (like knowing if the same person worked on two
> particular jobs, etc) then you need to have one database.
>
> The only time I've seen multiple databases with identical schemas used
> that was *not* a f'up, it was because it was a software-as-a-service and
> each customer got their own database and essentially managed it
> themselves. I.e., if you can't imagine installing each database at a
> separate company, you probably want one database.
>
>
Yes, I can think of a few instances of where it could be very useful. I
am trying to feel out how usual the practice may be. Thanks for the info.
>
> Each table that does not necessarily have any references to its rows is
> representing a separate real-world entity. For example, you might have a
> customer, a sale, etc. Subsidiary tables such as individual lines of a
> sale, might be separate in tables, but they won't represent a separate
> entity. (You can usually tell because the row in the subsidiary table
> will have an arbitrary primary key (if the PK isn't just a combination
> of FKs) *and* a FK that's exactly the PK of what it's a subsidiary table
> to.) That, IME, is approximately the right way to think about it and
> break it down. Build the DB as tables of real-world objects and events,
> then hook them together appropriately. You'll almost automatically be
> "normalized enough". The time you get denormalized is when you start
> thinking about how you're going to be processing the data before you
> think about the actual data, and you start doing joins in your head and
> encoding them into the schema.
>
Interesting way of looking at building a schema - just break it down to
objects. I think this fits our data very well. I think we were doing
this anyway, but not on purpose - more by accident.
> Another good way of working it is to see if there's any combination of
> columns that can be a primary key that isn't just some arbitrary number.
> I built an entire database designed to track people's financial accounts
> (on behalf of a bunch of *our* customers) for a stored-value web site
> that didn't have a single auto-increment field in it. (Granted, our
> customer supplied the identifier for *their* customer, so without that I
> would have needed one auto-increment field.) But everything else was
> keyed off that. The credit card number was the primary key for the
> credit card. The sale's primary key was the credit card number and the
> UTC timestamp of when we made the sale. The end-user's PK was our
> customer's PK concatenated with the identifier we had assigned to our
> customer. In other work, places were keyed by the three-letter code of
> the airport closest to the place (i.e., that's how we identified
> cities), radio stations were identified by their location and frequency,
> etc. It's not always possible to find such a key, but to the extent you
> can, it tells you a whole bunch about the meaning of the record. And if
> you use that and find it too slow, *then* it's easy to change it to a
> simple integer identifier.
>
Sounds like you had fun. I like how you had to get creative on how to
set primary keys - and using a pre-established system to boot (the
airport codes) - genius.
> As for text, I've found that a very good trick for readability is that a
> CHAR(4) will index just as fast as an INT. So if you want the database
> to be both fast and readable with codes, you use codes like 'SALE',
> 'RFND', 'VOID', 'CUST', 'MNGR', 'EMPL', etc. Then, if you want, you can
> have another table mapping these codes into a varchar(250) or something
> to put in drop-down lists.
>
this might be worth considering, but I don't know how easy it will be to
implement and keep straight. Seems like the a user created key like
this could lead to duplicates - but if it is a key it will prevent
duplicates anyway - just prompt for another key.
> If you're talking about text or integer for other things, ask if there's
> any possibility the text will ever change. Is it the name of a company
> or person? A phone number or zip code? If so, it needs to be in a table
> and not part of any key.
>
Most of the data in question is a simple map from an integer key to a
piece of text. I do have some queries on the text with joins to pull
data from other tables.
The text is not designed to be changed.
The alternative that we have is to flatten the relationship and put the
text directly in the table instead of having a key reference.
ie
go from
tablePole tableOwners
Pole OwnerID ID Name
to
tablePole
Pole OwnerName
Any thoughts on this?
>> We are developing a new database system to take care of some severe
>> shortcomings of the old system.
>
> What are the shortcomings?
>
Let's just say is was a single table database with about 250 columns -
limited only by the fact that Excel at one time only allowed about 250
columns.
Putting data in and getting data out was very difficult and required the
sacrifice of too many brain cells.
>> 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).
>
> These last two should probably be in separate tables that you join at
> runtime. (Especially if you're using MySql, where differnet "engines"
> give you different capabilities.)
>
That's what we are trying to figure out - how to make it make sense to
have them in separate tables.
FYI, my statements and questions may seem vague or uninformative. I am
trying to get input without having my bias guide it.
Thanks
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
| |
|
|
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
|
|
| |
| |
|
|
|
|
| |
| |
|
|
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
|
|
| |
| |
|
|
|
|
| |
| |
|
|
Orchid XP v8 wrote:
> 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.
I disagree. The only time you should use automatically-generated primary
keys is when there is no attribute of the item in the table that is
guaranteed not to change.
A sales receipt whose primary key is the cash register identifier and the
time to the second when the receipt was printed is an excellent PK for a
sales receipt. A record recording what song was playing on the radio has the
obvious PK of location+timestamp+frequency. A road intersection has a fine
PK of the GPS coordinates of the intersection.
The problem is that people are changing all the time, so it's very hard to
have a customer record that persists with a PK that makes sense.
> 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.
The drawback of this is if you get a reference to the wrong value, you'll
never notice.
> I would suggest not setting up an Oracle database, for example. It
> sounds like it would be utter overkill here.
Yes. One of the free databases (Postgresql or mysql) or (on windows) SQL
Server (Express, perhaps) would do fine. You'd probably find the most free
help with MySql, but it's kind of messy to run on Windows, or at least was
last I checked. SQL Server Express would *probably* have enough performance
for you, given you were using Access.
--
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
|
|
| |
| |
|
|
|
|
| |
| |
|
|
Tom Austin wrote:
> Yes, the data needs to be well understood before starting the schema.
> There should be no anomalies to 'fit in' after the fact.
The absolute best way to make this work out, then, is to record "reality" in
the database. Ignore how you're going to use the data while you're designing
what's in the database (to the extent possible). Instead, simply record facts.
Of course you need to know the height of the poles and their locations and
such, because that's what you do your calculations with.
What I'm trying to say is don't say "our calculations would be easier if our
data was arranged like *this*." If you stick with the reality of what's out
there, you will never run into the situation where you have to *change* data
you already have in the database.
I know this sounds kind of vague, and it can be hard to get away from it if
you already have a model of something up and running and you know how that
works.
--
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
|
|
| |
| |
|
|
|
|
| |
| |
|
|
Tom Austin wrote:
> Having unoptimized programs makes it very slow. Programs written as
> fast as possible do not benefit from being efficient.
Modern database systems are designed to take normalized tables and make
accessing them very fast. That's the fundamental operation a relational
database management system performs.
How big is your total collection of active databases right now? If it fits
in (say) a gigabyte, chances are you can't possibly make it too slow.
If it *is* too slow when done "right", *then* you get out the query analyzer
and look to see what it's spending its time doing, and you add the one index
that cuts that time from minutes to miliseconds.
> 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.
You will need transactions. Trust me on this one. :-) If nothing else,
you'll need transactions when you're archiving out an old job.
That said, it's surprisingly common that if you structure your queries
right, you can make every operation essentially an "append to this table",
followed by an eventual "update that table in a way that if it fails, we can
reconstruct that table entirely from scratch". Think Journal -> General
Ledger.
>> 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 :-)
That siad, btw, it's often the case that you *can* find text that won't
change often enough to make a difference. Stock ticker, street name, radio
station call letters. If it changes rarely enough that you're willing to
treat it as an "delete all the old, insert all the new" sort of transaction,
using text is OK. One generally doesn't do this with people, but saying "if
you change your stock ticker, you have to treat it as selling all the old
shares and getting the same number of new shares from the new ticker name"
isn't too unreasonable.
> OK, you want a job surveying utility poles :-) You have good insight.
I've just done this dozens of times in the last couple decades.
> 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 :-)
Look at it this way: This is the data that is vital to your company, in the
strictest sense. Your company's job is to manage and evolve this data. You
don't have time to get it wrong.
What about your current system is so broken that you can't afford the time
to fix it?
The other possibility is to hire someone who actually does this sort of
stuff for a living for long enough to show you how to set up the basics of
the database.
>>> 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.
That's the goal. As you get more sophisticated, you find that breaks down.
When you use views in your "real" database and you archive it to something
that has no views, you find yourself with a problem.
That said, even a cheap machine nowadays has more than enough processing
power to do whole boatloads of database processing.
About how much data do you have, in total and in active jobs? Many people
seriously underestimate just how much processing even a $1000 machine can do
on a SQL database.
> 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.
Be sure to factor in some ongoing maintenance time. Occasionally your
replication will break, or something like that, and you'll have to fix stuff
in a panic. :-)
--
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
|
|
| |
| |
|
|
|
|
| |
| |
|
|
On 08/03/2011 09:24 PM, Darren New wrote:
> Orchid XP v8 wrote:
>> 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.
>
> I disagree. The only time you should use automatically-generated primary
> keys is when there is no attribute of the item in the table that is
> guaranteed not to change.
The recommendation comes not so much from me, but from Tom Kyte. (He's
an Oracle expert. I'm not entirely sure, but I believe he actually works
for Oracle Corp.) The assertion being that "stuff that doesn't change"
is rarer than you think it is.
But sure, especially if you're recording historical data (which is of
course especially unlikely to change), there's no real need for a
synthetic key.
> A sales receipt whose primary key is the cash register identifier and
> the time to the second when the receipt was printed is an excellent PK
> for a sales receipt.
Agreed.
> A road
> intersection has a fine PK of the GPS coordinates of the intersection.
Until the GPS coordinates turn out to have been inaccurate. GPS is only
accurate to a few hundred yards of course, so different people will
probably get different measurements depending on where they're standing.
That's probably not a great idea. (Unless you explicitly round the
coordinates up to some number that makes the size of the actual
intersection negligable.)
Then again, most intersections probably already *have* a synthetic key.
I don't know about your country, but around here major roads tend to
have uniquely numbered markers on the roadside...
> The problem is that people are changing all the time, so it's very hard
> to have a customer record that persists with a PK that makes sense.
Quite.
> The drawback of this is if you get a reference to the wrong value,
> you'll never notice.
Well, perhaps. But if somebody gives you the wrong phone number, you'll
"never notice" in the same sense...
--
http://blog.orphi.me.uk/
http://www.zazzle.com/MathematicalOrchid*
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
| |
|
|
Tom Austin wrote:
> Interesting way of looking at building a schema - just break it down to
> objects. I think this fits our data very well. I think we were doing
> this anyway, but not on purpose - more by accident.
Trust me on this: It's the only way you ever get a schema you don't have to
change with every software update. Record what is "out there", and to the
extent sensible, never overwrite data. If you find yourself thinking "We'll
need to keep copies", you've done something wrong, because what that really
means is you need the state of something at two different times, but you
didn't record the times. Record *everything*, in as raw a format as you can.
I am looking at a financial system right now where I never overwrite data.
When someone swipes a card, I put into a table exactly what the card machine
gave me: The card machine ID, the credit card number, the amount the clerk
typed in, and the timestamp I got the record. I don't record the merchant
ID in that table, or the ID of the cardholder. That all comes later, when I
process the records.
Why? Because the merchant might change merchant accounts before telling us.
The cardholder might lose the card. Heck, we might even accidentally create
two cards with the same number.
I'm blathering. Nevermind.
Anyway, yes, record everything from *outside* your company in one set of
tables, as *raw* as you can. Changes to these tables should be idempotent.
(I.e., you *should* be able to read the CSV file the telco gave you listing
the pole information multiple times into the same table without corruption.
This implies no automatically-generated primary key on those tables. That
said, again, this is just a guideline and not always possible.)
Have a second set of tables, completely created from the first set, that
holds any calculated information that might be difficult to recalculate
(e.g., running account balances in a financial app), but which can be
checked against and recreated from the raw data.
Once you have stuff working, figure out where it's slow, *then* fix that.
Usually "fix that" involves changing the declarations of some indexes, not
rearranging the actual data (which, if you have data for which you can
follow the rules above, you can't really rearrange anyway).
> this might be worth considering, but I don't know how easy it will be to
> implement and keep straight. Seems like the a user created key like
> this could lead to duplicates - but if it is a key it will prevent
> duplicates anyway - just prompt for another key.
Oh, yes, this isn't for user keys. I meant where in a programming language
you'd use an enumerated type.
So if you have a "status" row for jobs that says "in negotiation", "paid for
but waiting for someone to be assigned", "that person is working on it", and
"ready to be delivered", you could use this sort of coding to make your
programs more readable. "NEGO", "PAID", "WORK", "DONE" rather than 0, 1, 2, 3.
> Most of the data in question is a simple map from an integer key to a
> piece of text. I do have some queries on the text with joins to pull
> data from other tables.
>
> The text is not designed to be changed.
>
> The alternative that we have is to flatten the relationship and put the
> text directly in the table instead of having a key reference.
>
> ie
> go from
>
> tablePole tableOwners
> Pole OwnerID ID Name
>
>
> to
>
> tablePole
> Pole OwnerName
>
>
>
> Any thoughts on this?
If OwnerName shows up in multiple places, then these bits would need to be
in a PK/FK relationship to properly cascade changes.
I assume each owner has more than one pole. Hence, duplicating the name for
every pole is inefficient space-wise and dangerous update-wise. (Especially
if you don't use transactions.)
Is the "owner" a real independent entity? Is it rational to speculate there
might be an owner with no poles? Or an owner who has poles but hasn't told
you about them yet? What about an owner who just sold his last pole to pay
for the pole he's going to buy next week?
So, yes, here, "owner" is an actual entity, not just some field about a
pole. The name of the owner isn't associated with the pole at all.
Indeed, the *best* way to structure this would be
table Pole: GPS coords, height. (PK: GPS)
table Owner: ID, contact name, contact phone, etc etc. (PK: ID)
table Ownership: PoleGPS, OwnerID, StartingFromTime. (PK: GPS+StartingFromTime).
table Cables: PoleGPS, cable height, fiber height, power height,
StartingFromTime. (PK: GPS + StartingFromTime)
Later, you can go back and ask questions like "Did AT&T ever change the
height of the power lines on the pole at Fifth and Main, or was that
Cingular, after they bought that line from AT&T?"
*That* might be a bit too normalized for your purposes, but you'll never get
in a situation where someone sells a bunch of poles, then comes to you and
asks you to recreate a report from before they sold the poles or something.
See if you can look at the columns you have and come up with a breakdown
like this into "things really happening out there". You might find yourself
a natural at it. :-)
Now, the "groups" kind of thing I was talking about, the "secondary" set of
tables, might be tables that take a group of poles owned by the same
company, that constitute a single "cable run". Large stretches of cable run
might have the cable at the same height, so your calculations could be
faster if you had a table that put each pole in a group, and then had the
height of the cable on the group specified, or something like that.
It's hard coming up with that sort of thing when I don't know how your
business works, tho, but that's the approximate sort of thing I was talking
about.
>>> We are developing a new database system to take care of some severe
>>> shortcomings of the old system.
>>
>> What are the shortcomings?
>>
>
> Let's just say is was a single table database with about 250 columns -
> limited only by the fact that Excel at one time only allowed about 250
> columns.
Oh, so the shortcoming is that it was rushed and grew instead of being
designed. Makes sense. :-)
> Putting data in and getting data out was very difficult and required the
> sacrifice of too many brain cells.
That's exactly where SQL queries between multiple tables will help.
Thinking in SQL instead of in terms of iterating over things is tough, but
it's almost always possible to come up with a statement in SQL that will
pull out all and only the data you need. If you ever get to the point where
you're saying "for each result from *this* query, submit *that* query",
you're almost 100% sure you're doing something wrong. In my entire career, I
only ever did that once, and that was after I'd written it the right way and
figured out MySql was not optimizing things in the obvious way and I didn't
know how to make it do so.
>>> 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).
>>
>> These last two should probably be in separate tables that you join at
>> runtime. (Especially if you're using MySql, where differnet "engines"
>> give you different capabilities.)
>>
>
> That's what we are trying to figure out - how to make it make sense to
> have them in separate tables.
Let me know if the description of tables above does or doesn't make sense.
--
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
|
|
| |
| |
|
|
|
|
| |
| |
|
|
Orchid XP v8 wrote:
> The assertion being that "stuff that doesn't change"
> is rarer than you think it is.
I'll grant that it's rarer than most people think it is. The trick is to
consider whether if the PK changes, you're willing to treat the object as a
new entity, or whether it's rare enough that you're willing to expunge the
old ID entirely.
Is it the same telephone pole if I dig it up and plant it somewhere else?
Sure, maybe. But am I ever going to need to know that if my job involves
routing cables? Probably not. Am I going to need to know that if my job
involves replacing them when they wear out? Most definitely.
How often do you replace the little tags on the side of the pole that give
the pole number? Not very often. Is there any reason if that ID tag gets
replaced that you can't just update in the database everyplace that appears?
Probably not.
> But sure, especially if you're recording historical data (which is of
> course especially unlikely to change), there's no real need for a
> synthetic key.
I contend that most primary data in the database should be historical.
> Until the GPS coordinates turn out to have been inaccurate.
Then you fix it. There never *was* an intersection with those GPS
coordinates. So you fix it. The point is to avoid collisions, not to
necessarily have a PK that never changes.
Given that the BIGGEST TRANSACTIONAL DATABASE IN THE WORLD (*) identifies
street intersections with GPS coordinates, I'm gonna have to go with GPS
coordinates on this one. ;-)
> GPS is only
> accurate to a few hundred yards of course,
Uh, no. What are you smoking?
> so different people will
> probably get different measurements depending on where they're standing.
Sure. But you're not asking someone to identify a real intersection based on
their GPS coordinates. You're using it as a primary key.
None of your customers identifies themself with your internal ID number, either.
> Then again, most intersections probably already *have* a synthetic key.
> I don't know about your country, but around here major roads tend to
> have uniquely numbered markers on the roadside...
Assuming those don't change, they're probably based on geometry also. The
point of using GPS coordinates is that they are guaranteed not to change. If
you enter them in the database wrong, then you have bad data, but that's not
because the intersection moved.
>> The drawback of this is if you get a reference to the wrong value,
>> you'll never notice.
>
> Well, perhaps. But if somebody gives you the wrong phone number, you'll
> "never notice" in the same sense...
Except I'm not using a phone number as a primary key. :-)
(*) The 300 terabyte TURKS database. The size is probably bigger 15 years
later, too.
--
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
|
|
| |
| |
|
|
|
|
| |
| |
|
|
On 3/8/2011 4:43 PM, Darren New wrote:
> Tom Austin wrote:
>> Having unoptimized programs makes it very slow. Programs written as
>> fast as possible do not benefit from being efficient.
>
> Modern database systems are designed to take normalized tables and make
> accessing them very fast. That's the fundamental operation a relational
> database management system performs.
>
> How big is your total collection of active databases right now? If it
> fits in (say) a gigabyte, chances are you can't possibly make it too slow.
>
> If it *is* too slow when done "right", *then* you get out the query
> analyzer and look to see what it's spending its time doing, and you add
> the one index that cuts that time from minutes to miliseconds.
>
I think most of our slow downs is currently in the applications that
access it. Basically inefficient routines and use of objects. A
required approach when coding time as of the essence.
>> 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.
>
> You will need transactions. Trust me on this one. :-) If nothing else,
> you'll need transactions when you're archiving out an old job.
>
I've worked with transactions some in the past and they make a lot of
sense. I'll have to try to incorporate them as we move forward.
Does it make sense to set up a transaction wrapper on even simple single
updates?
> That said, it's surprisingly common that if you structure your queries
> right, you can make every operation essentially an "append to this
> table", followed by an eventual "update that table in a way that if it
> fails, we can reconstruct that table entirely from scratch". Think
> Journal -> General Ledger.
>
OK, I've been dealing with databases for 15 years now - on mostly a low
level. that just went right over my head. Makes sense, but I have no
concept of how it is implemented.
>
> That siad, btw, it's often the case that you *can* find text that won't
> change often enough to make a difference. Stock ticker, street name,
> radio station call letters. If it changes rarely enough that you're
> willing to treat it as an "delete all the old, insert all the new" sort
> of transaction, using text is OK. One generally doesn't do this with
> people, but saying "if you change your stock ticker, you have to treat
> it as selling all the old shares and getting the same number of new
> shares from the new ticker name" isn't too unreasonable.
>
for some reason I am hesitant to present the key as something that the
end user interacts with. Maybe I'm a control freak :-&
>> OK, you want a job surveying utility poles :-) You have good insight.
>
> I've just done this dozens of times in the last couple decades.
>
what - survey telephone poles?
>
> Look at it this way: This is the data that is vital to your company, in
> the strictest sense. Your company's job is to manage and evolve this
> data. You don't have time to get it wrong.
that is very correct. Our first stab was fast and mostly dirty. We
spent a lot of time to try to get the data right, but we missed some
pieces of data.
>
> What about your current system is so broken that you can't afford the
> time to fix it?
>
It has layers upon layers of fixes. The end users had to fight with it
to get it to put out data correctly. it was an ad-hoc rich system to
the point where I refused to make any more changes because of the risk
of data corruption/loss and our client getting crap for a deliverable.
There were some cool features that I implemented - like a GIS type
interface - before GIS systems were popular.
> The other possibility is to hire someone who actually does this sort of
> stuff for a living for long enough to show you how to set up the basics
> of the database.
>
It's a balance. Depending on our requirements it could take just as
much of our time working with the guy and doing it ourselves.
>>>> 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.
>
> That's the goal. As you get more sophisticated, you find that breaks
> down. When you use views in your "real" database and you archive it to
> something that has no views, you find yourself with a problem.
>
yes, one just has to pay attention and not set up things that break.
It could be that applications do not rely on the views (or other
non-common features) But then one could be crippling themselves.
> That said, even a cheap machine nowadays has more than enough processing
> power to do whole boatloads of database processing.
>
> About how much data do you have, in total and in active jobs? Many
> people seriously underestimate just how much processing even a $1000
> machine can do on a SQL database.
>
yes, I don't think it will take much of a machine. We have an old
server box (PIII 600 I think) that would likely do just that. Or I
might incorporate MySQL on our current file server (Linux) to simplify
things and use the old box for development.
>> 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.
>
> Be sure to factor in some ongoing maintenance time. Occasionally your
> replication will break, or something like that, and you'll have to fix
> stuff in a panic. :-)
>
yes, just like last week with out servers.
In the past 3 years I have had 1 or 2 incidents that required immediate
attention.
The past week brought no less than 5 of them.
From full OS drives to failed RAID mirrors.
Luckily none of them were damaging. Just annoying and cost a couple of
hours of everyone's time.
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
| |
|
|
Tom Austin wrote:
> I think most of our slow downs is currently in the applications that
> access it. Basically inefficient routines and use of objects. A
> required approach when coding time as of the essence.
Yeah, but now you hve some luxury to do it right, I'd wager, since you
already have a working system.
> Does it make sense to set up a transaction wrapper on even simple single
> updates?
If you're modifying more than one row at a time, yes. In most database
engines, each statement you submit becomes its own transaction. This isn't
true on all types of MySql tables. For example, if you have a MyISAM table
and you say "update blah set x = 50 where y = 3" and it gets interrupted
half way, you might update only half the rows where y = 3.
A single append of a single row is atomic. You're not going to corrupt the
database per se. Just the consistency of the data inside the database.
So, yes, there's really so little overhead in using a transaction around
anything that affects more than one row that it makes no sense not to do so.
>> That said, it's surprisingly common that if you structure your queries
>> right, you can make every operation essentially an "append to this
>> table", followed by an eventual "update that table in a way that if it
>> fails, we can reconstruct that table entirely from scratch". Think
>> Journal -> General Ledger.
>>
>
> OK, I've been dealing with databases for 15 years now - on mostly a low
> level. that just went right over my head. Makes sense, but I have no
> concept of how it is implemented.
I've usually done financial databases. So, basically, all the history of
everything in and out of the database is recorded in one set of tables,
while the current state is in a second set of tables.
Like your checkbook: Every check you write gets a row appended, and you
never change that row except to set a flag that you never unset (the "this
check cleared" flag). But there's another table that lists each account and
says how much money is in it. In theory, you can construct the latter from
the former, if you keep all your old checkbook registers around.
> for some reason I am hesitant to present the key as something that the
> end user interacts with. Maybe I'm a control freak :-&
No, there's almost never a reason to present the key that way. That doesn't
make it less useful as a key. :-)
>>> OK, you want a job surveying utility poles :-) You have good insight.
>> I've just done this dozens of times in the last couple decades.
> what - survey telephone poles?
No, I only did that once. :-) Design databases from scratch.
> that is very correct. Our first stab was fast and mostly dirty. We
> spent a lot of time to try to get the data right, but we missed some
> pieces of data.
That's not unusual. The trick is to have the database structured in a way
you can add the missed data later without *changing* what's already there.
The best way to do that is to primarily record into the database exactly
what's outside the database, reflecting reality as closely as possible.
>> What about your current system is so broken that you can't afford the
>> time to fix it?
>>
>
> It has layers upon layers of fixes. The end users had to fight with it
> to get it to put out data correctly. it was an ad-hoc rich system to
> the point where I refused to make any more changes because of the risk
> of data corruption/loss and our client getting crap for a deliverable.
Been there done that. Along with "before I start, has this ever been backed
up?" Answered by "No, it has never been reliable enough we could get a
backup of the data." Followed by "Thanks, but I like my current job enough."
>> The other possibility is to hire someone who actually does this sort of
>> stuff for a living for long enough to show you how to set up the basics
>> of the database.
>>
>
> It's a balance. Depending on our requirements it could take just as
> much of our time working with the guy and doing it ourselves.
This is true. I don't think it would take any less time, but you'd probably
have a more robust system when you were done.
> yes, one just has to pay attention and not set up things that break.
> It could be that applications do not rely on the views (or other
> non-common features) But then one could be crippling themselves.
Yes. It's better to just say "if we need multiple archives of this database,
we'll use the same engine."
> yes, I don't think it will take much of a machine. We have an old
> server box (PIII 600 I think) that would likely do just that. Or I
> might incorporate MySQL on our current file server (Linux) to simplify
> things and use the old box for development.
Yeah. And you'll likely want a replicated server as well.
> Luckily none of them were damaging. Just annoying and cost a couple of
> hours of everyone's time.
Yeah, that's much better than the place catching on fire. :-)
--
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
|
|
| |
| |
|
|
|
|
| |
| |
|
|
On 08/03/2011 10:34 PM, Darren New wrote:
> Orchid XP v8 wrote:
>> The assertion being that "stuff that doesn't change" is rarer than you
>> think it is.
>
> I'll grant that it's rarer than most people think it is. The trick is to
> consider whether if the PK changes, you're willing to treat the object
> as a new entity, or whether it's rare enough that you're willing to
> expunge the old ID entirely.
Seems reasonable. And I'm guessing that Mr Kyte was talking about huge
multi-TB data warehouses where updating or removing all references to
something would be a major PITA.
> I contend that most primary data in the database should be historical.
This also seems like a sensible way to go.
>> Until the GPS coordinates turn out to have been inaccurate.
>
> Then you fix it. There never *was* an intersection with those GPS
> coordinates. So you fix it. The point is to avoid collisions, not to
> necessarily have a PK that never changes.
The point is *totally* to have a PK that never changes, since changing a
PK is a tricky business which you want to avoid. (Duplicate PKs would of
course be much, much worse, but that's fairly easy to avoid.)
> Given that the BIGGEST TRANSACTIONAL DATABASE IN THE WORLD (*)
> identifies street intersections with GPS coordinates, I'm gonna have to
> go with GPS coordinates on this one. ;-)
OK, well I'm gonna have to say they must be doing something more precise
than "stand in the general vicinity of the intersection, take down the
GPS coordinates, and we'll just use those".
>> GPS is only accurate to a few hundred yards of course,
>
> Uh, no. What are you smoking?
Hey, the satellites are 20,000 km up in the sky. It's astonishing that
GPS can even figure out which city your in, never mind fixing your
location to a few hundred yards.
>> so different people will probably get different measurements depending
>> on where they're standing.
>
> Sure. But you're not asking someone to identify a real intersection
> based on their GPS coordinates. You're using it as a primary key.
So, what, you pick a GPS coordinate at random that's approximately in
the right area? (So long as its unambiguously not near any *other*
intersection.)
>> Then again, most intersections probably already *have* a synthetic
>> key. I don't know about your country, but around here major roads tend
>> to have uniquely numbered markers on the roadside...
>
> Assuming those don't change, they're probably based on geometry also.
They seem to just be numbered sequentially along the length of the road.
> The point of using GPS coordinates is that they are guaranteed not to
> change.
Sure. Unless the GPS coordinate system moves, which can't possibly
happen. Oh, wait...
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
| |
|
|
Le 09/03/2011 10:29, Invisible a écrit :
>
>>> GPS is only accurate to a few hundred yards of course,
>>
>> Uh, no. What are you smoking?
>
> Hey, the satellites are 20,000 km up in the sky. It's astonishing that
> GPS can even figure out which city your in, never mind fixing your
> location to a few hundred yards.
GPS computation is accurate, with military access, to less than a yard.
One of its military purpose is guiding rocket/missile between buildings,
with a flight near the ground as close as possible to avoid radar detection.
Civil usage of GPS is accurate to 30 yards/meters.
Some area are covered with DGPS, an additional signal on radio which
provided the offsets measured between the GPS signal and the actual
location of reference points (so that a civil application can compensate
the delta...)
Remember that GPS is operated by the army of the USA (or whatever you
name it), and they can switch it down whenever they want. (or rather,
keeping it on only for the happy fews with the unscrambling devices...)
--
Software is like dirt - it costs time and money to change it and move it
around.
Just because you can't see it, it doesn't weigh anything,
and you can't drill a hole in it and stick a rivet into it doesn't mean
it's free.
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
| |
|
|
> GPS computation is accurate, with military access, to less than a yard.
So I've heard. This is only useful if you're in the military. ;-)
> Civil usage of GPS is accurate to 30 yards/meters.
That sounds more like it.
> Remember that GPS is operated by the army of the USA (or whatever you
> name it), and they can switch it down whenever they want. (or rather,
> keeping it on only for the happy fews with the unscrambling devices...)
*Other global positioning systems are available. ;-)
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
| |
|
|
> GPS computation is accurate, with military access, to less than a yard.
> One of its military purpose is guiding rocket/missile between buildings,
> with a flight near the ground as close as possible to avoid radar detection.
>
> Civil usage of GPS is accurate to 30 yards/meters.
I thought they removed the civil "randomness" in the GPS signal ages ago?
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
| |
|
|
On 3/8/2011 4:24 PM, Darren New wrote:
> Orchid XP v8 wrote:
>> 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.
>
> I disagree. The only time you should use automatically-generated primary
> keys is when there is no attribute of the item in the table that is
> guaranteed not to change.
>
> A sales receipt whose primary key is the cash register identifier and
> the time to the second when the receipt was printed is an excellent PK
> for a sales receipt. A record recording what song was playing on the
> radio has the obvious PK of location+timestamp+frequency. A road
> intersection has a fine PK of the GPS coordinates of the intersection.
>
> The problem is that people are changing all the time, so it's very hard
> to have a customer record that persists with a PK that makes sense.
>
I have to agree with Darren - A primary key is not necessarily an
auto-generated value (random or auto-increment). It only has to be
unique and not change.
For me, the *easiest* way to guarantee no change is to have an
auto-generated integer. Can it be done better, yes. But without the
experience of how to look at data, I run too high of a chance that I
chose the wrong key and it needs changed.
>> 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.
>
> The drawback of this is if you get a reference to the wrong value,
> you'll never notice.
>
I see the point in having the key have more meaning. It reduces the
mental referencing that must go on to understand the data without a
proper interface.
>> I would suggest not setting up an Oracle database, for example. It
>> sounds like it would be utter overkill here.
>
> Yes. One of the free databases (Postgresql or mysql) or (on windows) SQL
> Server (Express, perhaps) would do fine. You'd probably find the most
> free help with MySql, but it's kind of messy to run on Windows, or at
> least was last I checked. SQL Server Express would *probably* have
> enough performance for you, given you were using Access.
>
I'm on this page fully. I prefer to stay away from MS database
solutions. Not that they are not good, but they are just too ... well
just 'too'.
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
| |
|
|
On 3/8/2011 5:03 PM, Orchid XP v8 wrote:
>
> Until the GPS coordinates turn out to have been inaccurate. GPS is only
> accurate to a few hundred yards of course, so different people will
> probably get different measurements depending on where they're standing.
> That's probably not a great idea. (Unless you explicitly round the
> coordinates up to some number that makes the size of the actual
> intersection negligable.)
>
I just ran across this where the GPS coordinate was wrong in a
government data set - likely due to a typo.
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
| |
|
|
Invisible wrote:
> The point is *totally* to have a PK that never changes,
Well, yes, that's the desire. But you can only achieve that by having
meaningless PKs on everything, which I find to be undesirable if a
meaningful PK can be found.
If your database is likely to be big enough that an incorrect initial PK
is
going to be catastrophic, then you can go for meaningless PKs.
However, in Tom's case, the GPS coordinates being wrong means *everything
*
is wrong for that pole, so changing the GPS coordinates might as well be
treated as deleting the pole in the wrong place and creating the pole in
the
right place.
> OK, well I'm gonna have to say they must be doing something more precis
e
> than "stand in the general vicinity of the intersection, take down the
> GPS coordinates, and we'll just use those".
I don't know.
>>> GPS is only accurate to a few hundred yards of course,
>>
>> Uh, no. What are you smoking?
>
> Hey, the satellites are 20,000 km up in the sky. It's astonishing that
> GPS can even figure out which city your in, never mind fixing your
> location to a few hundred yards.
"""
The phase difference error in the normal GPS amounts to 2–3 metre
s (6.6–9.8
ft) of ambiguity. CPGPS working to within 1% of perfect transition reduce
s
this error to 3 centimeters (1.2 in) of ambiguity. By eliminating this er
ror
source, CPGPS coupled with DGPS normally realizes between 20–30 c
entimetres
(7.9–12 in) of absolute accuracy.
[...]
However, 2 millimeter accuracy requires measuring the total phase—
the number
of waves times the wavelength plus the fractional wavelength, which requi
res
specially equipped receivers. This method has many surveying applications
.
"""
> So, what, you pick a GPS coordinate at random that's approximately in
> the right area? (So long as its unambiguously not near any *other*
> intersection.)
There's a certain level of error of a couple of meters. You pick a standa
rd
place to measure from (such as the center of the manhole that you're
interested in) and call that your location.
When you go back to find the intersection, are the GPS coordinates given
good enough that someone else can tell what intersection you're talking a
bout?
It should be noted, btw, that they're not really interested in the
intersections, but in the wires running under the roads. The same PK can
be
used to track the wires running under or over not-roads as well.
>> The point of using GPS coordinates is that they are guaranteed not to
>> change.
>
> Sure. Unless the GPS coordinate system moves, which can't possibly
> happen. Oh, wait...
Have latitude and longitude changed since we invented RDBMs? I don't thi
nk so.
--
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
|
|
| |
| |
|
|
|
|
| |
| |
|
|
Le_Forgeron wrote:
> Civil usage of GPS is accurate to 30 yards/meters.
I think they turned off the encryption of the low bits back in Clinton's
timeframe, when other countries started threatening to launch their own
satellites. I think civilian GPS is quite accurate now.
Especially if you're measuring something that isn't moving, and you can let
the device sit for a day, you can get down in the milimeter range.
--
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
|
|
| |
| |
|
|
|
|
| |
| |
|
|
On 3/8/2011 5:34 PM, Darren New wrote:
> Is it the same telephone pole if I dig it up and plant it somewhere
> else? Sure, maybe. But am I ever going to need to know that if my job
> involves routing cables? Probably not. Am I going to need to know that
> if my job involves replacing them when they wear out? Most definitely.
>
> How often do you replace the little tags on the side of the pole that
> give the pole number? Not very often. Is there any reason if that ID tag
> gets replaced that you can't just update in the database everyplace that
> appears? Probably not.
>
Poles move all of the time, but they are considered 'new' poles and not
related to the old poles :-) pole tags usually do not change, but if
the tag is missing on one day, it could have a new one the next. In
fact, the power companies here tag their poles according to geographical
coordinates - likely they use it as their primary key. But I have seen
some wrong tags that will likely get changed.
>> But sure, especially if you're recording historical data (which is of
>> course especially unlikely to change), there's no real need for a
>> synthetic key.
>
> I contend that most primary data in the database should be historical.
>
>> Until the GPS coordinates turn out to have been inaccurate.
>
> Then you fix it. There never *was* an intersection with those GPS
> coordinates. So you fix it. The point is to avoid collisions, not to
> necessarily have a PK that never changes.
>
> Given that the BIGGEST TRANSACTIONAL DATABASE IN THE WORLD (*)
> identifies street intersections with GPS coordinates, I'm gonna have to
> go with GPS coordinates on this one. ;-)
>
>
> Assuming those don't change, they're probably based on geometry also.
> The point of using GPS coordinates is that they are guaranteed not to
> change. If you enter them in the database wrong, then you have bad data,
> but that's not because the intersection moved.
>
I will stay out of this one.... I think I will stick with auto number
fields for now - just to keep my head on straight.
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
| |
|
|
On 3/8/2011 6:22 PM, Darren New wrote:
> Tom Austin wrote:
>> I think most of our slow downs is currently in the applications that
>> access it. Basically inefficient routines and use of objects. A
>> required approach when coding time as of the essence.
>
> Yeah, but now you hve some luxury to do it right, I'd wager, since you
> already have a working system.
>
Exactly - but just how much time we can allocate to it is yet to be
seen. But I think I can push for more time now because it will cost
much more time later. But this only works to a point.
>> Does it make sense to set up a transaction wrapper on even simple
>> single updates?
>
> If you're modifying more than one row at a time, yes. In most database
> engines, each statement you submit becomes its own transaction. This
> isn't true on all types of MySql tables. For example, if you have a
> MyISAM table and you say "update blah set x = 50 where y = 3" and it
> gets interrupted half way, you might update only half the rows where y = 3.
>
> A single append of a single row is atomic. You're not going to corrupt
> the database per se. Just the consistency of the data inside the database.
>
> So, yes, there's really so little overhead in using a transaction around
> anything that affects more than one row that it makes no sense not to do
> so.
>
Makes sense - on anything that affects more than 1 row in 1 table is OK.
Transact wrapper everything else :-)
>>> That said, it's surprisingly common that if you structure your queries
>>> right, you can make every operation essentially an "append to this
>>> table", followed by an eventual "update that table in a way that if it
>>> fails, we can reconstruct that table entirely from scratch". Think
>>> Journal -> General Ledger.
>>>
>>
>> OK, I've been dealing with databases for 15 years now - on mostly a
>> low level. that just went right over my head. Makes sense, but I have
>> no concept of how it is implemented.
>
> I've usually done financial databases. So, basically, all the history of
> everything in and out of the database is recorded in one set of tables,
> while the current state is in a second set of tables.
>
> Like your checkbook: Every check you write gets a row appended, and you
> never change that row except to set a flag that you never unset (the
> "this check cleared" flag). But there's another table that lists each
> account and says how much money is in it. In theory, you can construct
> the latter from the former, if you keep all your old checkbook registers
> around.
>
Ok, makes sense - if you keep a 'transaction' history. In the financial
world - highly recommended.
I don't think we will have to go that far.
Our bigger problem is getting the data that is entered to be correct.
Not just structurally correct, but actually correct. This has to do
with the interfaces that were assembled with too much haste.
>> for some reason I am hesitant to present the key as something that the
>> end user interacts with. Maybe I'm a control freak :-&
>
> No, there's almost never a reason to present the key that way. That
> doesn't make it less useful as a key. :-)
>
True. I believe that if the system is built right the user will NEVER
have to look at the raw data. The interfaces should give the user all
of the information that they need. And if they need to much with raw
data for some kind of reporting, the system can output some form of csv
that the user can manipulate at will.
The keys can be almost anything and it not change the functionality of
the database or how the user interacts with. it does make it somewhat
more readable for the person trying to figure out a problems that
somehow got into the data.
>>>> OK, you want a job surveying utility poles :-) You have good insight.
>>> I've just done this dozens of times in the last couple decades.
>> what - survey telephone poles?
>
> No, I only did that once. :-) Design databases from scratch.
>
>> that is very correct. Our first stab was fast and mostly dirty. We
>> spent a lot of time to try to get the data right, but we missed some
>> pieces of data.
>
> That's not unusual. The trick is to have the database structured in a
> way you can add the missed data later without *changing* what's already
> there. The best way to do that is to primarily record into the database
> exactly what's outside the database, reflecting reality as closely as
> possible.
>
Yes, agreed. Depending on how quickly the thing was built, it can be
very difficult if a complete aspect was missed. Also, one has to define
*what* and *how* base rules before adding the data or you get a mess -
which we are approaching.
>
>>> What about your current system is so broken that you can't afford the
>>> time to fix it?
>>>
>>
>> It has layers upon layers of fixes. The end users had to fight with it
>> to get it to put out data correctly. it was an ad-hoc rich system to
>> the point where I refused to make any more changes because of the risk
>> of data corruption/loss and our client getting crap for a deliverable.
>
> Been there done that. Along with "before I start, has this ever been
> backed up?" Answered by "No, it has never been reliable enough we could
> get a backup of the data." Followed by "Thanks, but I like my current
> job enough."
>
We are not quite that far :-) But it is nice to be starting with trying
to make a working system instead of forcing a broken one to continue to
work.
>>> The other possibility is to hire someone who actually does this sort of
>>> stuff for a living for long enough to show you how to set up the basics
>>> of the database.
>>>
>>
>> It's a balance. Depending on our requirements it could take just as
>> much of our time working with the guy and doing it ourselves.
>
> This is true. I don't think it would take any less time, but you'd
> probably have a more robust system when you were done.
>
very true - worth considering
>> yes, one just has to pay attention and not set up things that break.
>> It could be that applications do not rely on the views (or other
>> non-common features) But then one could be crippling themselves.
>
> Yes. It's better to just say "if we need multiple archives of this
> database, we'll use the same engine."
>
I just have to get this through to the rest of the guys.
>> yes, I don't think it will take much of a machine. We have an old
>> server box (PIII 600 I think) that would likely do just that. Or I
>> might incorporate MySQL on our current file server (Linux) to simplify
>> things and use the old box for development.
>
> Yeah. And you'll likely want a replicated server as well.
>
Not a bad idea - but that can get a bit more complicated. But I will
take it as a strong suggestion.
>> Luckily none of them were damaging. Just annoying and cost a couple of
>> hours of everyone's time.
>
> Yeah, that's much better than the place catching on fire. :-)
>
We do have off-site backups :-) Just 500GB in one big tarball tho :-(
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
| |
|
|
On 3/8/2011 4:31 PM, Darren New wrote:
> The absolute best way to make this work out, then, is to record
> "reality" in the database. Ignore how you're going to use the data while
> you're designing what's in the database (to the extent possible).
> Instead, simply record facts.
>
> What I'm trying to say is don't say "our calculations would be easier if
> our data was arranged like *this*." If you stick with the reality of
> what's out there, you will never run into the situation where you have
> to *change* data you already have in the database.
>
> I know this sounds kind of vague, and it can be hard to get away from it
> if you already have a model of something up and running and you know how
> that works.
>
I am realizing this - I believe that our current model is not yet too
bad in this regard. It basically reflects reality. We just need to
lock it down a bit more.
Your tips here will help us move forward more quickly. We do get mired
down in how to make the data usable.
I do need to consider usability somewhat to make sure that the data that
is needed makes it into the system.
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
| |
|
|
Tom Austin wrote:
> I will stay out of this one.... I think I will stick with auto number
> fields for now - just to keep my head on straight.
There's nothing *wrong* with it. It's just that many libraries assume that
every single table has an auto-increment ID, which I think is the wrong way
to go, simplifying the library at the expense of accurate data.
--
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
|
|
| |
| |
|
|
|
|
| |
| |
|
|
Tom Austin wrote:
> I'm on this page fully. I prefer to stay away from MS database
> solutions. Not that they are not good, but they are just too ... well
> just 'too'.
I bring it up only because you're running Windows there, based on your
comments about using Access. I don't know how well MySql and Postgressql
have been ported to Windows.
--
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
|
|
| |
| |
|
|
|
|
| |
| |
|
|
Tom Austin wrote:
> Exactly - but just how much time we can allocate to it is yet to be
> seen. But I think I can push for more time now because it will cost
> much more time later. But this only works to a point.
Naturally. I'm just recommending that you make it clear that any benefit you
spend time on now will reap rewards over and over into the future.
> Makes sense - on anything that affects more than 1 row in 1 table is OK.
> Transact wrapper everything else :-)
Well, other way around, yes. Anything that affects more than one row goes
into a transaction.
> Our bigger problem is getting the data that is entered to be correct.
> Not just structurally correct, but actually correct. This has to do
> with the interfaces that were assembled with too much haste.
That's always a bugger, yes. :-)
> True. I believe that if the system is built right the user will NEVER
> have to look at the raw data. The interfaces should give the user all
> of the information that they need.
Yep. And if you really get into it, you wind up making views for each
function of your application, so the applications themselves don't need to
know how the raw data is organized. But that's kind of overkill for a small
database.
> The keys can be almost anything and it not change the functionality of
> the database or how the user interacts with. it does make it somewhat
> more readable for the person trying to figure out a problems that
> somehow got into the data.
Right. For one thing, for example, it keeps you from having (say) the same
pole entered multiple times in the database by mistake. If a pole is
identified by the GPS coords (assuming these are precise enough) it keeps
the database from having three records for a pole all at the same location.
> Yes, agreed. Depending on how quickly the thing was built, it can be
> very difficult if a complete aspect was missed.
No, it's not really that hard to add a complete new aspect to the database
after the fact, if what exists there is well thought out. You just have to
avoid putting multiple things in the same table that aren't really the same
thing in reality; like, avoid putting owner contact info into a table of
poles, or even ownership information into a table of poles.
> Also, one has to define
> *what* and *how* base rules before adding the data or you get a mess -
> which we are approaching.
Yep. That's the "C" in ACID. Way too many people who never actually studied
the topic think that the "C" means an error updating a row will corrupt the
database files. No, it means an error updating a row will corrupt the
*data*, not the container.
>>> yes, one just has to pay attention and not set up things that break.
>>> It could be that applications do not rely on the views (or other
>>> non-common features) But then one could be crippling themselves.
>>
>> Yes. It's better to just say "if we need multiple archives of this
>> database, we'll use the same engine."
>>
>
> I just have to get this through to the rest of the guys.
Or, alternately, accept that you'll use the same engine, and then make plans
for how to replace its features. Like, "we'll use views, but if we wind up
exporting to CVS, we'll export both the raw data and the views, which is OK
since we won't be updating historical data that we've already exported."
> Not a bad idea - but that can get a bit more complicated. But I will
> take it as a strong suggestion.
MySql makes it pretty easy, actually. Just follow the instructions.
Basically, you set up two machines with identical configurations of the SQL
server. Then you tell one "Hey, you're a master", using the tool that takes
a snapshot of the database and turns on the master flag at the same time.
Then you restore that snapshot to the slave, then tell the slave "Hey, your
master is over there!"
As long as you don't update the slave in a way that makes a transaction
applied to the master fail on the slave, you're good to go. If you
accidentally insert a record into the slave in a table that has an
auto-increment integer, the slave will crash out when the master tries to
insert the record with the same key, and you'll have to re-do that
backup/restore process.
> We do have off-site backups :-) Just 500GB in one big tarball tho :-(
Yah. Your actual data is 500G? That's actually a pretty large database.
--
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
|
|
| |
| |
|
|
|
|
| |
| |
|
|
On 3/8/2011 5:11 PM, Darren New wrote:
> Trust me on this: It's the only way you ever get a schema you don't have
> to change with every software update. Record what is "out there", and to
>
> I am looking at a financial system right now where I never overwrite
> data. When someone swipes a card, I put into a table exactly what the
>
> Why? Because the merchant might change merchant accounts before telling
> us. The cardholder might lose the card. Heck, we might even accidentally
> create two cards with the same number.
>
> I'm blathering. Nevermind.
>
you can blather - it is useful info
> Anyway, yes, record everything from *outside* your company in one set of
> tables, as *raw* as you can. Changes to these tables should be
> idempotent. (I.e., you *should* be able to read the CSV file the telco
> gave you listing the pole information multiple times into the same table
> without corruption. This implies no automatically-generated primary key
> on those tables. That said, again, this is just a guideline and not
> always possible.)
>
I see - the primary key thing should prevent the 'error'.
When you state multiple times, you are implying that multiple records
are NOT being created - just 'updating' existing records after the first
read.
>
>> this might be worth considering, but I don't know how easy it will be
>> to implement and keep straight. Seems like the a user created key like
>> this could lead to duplicates - but if it is a key it will prevent
>> duplicates anyway - just prompt for another key.
>
> Oh, yes, this isn't for user keys. I meant where in a programming
> language you'd use an enumerated type.
>
> So if you have a "status" row for jobs that says "in negotiation", "paid
> for but waiting for someone to be assigned", "that person is working on
> it", and "ready to be delivered", you could use this sort of coding to
> make your programs more readable. "NEGO", "PAID", "WORK", "DONE" rather
> than 0, 1, 2, 3.
>
yes, enumerations are very friendly - I avoid using plain ol integers
for this stuff - makes it hard to read
>
> It's hard coming up with that sort of thing when I don't know how your
> business works, tho, but that's the approximate sort of thing I was
> talking about.
>
Yes, there is more to it, but you do a good job of visualizing it.
>
>
> Oh, so the shortcoming is that it was rushed and grew instead of being
> designed. Makes sense. :-)
>
pretty much - the owner didn't want to invest the time to make it better
and gain the rewards. new owner - new ideas
>> Putting data in and getting data out was very difficult and required
>> the sacrifice of too many brain cells.
>
> That's exactly where SQL queries between multiple tables will help.
> Thinking in SQL instead of in terms of iterating over things is tough,
> but it's almost always possible to come up with a statement in SQL that
> will pull out all and only the data you need. If you ever get to the
> point where you're saying "for each result from *this* query, submit
> *that* query", you're almost 100% sure you're doing something wrong. In
> my entire career, I only ever did that once, and that was after I'd
> written it the right way and figured out MySql was not optimizing things
> in the obvious way and I didn't know how to make it do so.
>
I think I have run into this already
select A from b where A.ID in ( select ID from C)
we got pretty complicated with poles, connections between poles, cables
on poles and cables between poles.... a little intertwined
in its simplest form leaving out fluff like owners and such:
table poles: (each pole)
ID, GPS Location
1 entry for each pole
table connections: (how poles are connected)
ID, PoleID1, PoleID2, Distance
1 entry for each pole-pole joining (1 for each 'set' of cables)
table attachments: (cable attached to poles & height)
ID, PoleID, Height
multiple entries per pole
1 for each attachment
table midspans: (lowest height between poles for each cable)
ID, AttachmentID1, AttachmentID2, Height, ConnectionID(redundant)
multiple midspans for each 'connection'
1 midspan per cable over the connection
essentially 1 midspan for each connection on a pair of poles
The rest is pretty much straight forward - objects and properties
associated with each of the above.
As you can see, there are some circular references going on (attachments
and midspans connect poles
>
> Let me know if the description of tables above does or doesn't make sense.
>
Makes perfect sense.
To clarify my situation - I tried to present some questions that we have
in our office without bias to get a true feel of what constitutes good
and bad practice.
What you have presented falls in line with what I was thinking - but I
didn't want to present it as "is it stupid to flatten" so that I would
not bias the question.
You have presented some new ideas that 'extend' my thinking and stretch
it. I do not know how much will make it in to the system, but it is
good food for thought.
We have some views in our group that state that the use of the data
should drive how it is stored. I agree with it to a point - mainly for
ease of working with the data, but not at the sacrifice of usability of
the data. The drive is so hard that there is talk about flattening the
database to make it 'simpler' and that adding more tables makes it more
complex.
Additionally some of our issue is that when we finish a job for a client
we do not need to reference the data any more. Rarely have we found
ourselves going back to old data. How much effort should be put into a
system that supports saving everything and making it accessible when one
does not refer back to it?
Thanks for your feedback - it is very helpful.
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
| |
|
|
On 3/9/2011 12:11 PM, Darren New wrote:
> Le_Forgeron wrote:
>> Civil usage of GPS is accurate to 30 yards/meters.
>
> I think they turned off the encryption of the low bits back in Clinton's
> timeframe, when other countries started threatening to launch their own
> satellites. I think civilian GPS is quite accurate now.
>
> Especially if you're measuring something that isn't moving, and you can
> let the device sit for a day, you can get down in the milimeter range.
>
Atmospheric drift still plays a factor. You can reference to a known
nearby location that is receiving GPS signals to help nullify the effect
as the 'location' will drift as well.
take a look at Wide Area Augmentation System
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
| |
|
|
On 3/9/2011 12:10 PM, Darren New wrote:
> Invisible wrote:
>> The point is *totally* to have a PK that never changes,
>
> Well, yes, that's the desire. But you can only achieve that by having
> meaningless PKs on everything, which I find to be undesirable if a
> meaningful PK can be found.
>
> If your database is likely to be big enough that an incorrect initial PK
> is going to be catastrophic, then you can go for meaningless PKs.
>
> However, in Tom's case, the GPS coordinates being wrong means
> *everything* is wrong for that pole, so changing the GPS coordinates
> might as well be treated as deleting the pole in the wrong place and
> creating the pole in the right place.
>
Not exactly - we at times have to 'adjust' the coords to achieve better
accuracy once they are int he system. It sucks that after you get a lot
of data your client gives you the 'revised' requirements that cause such
changes.
>> OK, well I'm gonna have to say they must be doing something more
>> precise than "stand in the general vicinity of the intersection, take
>> down the GPS coordinates, and we'll just use those".
>
> I don't know.
>
look at aerial imagery - see pole - that's the GPS. Better than
anything else we have. GPS units are too slow to get good numbers that
close.
>
> There's a certain level of error of a couple of meters. You pick a
> standard place to measure from (such as the center of the manhole that
> you're interested in) and call that your location.
>
> When you go back to find the intersection, are the GPS coordinates given
> good enough that someone else can tell what intersection you're talking
> about?
>
> It should be noted, btw, that they're not really interested in the
> intersections, but in the wires running under the roads. The same PK can
> be used to track the wires running under or over not-roads as well.
>
We have had clients require specs that someone in an office without
experience must have come up with. Must be withing xx accuracy - must
add up to +- xx difference. Possible, but very expensive to actually
meet the requirements.
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
| |
|
|
On 3/9/2011 1:30 PM, Darren New wrote:
> Tom Austin wrote:
>> I will stay out of this one.... I think I will stick with auto number
>> fields for now - just to keep my head on straight.
>
> There's nothing *wrong* with it. It's just that many libraries assume
> that every single table has an auto-increment ID, which I think is the
> wrong way to go, simplifying the library at the expense of accurate data.
>
Yep, nothing wrong with it, but not required. I think it might be that
it is a 'simple' way of thinking about keys and making them happen. To
step out of that box requires more creative thinking and understanding
of the data than most people enjoy.
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
| |
|
|
Tom Austin wrote:
> Not exactly - we at times have to 'adjust' the coords to achieve better
> accuracy once they are int he system. It sucks that after you get a lot
> of data your client gives you the 'revised' requirements that cause such
> changes.
So the GPS is *not* something that uniquely identifies the pole, or the
customer wouldn't be able to tell you it was wrong. :-) So that shouldn't be
the PK in this case.
> look at aerial imagery - see pole - that's the GPS.
That's another way to do it. :-)
> We have had clients require specs that someone in an office without
> experience must have come up with. Must be withing xx accuracy - must
> add up to +- xx difference. Possible, but very expensive to actually
> meet the requirements.
Yeah. When it comes time to mark the roads, they still send someone out with
a metal detector and a can of spray paint.
--
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
|
|
| |
| |
|
|
|
|
| |
| |
|
|
On 3/9/2011 1:32 PM, Darren New wrote:
> Tom Austin wrote:
>> I'm on this page fully. I prefer to stay away from MS database
>> solutions. Not that they are not good, but they are just too ... well
>> just 'too'.
>
> I bring it up only because you're running Windows there, based on your
> comments about using Access. I don't know how well MySql and Postgressql
> have been ported to Windows.
>
Actually we run a mixed bag.
Windows desktops (Autocad and other crap)
Windows domain server (grandfathered in)
Linux from scratch file server (very cool and flexible)
A database server will virtually be 100% linux based.
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
| |
|
|
On 3/9/2011 1:43 PM, Darren New wrote:
>
> Naturally. I'm just recommending that you make it clear that any benefit
> you spend time on now will reap rewards over and over into the future.
>
The problem is that we have to get jobs out for the client now and might
not physically have the time to do it right.
>> Makes sense - on anything that affects more than 1 row in 1 table is
>> OK. Transact wrapper everything else :-)
>
> Well, other way around, yes. Anything that affects more than one row
> goes into a transaction.
>
yes - great application of 'not' on a sentence :-)
>> True. I believe that if the system is built right the user will NEVER
>> have to look at the raw data. The interfaces should give the user all
>> of the information that they need.
>
> Yep. And if you really get into it, you wind up making views for each
> function of your application, so the applications themselves don't need
> to know how the raw data is organized. But that's kind of overkill for a
> small database.
>
Yes, but makes the application have to perform less and changes where
the work is done.
> Right. For one thing, for example, it keeps you from having (say) the
> same pole entered multiple times in the database by mistake. If a pole
> is identified by the GPS coords (assuming these are precise enough) it
> keeps the database from having three records for a pole all at the same
> location.
>
That works if the GPS location used in each instance is exactly the same
- get off by a bit and you have a duplicate pole 1' away from the
existing pole. But a good interface could check for that and prevent it
from happening.
>> Yes, agreed. Depending on how quickly the thing was built, it can be
>> very difficult if a complete aspect was missed.
>
> No, it's not really that hard to add a complete new aspect to the
> database after the fact, if what exists there is well thought out. You
> just have to avoid putting multiple things in the same table that aren't
> really the same thing in reality; like, avoid putting owner contact info
> into a table of poles, or even ownership information into a table of poles.
>
Yes, you just can add another 'property'. But not if you miss a
fundamental requirement that alters the data you already have organized.
>> Also, one has to define *what* and *how* base rules before adding the
>> data or you get a mess - which we are approaching.
>
> Yep. That's the "C" in ACID. Way too many people who never actually
> studied the topic think that the "C" means an error updating a row will
> corrupt the database files. No, it means an error updating a row will
> corrupt the *data*, not the container.
>
Yes, but wrong direction.
I was referring to the situation where a piece of data was not accounted
for in the database. You must define ground rules for the database so
you can define the piece of data and know where it actually should get
stored. We have been lacking in such definition.
>
> Or, alternately, accept that you'll use the same engine, and then make
> plans for how to replace its features. Like, "we'll use views, but if we
> wind up exporting to CVS, we'll export both the raw data and the views,
> which is OK since we won't be updating historical data that we've
> already exported."
>
yes, several alternatives can achieve the same outcome. Our group
sometimes has trouble seeing that. And the solution provided can cause
many many problems.
> MySql makes it pretty easy, actually. Just follow the instructions.
>
> Basically, you set up two machines with identical configurations of the
> SQL server. Then you tell one "Hey, you're a master", using the tool
> that takes a snapshot of the database and turns on the master flag at
> the same time. Then you restore that snapshot to the slave, then tell
> the slave "Hey, your master is over there!"
>
> As long as you don't update the slave in a way that makes a transaction
> applied to the master fail on the slave, you're good to go. If you
> accidentally insert a record into the slave in a table that has an
> auto-increment integer, the slave will crash out when the master tries
> to insert the record with the same key, and you'll have to re-do that
> backup/restore process.
>
>> We do have off-site backups :-) Just 500GB in one big tarball tho :-(
>
> Yah. Your actual data is 500G? That's actually a pretty large database.
>
No, not a single database. We take many pictures and produce some
pretty large 3D pointclouds for some of the work. Each job has a .mdb
database file, but that is pretty small.
Here's a question:
We have pictures (sometimes more than one) of each pole. We do have
some applications that require that the image be in a file format and
not be fed from a stream.
What is the practicality of storing the images in the DB as a field
(blob or something else)?
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
| |
|
|
On 3/9/2011 2:27 PM, Darren New wrote:
> Tom Austin wrote:
>> Not exactly - we at times have to 'adjust' the coords to achieve
>> better accuracy once they are int he system. It sucks that after you
>> get a lot of data your client gives you the 'revised' requirements
>> that cause such changes.
>
> So the GPS is *not* something that uniquely identifies the pole, or the
> customer wouldn't be able to tell you it was wrong. :-) So that
> shouldn't be the PK in this case.
>
>> look at aerial imagery - see pole - that's the GPS.
>
> That's another way to do it. :-)
>
>> We have had clients require specs that someone in an office without
>> experience must have come up with. Must be withing xx accuracy - must
>> add up to +- xx difference. Possible, but very expensive to actually
>> meet the requirements.
>
> Yeah. When it comes time to mark the roads, they still send someone out
> with a metal detector and a can of spray paint.
>
I've used dowsing rods for the task before :-)
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
| |
|
|
Tom Austin wrote:
> I see - the primary key thing should prevent the 'error'.
Yeah. OK, imagine a database of oh receipts, say.
If the primary key is some arbitrary integer, it's possible to get the same
receipt into the table more than once, which will screw up your accounting.
If the primary key is the cash register number + timestamp to the second,
the only way you get a collision is to make two sales on the same register
within one second of each other, which probably *should* throw some sort of
error. It also makes it easy to print on the paper receipt all you need to
know to get the exact record.
Whether you can do this sort of thing with any particular table of yours is
another question.
> When you state multiple times, you are implying that multiple records
> are NOT being created - just 'updating' existing records after the first
> read.
Or not updating them at all, if you pass in the same data twice by mistake.
Look at a CSV file full of receipts like above. Reading each row is going to
clobber the record already there. If you use an auto-increment ID, you'll
wind up with 200 reciepts if you read a 100-row CSV in twice.
IME, a large part of database fuckage is either operator error (like loading
stuff twice) or simple programming errors (like debiting everyone's account
instead of just the account of the person you should have debited). The idea
of making idempotent updates and keeping write-once historical records came
about because that makes these things easy to fix.
> pretty much - the owner didn't want to invest the time to make it better
> and gain the rewards. new owner - new ideas
Cool. Let's hope it stays that way. :-)
> I think I have run into this already
> select A from b where A.ID in ( select ID from C)
No, that's cool. Well, not that exact query, but that sort of thing. The
trick is it's all in SQL. You aren't looping inside the PHP code or
Javascript code or whatever you're using to submit that code to the database
engine.
Since it's all in SQL, the SQL engine can optimize that.
> we got pretty complicated with poles, connections between poles, cables
> on poles and cables between poles.... a little intertwined
Oh, I can imagine. :-) But there are actually formal mathematical processes
and rules for designing a database like that in a way that makes it easier.
> in its simplest form leaving out fluff like owners and such:
>
> table poles: (each pole)
> ID, GPS Location
> 1 entry for each pole
>
> table connections: (how poles are connected)
> ID, PoleID1, PoleID2, Distance
> 1 entry for each pole-pole joining (1 for each 'set' of cables)
See, this is what I mean. There's no need for an ID on this table. You don't
want two rows, one where it's
(27, Pole 3, Pole 4, 500 feet)
(29, Pole 3, Pole 4, 800 feet)
You get that, your data is screwed, and you know it, but you can't fix it.
A connection between two poles is defined by the two poles it's connecting.
The distance and what types of cables go through are dependent data.
table connections:
PoleID1, PoleID2, Distance (pk=PoleID1+PokeID2)
table cables:
PoleID1, PoleID2, height, cable type. (pk=PoleID1+PoleID2+cable type)
> table attachments: (cable attached to poles & height)
> ID, PoleID, Height
> multiple entries per pole
> 1 for each attachment
Is there a row in this table for a pole that has no cables attached? I.e.,
does this represent "Cables attached to this pole" or "places where it's
posslbe to attach a cable"?
> table midspans: (lowest height between poles for each cable)
> ID, AttachmentID1, AttachmentID2, Height, ConnectionID(redundant)
> multiple midspans for each 'connection'
> 1 midspan per cable over the connection
> essentially 1 midspan for each connection on a pair of poles
Consider a slightly different layout: a span.
Cable type, poleID1, attachmenthieght1, poleID2, attachmentheight2,
midspanheight.
Then poleid1+poleid2+cabletype becomes your primary key.
That gives you poles, distances between poles, and spans of cable. If you
need to know connectivity, you could have a table that lists the first pole
and last pole along with a collection of spans.
I'm not saying you should do it this way. I'm just pointing out there are
lots of ways to organize the data, and you might want to think of a drastic
simplification in the structure that will still give you everything you need
with a bit of SQL processing time.
What you want is a structure where it becomes impossible to have only some
of the information you need. Just like you couldn't have a GPS location
without a pole, you don't want to have (say) a connection height on a pole
without a mispan, or a connection height on one pole but not on the other pole.
> The rest is pretty much straight forward - objects and properties
> associated with each of the above.
>
> As you can see, there are some circular references going on (attachments
> and midspans connect poles
Yeah. That means you're doing it wrong, if it's actually *circular*.
It's often easier to draw stuff as pictures on paper when you're designing
things.
Draw a box for each table, and an arrow each time there's a foreign key.
Any box with no outgoing arrows is a fundamental real-world entity. In this
case, for example, the poles better be in the real world, because they're
not attached to anything else.
> You have presented some new ideas that 'extend' my thinking and stretch
> it. I do not know how much will make it in to the system, but it is
> good food for thought.
It takes practice. I'm just trying to explain how I initially approach
things, and why. Of course each situation varies.
> We have some views in our group that state that the use of the data
> should drive how it is stored.
This is exactly the *opposite* of what an RDBMS is all about. People already
had this kind of database, and the problem was that the data became unusable
when new requirements came up. That's why SQL has no pointers.
> I agree with it to a point - mainly for
> ease of working with the data, but not at the sacrifice of usability of
> the data. The drive is so hard that there is talk about flattening the
> database to make it 'simpler' and that adding more tables makes it more
> complex.
Add lots of tables. Create "simpler" tables by creating views. When you find
in six months that you need new functionality, create other views that
expose the data organized in the new way.
> Additionally some of our issue is that when we finish a job for a client
> we do not need to reference the data any more. Rarely have we found
> ourselves going back to old data. How much effort should be put into a
> system that supports saving everything and making it accessible when one
> does not refer back to it?
Another good reason to avoid using auto-increment IDs. :-) You can have a
"job" table that gives you top-level pointers for a job to each thing
involved in a job. (I.e., if a Job consisted of a bunch of connections on a
bunch of poles, you'd have a Job ID and a collection of poles. The
collection of poles would give you the collection of cables. Etc.) You could
then write out (as SQL even) all the rows associated with a given job, then
delete those rows. If you ever needed them back, run that SQL back into the
database. (Altho, honestly, most database engines with autoincrement IDs
can handle actually inserting records with a given ID and not generating a
new one.)
But honestly, I don't know how big your database is, but a database can
store millions of rows before you even start to see anything getting slow.
It's not a case of "do we need to keep it around" as it is "do we need to
throw it away?" But if you manage to build the data in a way that each
chunk you might want to archive (i.e., a job) has a record that lets you get
the whole collection of everything associated with that record, then you
don't have to worry about the actual archive-and-delete part of the code
until there's a reason to. Then you can write that code, confident that you
know what you need to store out.
> Thanks for your feedback - it is very helpful.
I like helping,b ecause writing the thoughts out clearly enough to explain
to someone else always clarifies things to me too.
--
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
|
|
| |
| |
|
|
|
|
| |
| |
|
|
Tom Austin wrote:
> A database server will virtually be 100% linux based.
Ah. MySql or Postrgresql might be the way to go, then, obviously. You'd have
to do the research on how to connect the Linux server to the Windows desktop
software, then. I'm not sure I've done that recently enough for any advice
I'd give to be valid.
--
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
|
|
| |
| |
|
|
|
|
| |
| |
|
|
Tom Austin wrote:
> The problem is that we have to get jobs out for the client now and might
> not physically have the time to do it right.
I grok. Why can't you use the old system, tho.
> Yes, but makes the application have to perform less and changes where
> the work is done.
The real advantage is you can change what's stored underneath (like, say,
changing the primary keys, or changing whether you talk about "attachments"
or "spans") without updating the application. You rearrange the database,
rewrite the views, but all your calculation code etc stays the same.
> That works if the GPS location used in each instance is exactly the same
> - get off by a bit and you have a duplicate pole 1' away from the
> existing pole. But a good interface could check for that and prevent it
> from happening.
Right. That was the "precise enough" bit. :-)
> Yes, you just can add another 'property'. But not if you miss a
> fundamental requirement that alters the data you already have organized.
The advantage of basing the schema on the "real world" is that if you miss a
fundamental property, it's because the data isn't available.
> I was referring to the situation where a piece of data was not accounted
> for in the database. You must define ground rules for the database so
> you can define the piece of data and know where it actually should get
> stored. We have been lacking in such definition.
Well, yes, it's hard to store data you don't know about. :-)
I was simply talking about the addition of new types of data after you
already have the existing data defined.
> yes, several alternatives can achieve the same outcome. Our group
> sometimes has trouble seeing that.
Ah, welcome to the club. ;-) That's why I like being boss.
> What is the practicality of storing the images in the DB as a field
> (blob or something else)?
Well, it'll certainly make things slow. If you want to store them in a
database rather than the file system, *this* is a perfect use case for
having a second database. :-) Make them blobs in the second database, and
refer to them from the first database.
The benefits of doing this are that you get easy access to the pictures from
the same place you have any of the other data (i.e., you don't have to
figure out how to set up network shares, your login information can control
access to the pictures, etc.). But you also have the advantage that you
don't have to back up the database as often, you can put it on a different
partition or a different server, etc. (For example, say the customer FTPs
the pictures to you. You could pull the pictures out of the incoming
directory and put them into the PolePicture blob database when you created
the pole record in the primary database that references the picture. Then
you could leave the pictures on the FTP server until you back up the blob
database at the end of the week/month/whatever, whereas you'd probably want
to be replicating the actual transactional database live.)
So generally that's what I'd recommend. Be sure to write the code to fetch
the pictures modularly, so you can move it to an entirely different server
with an entirely different connection string if it gets too big or something.
And read up on the technical details of how blobs are handled in storage, so
you don't wind up configuring yourself into a corner or something. :-)
--
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
|
|
| |
| |
|
|
|
|
| |
| |
|
|
Invisible wrote:
> The point is *totally* to have a PK that never changes, since changing a
> PK is a tricky business which you want to avoid. (Duplicate PKs would of
> course be much, much worse, but that's fairly easy to avoid.)
Actually, I think this is backwards. The trick would be to pick a PK that
never changes because it actually uniquely identifies the entity the record
represents (which, as I have said, is not always possible). The problem is
not to avoid duplicate PKs, but to avoid two records with different PKs
representing the same thing. If you already have a "unique index" on a table
for fields you don't anticipate changing, that can easily serve as the PK
for that table without having yet another arbitrary identifier.
By giving each record a PK of some random number, you have eliminated the
ability to distinguish duplicates. Actually updating a PK on a record, while
possibly time-consuming, shouldn't be problematic from a theoretical point
of view. That's precisely what foreign keys, cascading updates, and
cascading deletes are for.
--
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
|
|
| |
| |
|
|
|
|
| |
| |
|
|
On 3/9/2011 3:00 PM, Darren New wrote:
> Tom Austin wrote:
>> A database server will virtually be 100% linux based.
>
> Ah. MySql or Postrgresql might be the way to go, then, obviously. You'd
> have to do the research on how to connect the Linux server to the
> Windows desktop software, then. I'm not sure I've done that recently
> enough for any advice I'd give to be valid.
>
I'm actually not too afraid on how to do that. Shouldn't be much
different than for a SQL Server or anything else. Just have to get it
set up and then you can run.
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
| |
|
|
On 3/9/2011 3:14 PM, Darren New wrote:
> Tom Austin wrote:
>> The problem is that we have to get jobs out for the client now and
>> might not physically have the time to do it right.
>
> I grok. Why can't you use the old system, tho.
>
A desire to just get rid of it and not try to morph it.
It is one of those things - until you grasp how broken it was it doesn't
seem logical.
>> Yes, but makes the application have to perform less and changes where
>> the work is done.
>
> The real advantage is you can change what's stored underneath (like,
> say, changing the primary keys, or changing whether you talk about
> "attachments" or "spans") without updating the application. You
> rearrange the database, rewrite the views, but all your calculation code
> etc stays the same.
>
that sounds like fun :-)
I like the idea - now to try to sell it....
>> I was referring to the situation where a piece of data was not
>> accounted for in the database. You must define ground rules for the
>> database so you can define the piece of data and know where it
>> actually should get stored. We have been lacking in such definition.
>
> Well, yes, it's hard to store data you don't know about. :-)
>
> I was simply talking about the addition of new types of data after you
> already have the existing data defined.
>
yes, that can work well. But it good to have rules as to how to define
the new data so that the db is not peppered with ad-hocs.
Like - does this belong with make ready or the current state of the pole....
>
> Ah, welcome to the club. ;-) That's why I like being boss.
>
can be difficult if you someone in the group has a strong personality.
>> What is the practicality of storing the images in the DB as a field
>> (blob or something else)?
>
> Well, it'll certainly make things slow. If you want to store them in a
> database rather than the file system, *this* is a perfect use case for
> having a second database. :-) Make them blobs in the second database,
> and refer to them from the first database.
>
> The benefits of doing this are that you get easy access to the pictures
> from the same place you have any of the other data (i.e., you don't have
> to figure out how to set up network shares, your login information can
> control access to the pictures, etc.). But you also have the advantage
> that you don't have to back up the database as often, you can put it on
> a different partition or a different server, etc. (For example, say the
> customer FTPs the pictures to you. You could pull the pictures out of
> the incoming directory and put them into the PolePicture blob database
> when you created the pole record in the primary database that references
> the picture. Then you could leave the pictures on the FTP server until
> you back up the blob database at the end of the week/month/whatever,
> whereas you'd probably want to be replicating the actual transactional
> database live.)
>
> So generally that's what I'd recommend. Be sure to write the code to
> fetch the pictures modularly, so you can move it to an entirely
> different server with an entirely different connection string if it gets
> too big or something.
>
> And read up on the technical details of how blobs are handled in
> storage, so you don't wind up configuring yourself into a corner or
> something. :-)
>
Thanks for the tips - for the image files I don't know if it will go to
blobs or stay files simply because of making working with the files more
complex. But leaving the files as is adds complexity as well. <sigh>
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
| |
|
|
Tom Austin wrote:
> On 3/9/2011 3:14 PM, Darren New wrote:
>> Tom Austin wrote:
>>> The problem is that we have to get jobs out for the client now and
>>> might not physically have the time to do it right.
>>
>> I grok. Why can't you use the old system, tho.
>>
>
> A desire to just get rid of it and not try to morph it.
> It is one of those things - until you grasp how broken it was it doesn't
> seem logical.
No, I meant your business is going to be making money running the old system
while you're implementing the new system. Why is there a hurry beyond "we're
spending money on peoples time to build the new system"? If you turned the
old system off before the new system was ready, I can see the hurry.
Doing it right shouldn't take a whole lot longer than doing it wrong.
> Like - does this belong with make ready or the current state of the
> pole....
Right. You do have to watch out for that sort of thing, yes. But again, fall
back to the concept that each row (in a "primary" table) represents a real
entity. If it's something artificial that is related to but not *really* a
part of the thing the row represents, it should go in a separate table keyed
to the main table.
Like, if you have a bunch of poles, and you want to know where to put a date
at which the utility thinks it'll finish installing the cabling, that's not
part of the utility *or* part of the pole. So you can make a new table
"ExpectedInstall" that has nothing but the pole and the date. Or whatever
the key is you wind up hooking it to. Even if you have only one row in that
table for each pole, that keeps the database clean, as well as making it
obvious everywhere you actually use that data later (in that everywhere you
actually use it, you'll be joining against the table).
>> Ah, welcome to the club. ;-) That's why I like being boss.
>>
>
> can be difficult if you someone in the group has a strong personality.
As long as they're not stubborn in the face of reason. :-)
> Thanks for the tips - for the image files I don't know if it will go to
> blobs or stay files simply because of making working with the files more
> complex. But leaving the files as is adds complexity as well. <sigh>
Yeah, the main reason would be to unify managing the pictures with managing
the rest of the data. If unifying the pictures makes it more complex to
manage rather than simpler, it doesn't make sense to do that. Make sure you
make that bit modular in your code, and you shouldn't have a problem. :-)
--
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
|
|
| |
| |
|
|
|
|
| |
| |
|
|
On 09/03/2011 05:11 PM, Darren New wrote:
> I think they turned off the encryption of the low bits back in Clinton's
> timeframe, when other countries started threatening to launch their own
> satellites. I think civilian GPS is quite accurate now.
My satnav device cannot accurately determine which motorway I'm on.
Given that a six-lane motorway isn't exactly small, this implies a very
large inaccuracy in the GPS location.
(Of course, I suppose they may have turned off the encryption, but that
doesn't mean that a specific device knows about this fact...)
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
| |
|
|
Invisible wrote:
> On 09/03/2011 05:11 PM, Darren New wrote:
>
>> I think they turned off the encryption of the low bits back in Clinton's
>> timeframe, when other countries started threatening to launch their own
>> satellites. I think civilian GPS is quite accurate now.
>
> My satnav device cannot accurately determine which motorway I'm on.
Yeah, and your calculator can't run DOOM. We already know you make bad
technology purchase choices. ;-)
> Given that a six-lane motorway isn't exactly small, this implies a very
> large inaccuracy in the GPS location.
No, it implies a crappy GPS receiver.
Plus, GPS while you're moving is somewhat harder to get accurate than GPS
while you're stationary. The way you get millimeter precision even when the
encryption is turned on is to leave the receiver in the same place for a
couple of days, integrating the position samples.
--
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
|
|
| |
| |
|
|
|
|
| |
|
|
