James G. Sack (jim) wrote:
Ralph Shumaker wrote:
All this talk of perl tutorials (on the main list) moves me to ask a
question about tutorials for Base (Open Office).
I'm not sure if I want to buy a manual at this point because I don't
know yet if I want to really get into it beyond my current need.
So far, the tutorials I have found online are not that great. The one I
decided to try first was written by someone who seems to be only about
one or two levels above me. That would be all right except that he has
aimed his tutorials at people who are many levels below, people who are
*completely* green. _AND_ he diverges often.
Can anyone point me to good tutorials that assume at least a basic
understanding of databases?
One thing I'd like to do may not be doable, at least not the way I
imagine it. One small part of what I'd like to do is this: I will have
a table of keys for cars. (Since key means somethings else in database
context, I will refer to the brass kind as "kee".) Some of these kees
(k1, k2, k3) have substitutes (k1a, k1b, k2a, k3a, k3b, k3c) that can be
used. Some of the substitutes (k1b, k3a, k3c) have secondary
substitutes. Some of those secondary substitutes are the original kee
(k1, k3). If a car requires k1, I want to show that it could also take
k1a or k1b. If another car calls for k3a, I want to show that it could
also take k3, and by extension, k3b and k3c. I would prefer to *not*
have two separate tables with data entered twice (increasing the risk of
typos, and preventing corrections in one list from propagating to the
other). But the only kind of referencing I can think of ends up being
circular since I don't know of a way to get one record to point to
another record as an acceptable substitute. It's complicated. It's not
a one to one relationship either, which complicates it further. It
would be nice to have a second table which has each record linked
table-1
---------
rec kee
num info
---------
0 k1
1 k2
2 k3
3 k4
4 k1a
5 k1b
6 k2a
7 k3a
8 k3b
9 k3c
table-2
-------------
rec pri sec
num kee kee
-------------
0 0 4
1 0 5
2 1 6
3 2 7
4 2 8
5 2 9
6 5 0
7 7 2
8 9 2
Line 3 of table-1 isn't even referenced in table-2 because it has no
substitutes.
That's only a small part of what I want to do.
I have some knowledge of database work, but not graphical. I worked in
the guts of FoxBasePlus (not Pro). I took a program for work and
tripled its size (or more) by taking away the ugly stuff, adding
features, and making it easier to work with. I had *zero* knowledge of
database programming before that. But good knowledge of BASIC gave me a
*huge* leg up on it because of similarity in terms and function. I only
had to learn the stuff that was *not* similar, and most of that was
self-explanatory from the context in the old program. And the rest I
learned from other sources.
I haven't the slightest acquaintance with OpenOffice database capabilities.
..BUT..
Database design questions are pretty fun!
In your example, it's not quite clear what the meaning or significance
of original and substitute really is.
What I see is kees and cars (kars?) where any given car has zero-or-more
associated kees.
Actually, one-or-more (even if the only one is "Dealer Key Only" or
"Restricted Key").
kee car
--- ---
k1 bluecar
k1a bluecar
k1b bluecar
k2 greencar
k2a greencar
k2b greencar
<NULL> batmobile
probably a custom kee for the Batmobile, since everything else on it is
custom.
...
etc
You presumably want to describe something more. Can you elaborate?
Perhaps it would help to pose particular questions you want the database
to be able to answer.
What kees mate with car=bluecar?
Regards,
..jim
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
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
year to the next, and *not* necessarily for corrections. I think this
book is manually typed each year. 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.
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. 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,
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.
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)
an entry for the year from
an entry for the year thru
a combo box for the lock application (currently under 100)
a combo box for the code series (currently about 100)
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.
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.
--
Ralph
--------------------
How do you test an uncooperative intelligence when it's smarter than you?
--Stewart Stremler
--
[email protected]
http://www.kernel-panic.org/cgi-bin/mailman/listinfo/kplug-lpsg
