POV-Ray : Newsgroups : povray.off-topic : Database Questions Server Time
18 Nov 2026 19:30:33 EST (-0500)
  Database Questions (Message 39 to 68 of 68)  
<<< Previous 38 Messages Goto Initial 50 Messages
From: Tom Austin
Subject: Re: Database Questions
Date: 9 Mar 2011 14:28:34
Message: <4d77d4e2$1@news.povray.org>
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

From: Tom Austin
Subject: Re: Database Questions
Date: 9 Mar 2011 14:41:53
Message: <4d77d801$1@news.povray.org>
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

From: Tom Austin
Subject: Re: Database Questions
Date: 9 Mar 2011 14:43:10
Message: <4d77d84e$1@news.povray.org>
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

From: Darren New
Subject: Re: Database Questions
Date: 9 Mar 2011 14:57:42
Message: <4d77dbb6@news.povray.org>
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

From: Darren New
Subject: Re: Database Questions
Date: 9 Mar 2011 15:00:16
Message: <4d77dc50$1@news.povray.org>
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

From: Darren New
Subject: Re: Database Questions
Date: 9 Mar 2011 15:14:26
Message: <4d77dfa2$1@news.povray.org>
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

From: Darren New
Subject: Re: Database Questions
Date: 9 Mar 2011 16:48:57
Message: <4d77f5c9@news.povray.org>
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

From: Tom Austin
Subject: Re: Database Questions
Date: 9 Mar 2011 16:59:57
Message: <4d77f85d$1@news.povray.org>
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

From: Tom Austin
Subject: Re: Database Questions
Date: 9 Mar 2011 17:06:55
Message: <4d77f9ff$1@news.povray.org>
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

From: Darren New
Subject: Re: Database Questions
Date: 9 Mar 2011 18:34:59
Message: <4d780ea3$1@news.povray.org>
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

From: Invisible
Subject: Re: Database Questions
Date: 10 Mar 2011 04:17:52
Message: <4d789740$1@news.povray.org>
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

From: Darren New
Subject: Re: Database Questions
Date: 10 Mar 2011 11:51:22
Message: <4d79018a$1@news.povray.org>
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

From: Orchid XP v8
Subject: Re: Database Questions
Date: 10 Mar 2011 14:13:41
Message: <4d7922e5@news.povray.org>
>> 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. ;-)

Last time I checked, TomTom is the leading brand.

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

My dad has a crappy GPS receiver. It takes approximately 30 minutes to 
locate a GPS satelite. Mine, on the other hand, requires nearer to 30 
seconds...

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

Just because I'm on a motorway doesn't mean I'm *moving*! :-P

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


Post a reply to this message

From: Darren New
Subject: Re: Database Questions
Date: 10 Mar 2011 16:48:10
Message: <4d79471a$1@news.povray.org>
Orchid XP v8 wrote:
>>> 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. ;-)
> 
> Last time I checked, TomTom is the leading brand.

Sometimes, for a few minutes, a GPS receiver will be wrong, I've noticed. 
I'm assuming it happens when it picks up on a satellite for which it doesn't 
have ephemeris or something.  It usually corrects itself after a minute or less.

Of course, a GPS built into a car is going to assume the car is on one of 
the roads on the map, which can also throw things off.

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

From: Orchid XP v8
Subject: Re: Database Questions
Date: 10 Mar 2011 16:57:05
Message: <4d794931$1@news.povray.org>
> Of course, a GPS built into a car is going to assume the car is on one
> of the roads on the map, which can also throw things off.

More precisely, it assumes that you're on the road it's telling you to 
drive down, until you get X number of yards away from that road, at 
which point the location indicator suddenly snaps to the road that 
you're *actually* on.

Some of this might be the inaccuracy of the road maps, but even at low 
speed it seems to take a very long time to figure out where you actually 
are, which leads me to think that most of the time it's guessing, 
because the GPS fix isn't very accurate.

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


Post a reply to this message

From: Darren New
Subject: Re: Database Questions
Date: 10 Mar 2011 16:58:48
Message: <4d794998$1@news.povray.org>
Orchid XP v8 wrote:
> because the GPS fix isn't very accurate.

Yes, that's another behavior. I don't think it's because the GPS fix is 
inherently inaccurate, but more that it's not especially reliable. You 
wouldn't want your navigation to turn off every time you went through a 
tunnel, either.

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

From: Tom Austin
Subject: Re: Database Questions
Date: 10 Mar 2011 18:20:20
Message: <4d795cb4$1@news.povray.org>
On 3/9/2011 2:57 PM, Darren New wrote:
> 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.
>

This makes sense.

I think what still boggles my mind is how to do a double primary key / 
double foreign key relationship.

In the case of the receipts, what relationships do you create to have a 
table reference a particular receipt?

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

So, the primary key relationship keeps the data from being inserted by 
default.


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

And the trick is to have the database strong enough to prevent such 
errors from causing trouble in the first place.  You spend your time 
setting it up right or making fixes for problems that crop up.



>
>> 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 it will - but the new push is to be very dynamic - so dynamic 
that taking the time to set up a proper database is not possible.  From 
one extreme to another.  I guess I rather take this extreme - it means 
we are moving someplace.

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

So what's not cool about the query - should something like that be avoided?


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

Does this keep from having this case?
PoleID1	PoleID2
1   	2
2   	1

Does keying prevent this or does it have to be taken care of some other way?



>> 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"?
>

actual cables attached - so a pole can have 0 attachments.


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

Ok, I was confused but now I think I see clearly.  Instead of 
dereferencing to attachmentID like I did, one just uses the (poleID) and 
(attachment height) as the key from the attachments table.


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

No, but it gives a lot of food for thought.

I don't think you have proposed anything much different than what we 
have - it just rids the tables if ID's that can cause more trouble than 
they are worth.


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

