James G. Sack (jim) wrote:
OK, I see now you weren't just making a database for the keys to your
cars in the back-40. :-)
Oops. I neglected to mention that I'm a locksmith. I've mentioned it
before on the list somewhere and perhaps just didn't think to mention it
here.
It looks interesting, and I would like to spend a little time thinking
about it, but I have a couple of questions just-for-now. (See
interspersed remarks)
Whee!
Ralph Shumaker wrote:
..
OK, making things a little less simple, how about this:
Bear with me as I attempt to present this in a way that doesn't mangle
it all into a big mess.
The H75 appears to have *zero* substitutes. But that kee is used by
several Ford vehicles (not to mention Mercury, Lincoln, and others).
Just Ford alone has 25 entries using that kee.
It's not as simple as saying "the Ranger takes the H75" because the H75
was used by the Ranger from 97 thru 98 and during 05. Some (not all)
models of the Ranger used the H75 in 96, and 99 thru 00. And the Ranger
only accounts for 4 of the 25 entries for that kee.
If I'm making a kee for a vehicle that calls for the H67 and I'm out of
that kee, then I can substitute the H54, H55, H62, or H66. Now, I'm not
sure I trust the H55 and H62 info, because only one car lists those two
Where does the list (lists) come from? Car mfgr? Kee (keemaker?) mfgr?
Two (of my three) sources come from kee makers, Ilco and Jet. Ilco is
the commonly used one, but I suspect that Jet's is probably more
reliable. They are very different on a few things, and both have their
advantages. I haven't seen the one I have from Jet for some time, but I
downloaded the PDF version. I don't know if Ilco has an electronic
version. I kinda doubt it, only because I think of Ilco as being a
little stingy (tho I don't remember why). I seem to recall trying to
get an electronic form of the Ilco guide (after getting one from their
competitor, Jet) and not finding such an offering.
The third source is a publisher of manuals somewhat akin to auto repair
manuals, but these specific to locksmiths. The kee reference guides
made by the kee makers only give enuf detail to sell their products,
telling you which of their products you need for which cars. The
manuals go into _far_ more detail than just kee and lock references.
The Ilco guide is about 88 pages. The manuals (one for domestic and the
other for foreign) come to about 1100 similar sized pages.
But then again, it's not just three sources. Each year, the information
in the manual or guide may change. Wrong information may be corrected.
Right information can be messed up. So each year of the manuals are
separate sources. Additionally, Ilco stops (in most cases) at about 20
years. Most cars older than 20 years cannot be found in Ilco. So
that's where it pays off the most to keep the older Ilco booklets.
I have seen mistakes in each source. And one of the things I'd like to
have in my database is a list of things that I have personally confirmed
to be either true or false. Just by having my own line that basically
is a duplicate of the others would be confirmation. Each line would
show the source of its info. If the source is myself, then I will trust
it more (obviously). If any line has nothing from me, then I will
compare the sources and hopefully be able to see thru the errors.
I wouldn't mind trying to make a buck or two off of the finished
product, but my main motivation is to have reliable information for
myself to work with. My secondary reason is to become familiar with
modern database construction (frontend more than backend). A nice plus
would be if I could profit from my efforts and help other locksmiths in
the process. I'm fairly certain that if I could make a more reliable
guide, other locksmiths would be interested. Word of mouth would
probably be my best advertisement. Or maybe no one would care to pay a
couple bucks for an accurate guide when they can get for free one that
is _usually_ right. Personally, I'd rather have one that does not
change previously printed information unless it is for correction.
as substitutes. Other than this one car, all but 2 of the others list
both the H54 and H66 as substitutes. Two cars list only the H54 as an
acceptable substitute, but probably the H66 will work too (in other
words, the exclusion of the H66 on those two lines was probably a typo).
All substitutes are nearly identical to the first kee in the tail of the
kee. Most differences in substitutes are in the head or neck of the
kee. The H62 has a rather large head and probably could not substitute
for any other kee. On some, the neck is not as long and would prevent
them from substituting in for other kees. The H54 can substitute in for
several others (including the H62) because it has a long neck and a
relatively small head.
There are 25 entries that call for the H67. One of these lists the H54,
H62, and H55 as substitutes. Two list only the H54 as a substitute.
The other 22 list the H54 and the H66 as substitutes.
For my concerns, I don't care that one car lists different alternates
than all the others. My main concern is that each primary just show a
list of possible alternates. Maybe I will add a field to show how many
times a particular alternate is listed for a specific primary. But I
don't feel the need to track which car calls for which alternates. The
book I get the information from often has information changing from one
book: Some kind of 3rd party compendium? A trade tool, maybe.
Well, more a booklet. Think about 44 pages of 11x17 folded in half,
making 88 pages of 8.5x11.
year to the next, and *not* necessarily for corrections. I think this
book is manually typed each year...
heh! people still do that? =-O
It's quite clear that they do. The types of mistakes that show up do
not appear to leave any other possibility, unless their software is just
super shitty.
.. Information that is correct one year
can be wrong the next. I only want my database to show which primary
kee the car calls for. And my database will have info from different
years of the books to help compensate for the errors. I may even add
another field to indicate U, C, or W for Unconfirmed, confirmed Correct,
confirmed Wrong, and possibly even V for Varies.
Just a thought: it may be much easiest to do this in 2 stages.
1. assume there are no such conflicts to worry about,
(but at least reserving the thought there will be)
2. extend (1) to accommodate those conflicts
Well, what I had in mind was this: One line from Ilco's 2007 booklet
(if available to me), one line from Jet's 2007 booklet (if available),
one line from AutoSmart's 2007 manual (if available), and possibly one
line from me if I verify information, whether such verification agrees
or with the rest or not. And this process for each item from the three
(or four) sources. And this process for each of the older booklets (or
manual) from each source. I could even contradict myself if I discover
that one line item is sometimes one way and sometimes another. (And
believe me, that happens too.)
On Ford, the H62 was only used on the Escort, and even there, only from
1991 thru 1996. The H54 is the only listed substitute.
Only Ford (not including transponder kee entries):
Entries Primary Substitutes
2 H70
25 H75
22 H67 H54 H66
10 H51 H53
36 H50 H52
2 H78
2 H71
1 H67 H54 H62 H55
4 H54 H60
1 H76
1 H62 H54
1 H53 H51
1 H52 H50
2 H59 MZ16
2 H65 MZ27
2 H66 H54
1 S1186TS
10 H54 H60 H67
1 1185T-P H54
1 S1185T-P H50
1 H55 H54
2 RV4
2 62FT
1 WB2
2 MZ5 MZ10
1 MZ9
1 MZ4
2 H67 H54
Or, put another way:
Primary Substitutes
H50 H52*36
H51 H53*10
H52 H50*1
H53 H51*1
H54 H60*14
H54 H67*10
H55 H54*1
H59 MZ16*2
H62 H54*1
H65 MZ27*2
H66 H54*2
H67 H54*24
H67 H55*1
H67 H62*1
H67 H66*22
MZ5 MZ10*2
1185T-P H54*1
S1185T-P H50*1
Now, the ones that only show 1 occurrence in this final list, I'm not so
sure that I trust. For example, the only occurrence of the H52 being
replaced by the H50 is more likely to have been a typo in the book, and
that car *actually* call for the H50 and replaceable by the H52. Some
of the other probable typos are less obvious.
Essentially what I want to do is to make my own automobile kee reference
manual, first in database form, then printed. I want to create a
database that contains all the information from all the manuals that I
have. Basically, each line from the manual in my hand will be
identified in my database as having come from the 2007 manual...
the 2007 manual? the 2007 issue of the trade book mentioned above?
I'm not altogether clear about the term "trade book", but I think I got
it. It is a booklet that probably has very little use outside of my
trade. So, yes.
The one to which I refer here is the Ilco 2007 Auto Truck Key Blank
Reference.
.. I will
probably end up duplicating each line and correcting the info, and call
the new line my own. And when I print it out, I will print only *my*
lines.
When entering the data, I don't want to have to type in 1184FD/H54 (the
full description of the kee) each time and increase the chance of
introducing typos (which is obviously how the makers of this manual are
doing it). And seeing as how the H50 is listed as the kee of choice in
30 different entries in Ford alone, I think that kees would be a good
candidate for their own lookup table.
When it comes down to it, I want to have a table where I've already
entered all the kees, another table with all the transponder info,
transponder info comes from another reference?
Is this uniquely determined by car, maybe?
(make, model, year, [sub-series])
Sort of. One transponder kee specifically is the H72-PT. This is used by:
1997 Cobra (some)
(According to the Ilco guide, it seems that Cobra was not made after
1997. In 1996, some Cobras were fitted with locks that used the H67 on
code series A-B-C-D-E, and other Cobras got locks that used the H75 on
code series 1X-1706X. From 1994-1995, the Cobra got locks with only the
H67 (on same series). And from 1981-1993, the Cobra got Ignitions with
the H51 on series FA0-FA1863 and Door/Trunk/GB locks with the H50 on
series FB0-FB1863. [1])
1998-2002 Crown Victoria (some)
1998-2003 LTD (all)
1998-2004 Mustang (some)
1996-1997 Mustang (some)
(I don't know why the two above were separated, but this seems to
suggest that there must have been *something* different between them,
although the booklet does not show the difference. This seems to me to
indicate that the booklet has some sort of mistake here. Strictly going
by the info given, these two line items should only be one, namely, from
1996-2004.)
1998-1999 Taurus SHO & LS (some)
1996-1997 Taurus SHO & LS (some)
(Although it is not obvious from the limited info shown here, these two
line items *are* indeed different in the other info given and so _do_
deserve to be two separate line items. Specifically, they differ in how
the transponder is programmed into the car's computer. That, and the
1996-1996 Taurus had the transponder equipment as an option. Some
models made during those years did not have transponder equipment.
Others did. In 1998-1999, the transponder stuff was in all of them
(according to the 2007 Ilco reference guide).)
1998-1999 Taurus LX (some)
1996-1997 Taurus LX (some)
(These two different also.)
([1] This is one example of a line that goes back further than 20
years, but only because the latest use of this data was still _within_
the last 20 years. Ilco's reasoning is that very few cars on the road
are older than 20 years.)
And that is only the _Ford_ *cars* that use that transponder kee.
Several of the Ford trucks use it too, as well as several other car
makers (Mercury, Lincoln, and some Japanese).
Many other transponder kees may have been used on various cars. Even
most the ones I listed above did not have only the H72-PT. Some had
that one, others had the H72-EK, and possibly something else. I don't
know enuf about the differences between these two to be able to tell you
whether one car required one while another car of the same model
required the other, or if the only difference is that either car can
take either transponder kee and that the only difference is the tools
required to program the car's computer to accept it. There are *many*
complexities. This description barely scratches the surface.
Some of the very basic transponder info comes in the kee guides. They
tell you what tools are required for a given transponder kee with the
car listed (usually the same as other cars that use the same transponder
kee, but not always). And the guides also tell some other basic info
regarding transponder programming.
another table with all the substitutes, another table with all the
notes, another with car makers, another with car models, another with
lock applications, and another with code series, all enterable with a
combo box, one that will show possible matches based upon the first few
letters I type, but allowing a new entry if it matches no others.
OK the UI is a separate (and significant) question but I like the
thought of thinking in advance how the database ought to support your
access requirements.
Well, thanks. I'm no expert in databases, not by any means. But I'm no
novice either. When I improved the program used by my work (in a
computer repair shop) for work orders and invoicing and such, I learned
a lot (starting from nothing but a decent knowledge of BASIC). The
database program itself that I started with was written by a guy who
wrote programs for a living. After I finished, he looked over what I
had done and told me that I had what it took to do it too. I had no
idea how to even get started. I liked the idea. I still do. But even
back then, FoxBase was *soooooo* outdated (even tho many of the old
FoxBase programs, even now, are still out there and still running).
They work. So it's unlikely that any of them would need to be updated
or fixed. But if I were to run across any of them, I could manage
without much problem. The program I started with ended up about 3 times
bigger (which the original author pointed out, who then conceded that
storage devices had increased exponentially), mostly because of
enhancements that just made the program easier to use. But I introduced
several new things too. Also, the number of tables and indexes
increased by about 3 times, but this allowed the space actually used by
the data to *shrink* in size overall. And the data took up far more
space than the program. Overall, the space actually used by the whole
thing shrunk down to about half (which I pointed out to the original
author). There were a lot of empty fields in many of the records. I
separated some of those off to a different table that was only used by
the records that had that info.
But all that being said, that was many many moons ago. I remember most
of it. The book I referenced said that the most important part of
programming in a database language was to give a lot of thought to the
layout of the tables needed. It said that making even small changes in
the tables can require major changes in the program, which I can affirm
was most certainly accurate. So now I always think in terms of trying
to break the data down into tables appropriate to it. I was a little
surprised when I realized that just about every item (field) in this
project should be in it's own lookup table because of its repetitive
nature. The easiest example being perhaps car makers. There are only
about 66 of them (presently). So for each line item, each record that I
enter, when I come to this field, there are only about 66 to choose
from. And I don't want to have typos, which are much more likely if I
type this info from scratch every time. And proofreading takes a lot of
time, especially with something as dry as this stuff. But if I make a
typo in the auto makers table, once discovered, it only takes *one*
correction to correct it everywhere. If I type Chevrolet or Daihatsu or
Hyundai enuf times, I'm sure to end up with several different variations
for each. This is especially true when we get to the kee numbers and
other info equally as mundane and dry.
But the main table is where they are all connected. The main table will
have:
an entry for the year (of the manual)
a combo box for the brand of the manual (currently only 3)
a combo box for the model (currently over 700)
a combo box for the car maker (currently under 100)
You don't actually have combo boxes in the database, at least not in
relational databases. You're talking about the UI, I believe.
Well, yeah, I guess so. I started this thread in regards to ooBase.
I've been dabbling with it and it has the ability to do combo boxes,
which is appealing. But even in FoxBase, I *made* something like combo
boxes for ease of use. It was interesting. The database program was
made up of modules, each with their own function. I made one *just* for
use as a multi-purpose combo box (to simplify it). This module ended up
being called from several other modules, and sometimes by various parts
within a given module.
If I can construct a useful database with a decent UI, great! That's
what I want. If I have to get into the guts of the programming
instructions, then I'm sure that I will have MUCH to learn.
an entry for the year from
an entry for the year thru
a combo box for the lock application (currently under 100)
lock application? meaning
On most cars that use the H75 (for example), the car (when it left the
factory) uses the same kee on "All" the locks of the vehicle. Some of
these had a "Valet" kee.
On cars that used the H50, most of them used that kee only for the
"Door/Trunk" or "Door/Trunk/GB" or "Trunk/GB" or "GB". On these
vehicles, a different kee was used for the "Ignition" or "Ignition/Door"
or "Ignition/Hatch", etc. There are roughly 20-some variations (IIRC)
of "lock application" used.
a combo box for the code series (currently about 100)
series is a disjoint classification of kees, maybe?
(Really getting much further into detail than I expected to go. :) )
Not exactly sure what you mean. (I'm not very keen on the word "disjoint".)
If I understand correctly, it's not a disjoint classification of the
kees themselves, but rather a disjoint classification of the combination
of the cuts on the kees.
A given kee could be used with more than one code series (not usually
the case, but occasionally so). And a given code series often applied
to many different kees (usually the case).
A code series was used by the car maker to have a list of kee cuts that
were not "plaintext", so-to-speak. A1 could be 33542 [2], where A2
could be 53421, ..., K9722 would be 31245, all of which are used on kee
XZ123. They could stamp the code right onto the lock (and some did) and
anyone reading the code would not know how to convert that to a useful
kee unless they had access to the code list. The one I just made up
here would be written as code series A1-K9722 and it would apply to
certain locks (and their appropriate kee) of certain models of certain
makers of certain years. One of the actual code series for Ford (for
the H75 kee and others) is 1X-1706X. It appears (just by looking at
Ford listings only) that H75 does not seem to use any other code
series. But I have seen other kees that do use multiple code series.
[2] In a given code series, there are certain specifications on the
cuts, the spacing from one to the next and the increment from one depth
to the next. The kees used with the series have physical limitations.
Most car kees have a maximum of 4 different depths, usually starting
with depth number 1. Some have only 3 depths. Some have as many as 6
depths. The cuts I listed could belong to either the 5 or 6 depth
categories. But 33542 means that in the first space (the first spot
designated for a cut) the kee is to be cut to the depth identified as a
3. The second space is also to be a 3, the third a 5, the fourth a 4,
and the last one a 2. There are many combinations, especially when you
use the same combinations on different kees that cannot even go into the
locks belonging to the first kee or each other. [3]
[3] I realize that footnotes are supposed to be at the very bottom.
But I think the only thing that really needs to happen is that the
footnote be separated off _just_ far enuf so as to not break the flow of
the sentence that would have been too complex and unwieldy had the
footnote been left in place of the number asking you to look elsewhere
for other applicable info.
a combo box for the kee of choice (currently ???)
a combo box for substitutes (fewer than the kee of choice)
a combo box for transponder info (under 100?)
a combo box for the notes. (under 100?)
If the car model is filled in first, then most of the time the car maker
will only have one choice, but could have as many as three (currently).
Most lines have zero substitutes, some have only one, but some have more
than one. Same thing goes for the transponder info and the notes.
0-or-more is easy to do in relational databases.
Relational database is essentially what I did years ago in FoxBase, tho
the "relations" were done with lines of code that connected them. That
was challenging. It was fun in a way, but I cannot say that I would
like to get that far into the guts of the code if I don't have to.
In other words, I think I have at least a basic, rudimentary
understanding of "relational".
Most of the info in the manual is repetitious, but uniquely combined on
each line for a different lock and kee application. For example, the
Ranger has 17 lines, each one unique from the others in some way. In
other words, each field has a small number of possible entries (other
than car models and probably kees). But no two records will be entirely
the same. There are approximately 2,000 records for each manual. And
most of the information that is possible in each of the fields will be
identical from one manual to another. I'm thinking that each field
should have it's own table, except the years (because of how simple they
are, like entering 4 digits should be easier that scrolling thru a list).
I don't think this is all that simple. But then again, I've never
embarked upon anything quite like it.
Later..
..jim
Thanks jim. This email was a little exhausting, but fun. I *am*
enjoying this.
--
Ralph
--------------------
If we didn't have ambiguity in the language we would not have good crossword
puzzles.
--carl lowenstein
--
[email protected]
http://www.kernel-panic.org/cgi-bin/mailman/listinfo/kplug-lpsg