yes, and this is done in the schema without the need for any real code 
at this point.

I like the concept - make it robust enough that it can't get fouled up - 
no matter what you try to do.

I don't know if we will get there, but I know we will make a few steps 
in that direction.

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

Now that I think about it more, I don't think I have actual circular 
going on.

I do have the case where I get connections from the connection table 
(pole1, pole2, distance) and I can also get the same information from 
the attachments table (poleID, height) and midspans( attachment1, 
attachment2) table.

The touble arises if the attachments/midspan tables create a connection 
that is not defined int he connections table.


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

I have the knowledge on how to make a database work - even properly in 
the sense that it is clean.  but the other 10% is the icing that makes 
things really work well and keep working well with less effort.


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

OK, a question

I have a table with a descriptor - let's say pole color.

Right now it is just that - a descriptor, but is to be presented to the 
user as a list of distinct items to choose from.

I can vaguely see in the future that I will want to group the color into 
families (maybe reds, blues, greens), but because of time I can't define 
what they are so I will ignore them for now.

At some point in the future the need comes up to define the families and 
add them to the DB.


How would one go about this kind of 'building' and how would keys play a 
role.

Would I simply specify the column as 'text' and just put the color into 
it for now.  Then pull from a distinct query to get the list of 
available colors.  Then in the future make a new table that uses this 
column as a foreign key that gives the families.  but then the distinct 
query is no longer needed as the new table has the distinct values.

Or should I go ahead and create the additional table so that it can 
provide the colors with a 1 stop place to add a new one?  Then as the 
families are needed just add them to the table?


I know this might not be 100% clear, but how would you go about this?

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

yes, I have seen this with Access - updates fail, but inserts work.


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

We won't get very big.  We survey maybe 5000+- poles in a given year.

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

Yes, I like helping others as well for much the same reason.


Post a reply to this message

From: Darren New
Subject: Re: Database Questions
Date: 10 Mar 2011 20:46:10
Message: <4d797ee2$1@news.povray.org>
Tom Austin wrote:
>> If the primary key is the cash register number + timestamp to the

> In the case of the receipts, what relationships do you create to have a 
> table reference a particular receipt?

You just store the same columns.

Let's say you had a table that recorded customer service records about a 
particular receipt.

create table receipts (
    register INT NOT NULL,
    issued TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
       ON UPDATE CURRENT_TIMESTAMP, -- MySql sucks
    amount decimal(10,2) NOT NULL,
    clerk INT NOT NULL,
    ....
    primary key (register, issued),
    FOREIGN KEY (clerk) references clerks(ID),
    ...);

create table complaints (
    receipt_register INT NOT NULL,
    receipt_issued TIMESTAMP NOT NULL,
    csr_talking INT not null,
    complaint_answered TIMESTAMP NOT NULL,
    resolution blob,
    previous_call_csr INT,
    previous_answered timestamp,
    primary key (csr_talking, complaint_answered),
    foreign key (receipt_register, receipt_issed)
       references receipts(register, issued),
    foreign key (previous_call_csr, previous_answered)
       references complaints(csr_talking, complaint_answered)
    );

So, modulo the fact that MySql dicks with timestamps in tables depending on 
how many timestamps are in the table, and disregarding the fact that MySql 
"NOT NULL" doesn't prevent you from inserting records with NULLs, we can 
look at this structure:

A receipt is identified by the register number it was issued at and the time 
it was issued. There's also other details, like the clerk that was logged 
in, the amount of the sale, and so on.

When the phone rings and someone complains, the CSR creates a new complaint 
record. This record is identified by the CSR taking the call and when they 
took the call. The complaint is about a particular receipt, identified by 
the register the receipt came from and the time that receipt was issued. 
This may be a follow-up call, in which case the CSR has the previous calls 
about this on the screen and may point out that this is a follow-up call 
from a particular previous complaint, identified by *that* csr at *that* time.

Does that clarify?

>> 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.
>>
> 
> So, the primary key relationship keeps the data from being inserted by 
> default.

Or at least you know when something goes wrong, instead of happily inserting 
two copies of every credit card, or pole, or whatever.

>> 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.
>>
> 
> And the trick is to have the database strong enough to prevent such 
> errors from causing trouble in the first place.  You spend your time 
> setting it up right or making fixes for problems that crop up.

Precisely. That's exactly the "C" part of the database. But if you *do* 
screw up, being able to go back and fix it and send out the next set of 
bills with a credit for the screw-up is helpful compared to going "I don't 
know, boss, all the balances are wrong and there's no way to know who we 
billed twice for last week."

Your database sounds somewhat different, but to the extent you can actually 
figure out by looking at the database what happened to change it, the easier 
it's going to be to fix problems caused by going fast. Just something to 
keep in mind.

> I think it will - but the new push is to be very dynamic - so dynamic 
> that taking the time to set up a proper database is not possible.  From 
> one extreme to another.  I guess I rather take this extreme - it means 
> we are moving someplace.

Well, good luck with that. Some people can make that work, but I strongly 
suspect your boss is just a jerk. :-)

>>> 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.
>>
> 
> So what's not cool about the query - should something like that be avoided?

No, the query is cool. It's fine. (I mean, other than being syntactically 
wrong. :-) What you probably wanted to write is

select A from B where B.ID in (select ID from C)

but a better way of writing that is

select A from B, C where B.ID = C.ID

The nice thing about SQL is that both those statements will probably do 
exactly the same thing inside the server.

The thing I'm trying to get you to avoid is putting a query inside a loop 
that doesn't involve user interaction. I.e., you shouldn't have a for loop 
or while loop in your code with a SQL select statement inside the body of 
the for loop unless there's other I/O to read non-database files or take 
clicks from the GUI or something.  You should never need to do something like

$first_result = query_mysql("SELECT name from CUSTOMERS")
foreach ($name in $first_result)
    $second_result = query_mysql(
        "SELECT account from ACCOUNTS where ACCOUNTS.NAME=" + $name)

Instead, that should be one query:
select name, account from customers, accounts
    where customer.name=accounts.name


> Does this keep from having this case?
> PoleID1    PoleID2
> 1       2
> 2       1

Well, technically not.

> Does keying prevent this or does it have to be taken care of some other 
> way?

To prevent this, you'd probably have to use a trigger. The "consistency" in 
a database is supposed to be done by having a trigger reject the insert if 
the poles in a different order are already there.

That's why you have to take time to think about it: If you just type it in 
as fast as you can think, you wind up with something like this and then 
you're screwed. :-)


> Ok, I was confused but now I think I see clearly.  Instead of 
> dereferencing to attachmentID like I did, one just uses the (poleID) and 
> (attachment height) as the key from the attachments table.

Yes, you can do it that way.

> I don't think you have proposed anything much different than what we 
> have - it just rids the tables if ID's that can cause more trouble than 
> they are worth.

If you can manage that, it makes things more clear.

For one thing, for example, if you identify cables by (for example) the 
poles they're run between in the GUI, you don't have to then go look up the 
ID from that information in order to use it elsewhere. If you find that 
you're never actually referencing the ID in queries when you're not also 
referencing the pole and height, chances are that ID can be replaced by the 
pole and height.

> I like the concept - make it robust enough that it can't get fouled up - 
> no matter what you try to do.

That's the goal, yes. That's the idea behind the "C" part of ACID. When you 
have a monsterous database that has to live for decades, it's worth spending 
weeks or months figuring that bit out. How much it's worth to *you* depends 
on how easy it is to clean up mistakes.  If each job essentially starts 
over, it might not be worth putting too much time in up front, if you can 
just (say) re-import the excel sheets for where the poles are on the third 
job if you find the second job messed stuff up too bad.

> Now that I think about it more, I don't think I have actual circular 
> going on.

It didn't sound to me like you did.

> The touble arises if the attachments/midspan tables create a connection 
> that is not defined int he connections table.

Exactly. There really isn't an attachment without a midspan or a midspan 
without an attachment, nor is there an attachment on just one pole.

> How would one go about this kind of 'building' and how would keys play a 
> role.

I would say "color" is an attribute that deserves its own table. Assuming 
that all colors are equal and don't need any calculations and such, I'd 
store the colors in a table with
    ID int, ColorName varchar(20)
or something like that.

This lets you change the color names (boss wants them all in caps) or later 
add internationalized names (now you have ID, ColorName, Language in the 
table), etc. Plus, it's more space efficient to store an ID number than a 
color name everywhere. It also keeps people from misspelling a color and you 
wind up having both Green and Gren poles.

When adding family support, I would then add a second table that holds the 
allowable families. If each color was in only one family, I would put the ID 
of the family in the colors table. Otherwise, I would have a third table 
with Color.ID and Family.ID.

> Would I simply specify the column as 'text' and just put the color into 
> it for now.  Then pull from a distinct query to get the list of 
> available colors.  Then in the future make a new table that uses this 
> column as a foreign key that gives the families.  but then the distinct 
> query is no longer needed as the new table has the distinct values.

> Or should I go ahead and create the additional table so that it can 
> provide the colors with a 1 stop place to add a new one?  Then as the 
> families are needed just add them to the table?
> 
> 
> I know this might not be 100% clear, but how would you go about this?

Generally, if you have the same thing repeated many times in a table's 
column from a small selection of values, but it's *not* a foreign key, 
you've probably done something wrong. You probably want a table with that 
set of values as its primary key.

You can certainly start out with the color in its own column, then move 
things around later when you find it's problematic. You might have some 
processing time, taking the system offline for half an hour while you 
rearrange the tables and such, but I'm guessing (given you were using Access 
before) that this wouldn't be a problem every month or two. :-)

You can even start out with the color as text in the column, later add 
another table that has the colors as the primary key and add the FK 
constraint, and after that start putting the different translations or 
families or whatever on that second column. I.e., where I refered to the 
"color.ID" above, you could just use the text of the color name as the ID, 
then have the translation or the family or whatever keyed off of that. Only 
after space or speed becomes a problem do you really need to worry about 
making it into an integer ID or some such.

> We won't get very big.  We survey maybe 5000+- poles in a given year.

You should have zero concern about performance. Even a pretty weak machine 
should be able to do something with everything in your entire database 
several times a minute, let alone individual small groups of stuff.

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

From: scott
Subject: Re: Database Questions
Date: 11 Mar 2011 03:43:02
Message: <4d79e096$1@news.povray.org>
> More precisely, it assumes that you're on the road it's telling you to
> drive down, until you get X number of yards away from that road, at
> which point the location indicator suddenly snaps to the road that
> you're *actually* on.

Indeed, whilst moving the accuracy is not good enough to determine 
whether you're on the motorway or on the slip-road 5 metres to the side 
of it, so it assumes you're following the route (if any) or staying on 
the road (if no route planned).  Once the receiver knows, with a 
specific degree of certainty, that you are on a different road, it will 
snap you there on the map.

> Some of this might be the inaccuracy of the road maps, but even at low
> speed it seems to take a very long time to figure out where you actually
> are, which leads me to think that most of the time it's guessing,
> because the GPS fix isn't very accurate.

Once you're more than 10 metres or so away from the route it should snap 
instantly, if it doesn't then you've either got a low signal (GPS 
antenna positioning?) or a rubbish GPS receiver.

BTW the ones built into the car are far superior as the GPS antenna is 
placed in the best spot and it gets input from the steering angle and 
wheel speed (so it still works underground and from the second you turn 
on the car).  If you've used one you'll never go back to the portable 
add-on ones.


Post a reply to this message

From: Invisible
Subject: Re: Database Questions
Date: 11 Mar 2011 04:26:20
Message: <4d79eabc$1@news.povray.org>
On 11/03/2011 08:43 AM, scott wrote:
>> More precisely, it assumes that you're on the road it's telling you to
>> drive down, until you get X number of yards away from that road, at
>> which point the location indicator suddenly snaps to the road that
>> you're *actually* on.
>
> Indeed, whilst moving the accuracy is not good enough to determine
> whether you're on the motorway or on the slip-road 5 metres to the side
> of it

Which is what I said. GPS isn't very accurate.

> Once you're more than 10 metres or so away from the route it should snap
> instantly

I haven't measured whether it's 10 meters, but yeah, that's 
approximately what it does.

> BTW the ones built into the car are far superior

And outside of everybody's price range.

> If you've used one you'll never go back to the portable add-on ones.

Well, you will, because nobody has £80,000 for a car that has one. :-P


Post a reply to this message

From: Stephen
Subject: Re: Database Questions
Date: 11 Mar 2011 05:16:59
Message: <4d79f69b@news.povray.org>
On 11/03/2011 9:26 AM, Invisible wrote:
> Which is what I said. GPS isn't very accurate.

When you consider that the satellites orbit about 12,550 miles above the 
earth and the clock in your Tom Tom is not very expensive. The accuracy 
is acceptable IMO.

-- 
Regards
     Stephen


Post a reply to this message

From: Invisible
Subject: Re: Database Questions
Date: 11 Mar 2011 05:25:35
Message: <4d79f89f@news.povray.org>
On 11/03/2011 10:16 AM, Stephen wrote:
> On 11/03/2011 9:26 AM, Invisible wrote:
>> Which is what I said. GPS isn't very accurate.
>
> When you consider that the satellites orbit about 12,550 miles above the
> earth

...then it's amazing that it can fix your location to the nearest 
landmass, never mind the nearest city. I agree. ;-)


Post a reply to this message

From: Stephen
Subject: Re: Database Questions
Date: 11 Mar 2011 05:42:26
Message: <4d79fc92$1@news.povray.org>
On 11/03/2011 10:25 AM, Invisible wrote:
> On 11/03/2011 10:16 AM, Stephen wrote:
>> On 11/03/2011 9:26 AM, Invisible wrote:
>>> Which is what I said. GPS isn't very accurate.
>>
>> When you consider that the satellites orbit about 12,550 miles above the
>> earth
>
> ....then it's amazing that it can fix your location to the nearest
> landmass, never mind the nearest city. I agree. ;-)

I have used my Tom Tom to navigate around Chester by foot. It saved 
memorising a page of the A to Z.

-- 
Regards
     Stephen


Post a reply to this message

From: scott
Subject: Re: Database Questions
Date: 11 Mar 2011 05:54:41
Message: <4d79ff71$1@news.povray.org>
>> BTW the ones built into the car are far superior
>
> And outside of everybody's price range.

Maybe 10 years ago, today navigation is usually an option even on most 
B-segment cars (small mini size cars).  I just checked the Renault 
website, even on the Clio you can get navigation built-in for £470.  I 
imagine in 10 years time pretty much every car will have it as standard 
(and obviously more and more 2nd hand cars will have navigation installed).


Post a reply to this message

From: Darren New
Subject: Re: Database Questions
Date: 11 Mar 2011 11:57:53
Message: <4d7a5491$1@news.povray.org>
Invisible wrote:
> Which is what I said. GPS isn't very accurate.

It's not GPS that isn't accurate. It's the cheap GPS receiver in your car 
that isn't accurate.  GPS has a client and a server and they have to work 
together for the accuracy.

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

From: Tom Austin
Subject: Re: Database Questions
Date: 11 Mar 2011 14:09:50
Message: <4d7a737e$1@news.povray.org>
On 3/10/2011 8:46 PM, Darren New wrote:
> Tom Austin wrote:
>>> If the primary key is the cash register number + timestamp to the
>
>> In the case of the receipts, what relationships do you create to have
>> a table reference a particular receipt?
>
> You just store the same columns.
>

Sounds like a lot of 'extra' data storage, but if a DB is fast anyway, 
then the gain of .01s on a query doesn't make a difference.

> Let's say you had a table that recorded customer service records about a
> particular receipt.
>
> create table receipts (
> register INT NOT NULL,
> issued TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
> ON UPDATE CURRENT_TIMESTAMP, -- MySql sucks
> amount decimal(10,2) NOT NULL,
> clerk INT NOT NULL,
> ....
> primary key (register, issued),
> FOREIGN KEY (clerk) references clerks(ID),
> ...);
>
> create table complaints (
> receipt_register INT NOT NULL,
> receipt_issued TIMESTAMP NOT NULL,
> csr_talking INT not null,
> complaint_answered TIMESTAMP NOT NULL,
> resolution blob,
> previous_call_csr INT,
> previous_answered timestamp,
> primary key (csr_talking, complaint_answered),
> foreign key (receipt_register, receipt_issed)
> references receipts(register, issued),
> foreign key (previous_call_csr, previous_answered)
> references complaints(csr_talking, complaint_answered)
> );
>
> So, modulo the fact that MySql dicks with timestamps in tables depending
> on how many timestamps are in the table, and disregarding the fact that
> MySql "NOT NULL" doesn't prevent you from inserting records with NULLs,
> we can look at this structure:
>

MySQL changes timestamps....  just precision or something else?


> A receipt is identified by the register number it was issued at and the
> time it was issued. There's also other details, like the clerk that was
> logged in, the amount of the sale, and so on.
>
> When the phone rings and someone complains, the CSR creates a new
> complaint record. This record is identified by the CSR taking the call
> and when they took the call. The complaint is about a particular
> receipt, identified by the register the receipt came from and the time
> that receipt was issued. This may be a follow-up call, in which case the
> CSR has the previous calls about this on the screen and may point out
> that this is a follow-up call from a particular previous complaint,
> identified by *that* csr at *that* time.
>
> Does that clarify?
>

Yes, I think I am seeing it clearly.  Just store the data as it is.  be 
smart about what is picked for the keys and use IDs sparingly.

IDs are great, but they can allow duplicates and potentially break your 
DB unless your application is smart enough to prevent the error - but 
wait, the goal of a good db is that it cannot be broken no matter what 
accesses it.

IDs also require that you get the ID so that you can join on it - even 
if you have the other data.  If you have the other data anyway, why go 
fetch an ID...

>>> 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.
>>>
>>
>> So, the primary key relationship keeps the data from being inserted by
>> default.
>
> Or at least you know when something goes wrong, instead of happily
> inserting two copies of every credit card, or pole, or whatever.
>

I like the idea of building a database that prevents errors in the first 
place.  Let's see if I can get it put into place here.  If so, I think 
it will happen over time just because of how fast people like to work here.

>
> Precisely. That's exactly the "C" part of the database. But if you *do*
> screw up, being able to go back and fix it and send out the next set of
> bills with a credit for the screw-up is helpful compared to going "I
> don't know, boss, all the balances are wrong and there's no way to know
> who we billed twice for last week."
>

We currently have some of this.  But it a balance - is our data good 
enough to get the client's requirements done.....  Most of the time yes.


> Your database sounds somewhat different, but to the extent you can
> actually figure out by looking at the database what happened to change
> it, the easier it's going to be to fix problems caused by going fast.
> Just something to keep in mind.
>

Yes, that's some of the push for simplifying it - but from a usability 
standpoint.  I think the person sees how much effort I have put into 
coding and how long some things take and wants to reduce that.  The main 
problem is that everything was done at lightening speed so it will suck 
by definition.

>
> Well, good luck with that. Some people can make that work, but I
> strongly suspect your boss is just a jerk. :-)
>

No, the boss isn't a jerk in this case.  We just have a bunch of young 
people who don't know how to slow down.  Out of 6-7 people, only 2 of us 
are over the age of 26.  If we are to keep the younger crowd, we have to 
cater somewhat to their needs/expectations - as long as it moves us in 
the right direction.


>>>> 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.
>>>
>>
>> So what's not cool about the query - should something like that be
>> avoided?
>
> No, the query is cool. It's fine. (I mean, other than being
> syntactically wrong. :-) What you probably wanted to write is
>
> select A from B where B.ID in (select ID from C)
>
> but a better way of writing that is
>
> select A from B, C where B.ID = C.ID
>

Ok, so I typed it off the top of my head and referenced the wrong table :-)

But at least you knew what it was getting at.


> The nice thing about SQL is that both those statements will probably do
> exactly the same thing inside the server.
>
> The thing I'm trying to get you to avoid is putting a query inside a
> loop that doesn't involve user interaction. I.e., you shouldn't have a
> for loop or while loop in your code with a SQL select statement inside
> the body of the for loop unless there's other I/O to read non-database
> files or take clicks from the GUI or something. You should never need to
> do something like
>
> $first_result = query_mysql("SELECT name from CUSTOMERS")
> foreach ($name in $first_result)
> $second_result = query_mysql(
> "SELECT account from ACCOUNTS where ACCOUNTS.NAME=" + $name)
>
> Instead, that should be one query:
> select name, account from customers, accounts
> where customer.name=accounts.name
>
>


Yes, 100% agreed - this should be avoided.  The only way I have any of 
this at this point is by putting objects with other objects to make the 
objects easier to work with.  I know - there is a better way of doing 
this....


>> Does this keep from having this case?
>> PoleID1 PoleID2
>> 1 2
>> 2 1
>
> Well, technically not.
>
>> Does keying prevent this or does it have to be taken care of some
>> other way?
>
> To prevent this, you'd probably have to use a trigger. The "consistency"
> in a database is supposed to be done by having a trigger reject the
> insert if the poles in a different order are already there.
>
> That's why you have to take time to think about it: If you just type it
> in as fast as you can think, you wind up with something like this and
> then you're screwed. :-)
>

Yes, triggers, I remember those back from my SQL Server days.  They can 
do a lot for you - more than just keeping your data straight.  I can't 
wait to get back to those and be able to use them as a tool :-)

>
> If you can manage that, it makes things more clear.
>
> For one thing, for example, if you identify cables by (for example) the
> poles they're run between in the GUI, you don't have to then go look up
> the ID from that information in order to use it elsewhere. If you find
> that you're never actually referencing the ID in queries when you're not
> also referencing the pole and height, chances are that ID can be
> replaced by the pole and height.
>

yes, I do have to 'fetch' the ID from time to time.  I like not having 
to do that.  It's just getting things straight enough so that not using 
an ID doesn't mess me up too bad.  I don't have as much experience with 
setting up keys and making sure they don't mes things up.  Bu that is 
something that I am going to be working on :-)

>> I like the concept - make it robust enough that it can't get fouled up
>> - no matter what you try to do.
>
> That's the goal, yes. That's the idea behind the "C" part of ACID. When
> you have a monsterous database that has to live for decades, it's worth
> spending weeks or months figuring that bit out. How much it's worth to
> *you* depends on how easy it is to clean up mistakes. If each job
> essentially starts over, it might not be worth putting too much time in
> up front, if you can just (say) re-import the excel sheets for where the
> poles are on the third job if you find the second job messed stuff up
> too bad.
>

I think for the time being we will be morphing with each job until 
things get more stable.  Then we may start putting the data more together.

>
> Exactly. There really isn't an attachment without a midspan or a midspan
> without an attachment, nor is there an attachment on just one pole.
>

Well, there is - transformers and cables that are perpendicular to our 
direction of travel :-)


>> How would one go about this kind of 'building' and how would keys play
>> a role.
>
> I would say "color" is an attribute that deserves its own table.
> Assuming that all colors are equal and don't need any calculations and
> such, I'd store the colors in a table with
> ID int, ColorName varchar(20)
> or something like that.
>
> This lets you change the color names (boss wants them all in caps) or
> later add internationalized names (now you have ID, ColorName, Language
> in the table), etc. Plus, it's more space efficient to store an ID
> number than a color name everywhere. It also keeps people from
> misspelling a color and you wind up having both Green and Gren poles.
>

So on lists like this, an ID is a good idea - and you wouldn't use the 
ColorName as the pk/fk.




> When adding family support, I would then add a second table that holds
> the allowable families. If each color was in only one family, I would
> put the ID of the family in the colors table. Otherwise, I would have a
> third table with Color.ID and Family.ID.
>

yes, a table to group the items according to how they can be grouped (1 
2 many, many 2 many...)


> Generally, if you have the same thing repeated many times in a table's
> column from a small selection of values, but it's *not* a foreign key,
> you've probably done something wrong. You probably want a table with
> that set of values as its primary key.
>

OK, that helps.

One can take the ideas of using non ID columns for pks too far.  So, in 
the case of colors, go ahead and set up a pk/fk relationship even if it 
is the *only* thing in the second table.

> You can certainly start out with the color in its own column, then move
> things around later when you find it's problematic. You might have some
> processing time, taking the system offline for half an hour while you
> rearrange the tables and such, but I'm guessing (given you were using
> Access before) that this wouldn't be a problem every month or two. :-)
>

So, if we see it probably needing to be 'broken out' in the future, we 
should go ahead and break it out now.

> You can even start out with the color as text in the column, later add
> another table that has the colors as the primary key and add the FK
> constraint, and after that start putting the different translations or
> families or whatever on that second column. I.e., where I refered to the
> "color.ID" above, you could just use the text of the color name as the
> ID, then have the translation or the family or whatever keyed off of
> that. Only after space or speed becomes a problem do you really need to
> worry about making it into an integer ID or some such.
>

With what little you know about what we are trying to do, what approach 
would you do in this case?  Remember, you know you may want to do 
something with color families, but you don't know yet.


>> We won't get very big. We survey maybe 5000+- poles in a given year.
>
> You should have zero concern about performance. Even a pretty weak
> machine should be able to do something with everything in your entire
> database several times a minute, let alone individual small groups of
> stuff.
>

I agree, it seems that the only way we would need a beast of a machine 
is if we somehow make it very inefficient..... but that's a trade off - 
development time or hardware cost.....


Post a reply to this message

From: Darren New
Subject: Re: Database Questions
Date: 11 Mar 2011 18:05:42
Message: <4d7aaac6$1@news.povray.org>
Tom Austin wrote:
> Sounds like a lot of 'extra' data storage, but if a DB is fast anyway, 
> then the gain of .01s on a query doesn't make a difference.

It can be. Like all things, you have to make the trade-off between 
readability, reliability, size, and speed.

> MySQL changes timestamps....  just precision or something else?

MySQL basically assumes the first timestamp is the time you last changed the 
record, and the second was the time you created the record, or something 
stupid like that. You need to read the timestamp documentation.

Basically, MySQL will change your data without telling you, depending on 
what order you declared the columns in. Yes, MySQL sucks.

> Yes, I think I am seeing it clearly.  Just store the data as it is.  be 
> smart about what is picked for the keys and use IDs sparingly.

Yep.

> IDs are great, but they can allow duplicates and potentially break your 
> DB unless your application is smart enough to prevent the error - but 
> wait, the goal of a good db is that it cannot be broken no matter what 
> accesses it.

Yep.

> IDs also require that you get the ID so that you can join on it - even 
> if you have the other data.  If you have the other data anyway, why go 
> fetch an ID...

Right. Altho the join doesn't have to use the ID.

select * from receipts,sales where receipt.time=sales.time and 
receipt.register=sales.register

works even if receipts and sales both have ID columns.

> I like the idea of building a database that prevents errors in the first 
> place.  Let's see if I can get it put into place here.  If so, I think 
> it will happen over time just because of how fast people like to work here.

Good job. :-)

> We currently have some of this.  But it a balance - is our data good 
> enough to get the client's requirements done.....  Most of the time yes.

Indeed, it's always a balance. That's something a lot of people don't manage 
to recognise for some reason.

> Yes, that's some of the push for simplifying it - but from a usability 
> standpoint.  I think the person sees how much effort I have put into 
> coding and how long some things take and wants to reduce that.  The main 
> problem is that everything was done at lightening speed so it will suck 
> by definition.

Time. Cost. Quality.  Pick any two.

>> Well, good luck with that. Some people can make that work, but I
>> strongly suspect your boss is just a jerk. :-)
>>
> 
> No, the boss isn't a jerk in this case.  We just have a bunch of young 
> people who don't know how to slow down.  Out of 6-7 people, only 2 of us 
> are over the age of 26.  If we are to keep the younger crowd, we have to 
> cater somewhat to their needs/expectations - as long as it moves us in 
> the right direction.

Fair enough. Get input from them, teach them why you're doing it the way you 
are, and leave places in the system where they can do things the way they 
like (say, the GUI) while keeping the database clean.

> Ok, so I typed it off the top of my head and referenced the wrong table :-)
> But at least you knew what it was getting at.

Yeah.

> Yes, 100% agreed - this should be avoided.  The only way I have any of 
> this at this point is by putting objects with other objects to make the 
> objects easier to work with.  I know - there is a better way of doing 
> this....

Objects inside objects isn't a problem. SQL doesn't really do objects well. 
The trick is to encapsulate that in a business layer. The bottom end of the 
business layer is the database table definitions, and the top end is the 
conceptual things your business thinks about.

> Yes, triggers, I remember those back from my SQL Server days.  They can 
> do a lot for you - more than just keeping your data straight.  I can't 
> wait to get back to those and be able to use them as a tool :-)

I don't think MySQL has triggers.  Heck, you have to go out of your way to 
get MySQL to enforce foreign key constraints, let along triggers or views.

> yes, I do have to 'fetch' the ID from time to time.  I like not having 
> to do that.  It's just getting things straight enough so that not using 
> an ID doesn't mess me up too bad.  I don't have as much experience with 
> setting up keys and making sure they don't mes things up.  Bu that is 
> something that I am going to be working on :-)

Cool. And again, it's mostly just a personal preference of mine borne out by 
experience. It's rejecting the concept that every table, by default, must 
have and be referenced by an arbitrary integer key.  It's not saying they're 
bad, but just "think about it for more than 2 seconds."

> I think for the time being we will be morphing with each job until 
> things get more stable.  Then we may start putting the data more together.

Yeah, keep it agile. Keep every version of your schema around so you can 
tell what you changed.

> Well, there is - transformers and cables that are perpendicular to our 
> direction of travel :-)

Oh, fair enough. But you see what I'm trying to say.  If, logically, you 
can't have one without the other, they need to both be in the same row.

> So on lists like this, an ID is a good idea - and you wouldn't use the 
> ColorName as the pk/fk.

Not necessarily. Either an integer or the color name itself would work. This 
table is likely not to change often enough to be at risk of being screwed 
up, and honestly color names are already pretty arbitrary.

If you had a table of color RGB values, that would be different. Then you're 
talking about something "real". But the names themselves are already just 
symbols for the actual color, and one color can have multiple names etc.

> One can take the ideas of using non ID columns for pks too far.  So, in 
> the case of colors, go ahead and set up a pk/fk relationship even if it 
> is the *only* thing in the second table.

Yes. The point there is to enforce that all the colors are selected from a 
fixed list of possible colors. The text isn't really text, it's an 
identifier for a color in textual form. If you store it multiple times in 
textual form, it gives an opportunity to accidentally get a piece of text 
that isn't a color identifier into the color column.

Now, if you wanted to map colors to unique-names-of-colors (for PANTONE or 
something, say), I'd say make the RGB of the color be the PK.


>> You can certainly start out with the color in its own column, then move
>> things around later when you find it's problematic. You might have some
>> processing time, taking the system offline for half an hour while you
>> rearrange the tables and such, but I'm guessing (given you were using
>> Access before) that this wouldn't be a problem every month or two. :-)
>>
> 
> So, if we see it probably needing to be 'broken out' in the future, we 
> should go ahead and break it out now.

I would write code that accesses the database while being aware that it 
might need to be broken out. I wouldn't necessarily break it out now. You 
can break it out later, but only if you don't have a big rewrite to break it 
out.

Figuring out where the fracture lines lie is experience.

>> You can even start out with the color as text in the column, later add
>> another table that has the colors as the primary key and add the FK
>> constraint, and after that start putting the different translations or
>> families or whatever on that second column. I.e., where I refered to the
>> "color.ID" above, you could just use the text of the color name as the
>> ID, then have the translation or the family or whatever keyed off of
>> that. Only after space or speed becomes a problem do you really need to
>> worry about making it into an integer ID or some such.
>>
> 
> With what little you know about what we are trying to do, what approach 
> would you do in this case?  Remember, you know you may want to do 
> something with color families, but you don't know yet.

If you have a fixed number of colors (i.e., if you're talking about using 
color as a label rather than an *actual* color) then I would put the colors 
in a table with an ID and a color label and refer to the ID and not worry 
about the families and such.

I.e., if you're talking about "this is a green tag pole, that is an orange 
cable, this is a blue-white terminal" then the colors are tags. If you care 
about which shade of orange it is because you're mixing matching paint or 
something, that's not using the color as a label so I'd have a different answer.

So I would start with the colors in a table with
   ID int, ColorName varchar(50), primary key ID, unique index ColorName.

Use the ID to refer to colors, and you'll have the most flexibility in the 
future. Don't store IDs outside the database, tho - always translate them to 
color names before sticking them in external files or hard-coding them into 
a UI or something.

> I agree, it seems that the only way we would need a beast of a machine 
> is if we somehow make it very inefficient..... but that's a trade off - 
> development time or hardware cost.....

Yeah, where a "beast" machine is $1000. :-)

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

From: Tom Austin
Subject: Re: Database Questions
Date: 14 Mar 2011 09:46:23
Message: <4d7e1c2f$1@news.povray.org>
On 3/11/2011 6:05 PM, Darren New wrote:
>
> Right. Altho the join doesn't have to use the ID.
>
> select * from receipts,sales where receipt.time=sales.time and
> receipt.register=sales.register
>
> works even if receipts and sales both have ID columns.
>

Yes, I gathered that ;-)
Will make any transition easier as ID's don't have to be removed cold 
turkey.

>
>> We currently have some of this. But it a balance - is our data good
>> enough to get the client's requirements done..... Most of the time yes.
>
> Indeed, it's always a balance. That's something a lot of people don't
> manage to recognise for some reason.
>

yes, tho even when recognized people may have different notions of what 
it should be.

>
> Time. Cost. Quality. Pick any two.
>

That's our motto to our clients who want all three.

>
>> Yes, 100% agreed - this should be avoided. The only way I have any of
>> this at this point is by putting objects with other objects to make
>> the objects easier to work with. I know - there is a better way of
>> doing this....
>
> Objects inside objects isn't a problem. SQL doesn't really do objects
> well. The trick is to encapsulate that in a business layer. The bottom
> end of the business layer is the database table definitions, and the top
> end is the conceptual things your business thinks about.
>

Yep, SQL does tables well - if you want objects it has to come from the 
outside.

>> Yes, triggers, I remember those back from my SQL Server days. They can
>> do a lot for you - more than just keeping your data straight. I can't
>> wait to get back to those and be able to use them as a tool :-)
>
> I don't think MySQL has triggers. Heck, you have to go out of your way
> to get MySQL to enforce foreign key constraints, let along triggers or
> views.
>

I've been following it a bit over the years - at least some table 
formats in MySQL support triggers now.

> Yes. The point there is to enforce that all the colors are selected from
> a fixed list of possible colors. The text isn't really text, it's an
> identifier for a color in textual form. If you store it multiple times
> in textual form, it gives an opportunity to accidentally get a piece of
> text that isn't a color identifier into the color column.
>

I think I am grasping the concept.  If you need a 'pick list' you should 
have a table with the picklist values.  Storing an ID or piece of text 
doesn't matter as long as you have pk/fk set up correctly to prevent 
data corruption.

>
> If you have a fixed number of colors (i.e., if you're talking about
> using color as a label rather than an *actual* color) then I would put
> the colors in a table with an ID and a color label and refer to the ID
> and not worry about the families and such.
>

That's how I would approach it myself - I just wanted to hear how 
someone else would approach it.  Thanks for helping to verify my gut 
feelings.

> Use the ID to refer to colors, and you'll have the most flexibility in
> the future. Don't store IDs outside the database, tho - always translate
> them to color names before sticking them in external files or
> hard-coding them into a UI or something.
>

Yes, that would not be smart - ID's have no meaning outside the DB.

Thanks for all your input.  You have helped to confirm that my gut 
feelings on how to set up a DB are mostly in the right directions.

2 big points I take from this:

Be willing to explore better PK/FK uses.
I have always known that a PK doesn't have to be a number, but it always 
felt foreign to use text or some other number as they don't feel as 
'precise' as an integer.  I guess I've run into too many cases of trying 
to match floats or case sensitive text to let it feel natural.

Design the DB purely from 'what is out there'.  Don't take use into 
account (or at least minimize it).


Now for a good DB engine...  I'll start a new thread for that :-)


Post a reply to this message

From: Darren New
Subject: Re: Database Questions
Date: 14 Mar 2011 12:54:16
Message: <4d7e4838@news.povray.org>
Tom Austin wrote:
> I think I am grasping the concept.  If you need a 'pick list' you should 
> have a table with the picklist values.  Storing an ID or piece of text 
> doesn't matter as long as you have pk/fk set up correctly to prevent 
> data corruption.

Exactly. And it gives you a hook to expand on in the future. You have a 
place now to talk about colors divorced from where you reference them.

> Thanks for all your input.  You have helped to confirm that my gut 
> feelings on how to set up a DB are mostly in the right directions.

No problem. It helped me clarify how to word explanations too.

> 2 big points I take from this:
> 
> Be willing to explore better PK/FK uses.
> I have always known that a PK doesn't have to be a number, but it always 
> felt foreign to use text or some other number as they don't feel as 
> 'precise' as an integer.  I guess I've run into too many cases of trying 
> to match floats or case sensitive text to let it feel natural.

Yeah, exactly. You still have to be careful.

The other thing is, if upper/lower case makes a difference, or you're using 
a float, those are probably not things distinct enough for a real-world 
"unique identifier" anyway.  If you have trouble due to possible confusions 
with representation or precision, it's likely not a unique attribute of the 
object anyway.  I.e., if upper/lower case can be a confusion because it's 
(say) a human's name, well the name isn't a unique identifier anyway. If 
you're measuring the height of something and you don't know the precision to 
which you have to round it, chances are it's not a unique height anyway.

To use something like an email address, you have to decide how you're going 
to normalize it. Even tho RFC822 allows case-specific email addresses, in 
practice everybody lower-cases it. If you're talking about GPS coordinates 
and you don't have a significant minimum distance between objects, the GPS 
coordinates aren't going to be unique anyway.  E.g., telephone poles? Sure, 
the diameter of a phone pole is orders of magnitude smaller than the 
distance between them.  GPS locations of mail boxes? Probably not what you'd 
call unique.

And using a third-party identifier works well too. House addresses, zip 
codes, airport codes, phone numbers (plus timestamp of course), etc. If you 
can push the authoritative decision onto some other public organization, 
you're in good shape.

> Design the DB purely from 'what is out there'.  Don't take use into 
> account (or at least minimize it).

Yes. This is one of the really important ones that the self-educated guys 
tend to mess up.

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

From: Tom Austin
Subject: Re: Database Questions
Date: 14 Mar 2011 13:20:13
Message: <4d7e4e4d$1@news.povray.org>
On 3/14/2011 12:54 PM, Darren New wrote:
> Tom Austin wrote:
>> I think I am grasping the concept. If you need a 'pick list' you
>> should have a table with the picklist values. Storing an ID or piece
>> of text doesn't matter as long as you have pk/fk set up correctly to
>> prevent data corruption.
>
> Exactly. And it gives you a hook to expand on in the future. You have a
> place now to talk about colors divorced from where you reference them.
>

Now I have to get that through the other guys here <sigh>


>
> Yeah, exactly. You still have to be careful.
>

you can say that again

> And using a third-party identifier works well too. House addresses, zip
> codes, airport codes, phone numbers (plus timestamp of course), etc. If
> you can push the authoritative decision onto some other public
> organization, you're in good shape.
>

All good food to chew on.


>> Design the DB purely from 'what is out there'. Don't take use into
>> account (or at least minimize it).
>
> Yes. This is one of the really important ones that the self-educated
> guys tend to mess up.
>

I have no idea what you could be talking about ;-)


Post a reply to this message

<<< Previous 38 Messages Goto Initial 50 Messages

Copyright 2003-2023 Persistence of Vision Raytracer Pty. Ltd.