Thoughts on manual database design?

[Jay Jaeger]

There are three columns named Location for a reason.

There is the column Location in a table all by itself.  That is a list
of locations - not just for manuals, but, eventually, for all of my
artifacts.

Then there is a table of Cabinets.  Each Cabinet has a single location
at any given time.  But Cabinet by itself may not be unique, so Location
comes along for the ride as part of the key for the table of cabinets.

Manual artifacts (copies) are stored in Cabinets.  Yes, I *could* have
created a separate key for each Cabinet, and stored that in the Manual
table and the Cabinet table (like I did to relate Manuals to the other
tables), but that would have actually complicated the design, so instead
I used the same concatenation which is the key to the Cabinet table.

The database is defined such that Location is a foreign key in Cabinet,
and the keys to Cabinet (tee hee) are a foreign key in Manual_Artifact.
 This allows the database to *guarantee* that there is not any Manual
whose Cabinet does not exist or a Cabinet whose Location does not exist.
 It also allows the web applications  to easily populate pull down lists
without having to read through the entire artifacts table.  With a
database this small that probably doesn't matter much, but if the table
had millions of rows it certainly would.

Type and manufacturer are handled the same way for the same reason.

I could have made a more relationally pure design by creating a separate
table of Artifacts and Cabinets, Cabinets and Locations and so on.  But
because each of those relationships is just one to many and never many
to many, there was no point in my mind (purists would probably
disagree).  Not so for manuals and machines - many manuals may apply to
a given machine, and a given manual may apply to many machines, so that
had relation to be stored in a separate table.

Historial digression....

I have been doing database design essentially like this from *before*
relational databases were well known and commercially available, at
Wisconsin DOT, which developed its own database system called File
Handler in the early 1970's, starting on an IBM 360/65 MP with 2MB of
core.  It was written because the other DBMS's at the time were either
too slow, to big or required taking too much of the database too often
for reorganizations.  (IBM's IMS, in particular).  DB2 did not yet
exist.  Huge gamble management took on the programmers that wrote it,
which paid off in millions of dollars saved in computer capacity alone.
 For a while, for performance reasons, we had a "cheat" that could store
a one to many relation in a single column (which we called a "repeating
group") that was done for our drivers database.   We broke them out into
separate tables when we upgraded to an Amdahl 470/V6 in 1976.

File Handler production before I started there in 1975, I was the
primary DBA for it for about 7 years before I moved on to other things.
 It had features like row-level blank compression, elimination of nulls
at the column level (a bitmap indicated which columns were present), an
API which had a LALR compiler which parsed queries (though for online
production we required them to be pre-compiled), full (single phase)
commit with preempt detection including redundant log and checkpoint
files in case the machine went down mid-commit and so on.  It used
techniques for indexing that would be recognizable today.  It was almost
its own OS: by the time we were done it had (in order of development)
its own memory management ("KORMAN" aka "Harvey"), task management and
program loading/content management ("CONMAN").  It supported the SMP
fully, though applications were usually written to be single-threaded in
a given serially-reusable application instance - though you could have
multiple instances of the same application running.  A man named Robert
Tomlinson wrote the query compiler, and  used some of his work on File
Handler for his advanced degree theses (at least his PhD, for certain).
 I was an EE student at the same time he was at U. Wisconsin, though our
paths never crossed.

Florida DOT acquired the code from us in the late 1970's, and
established it, with some assistance from Wisconsin DOT staff, as their
motor vehicle and/or driver database system for many years as well.  I
have a copy of that instance of the code, and have run the thing under
Hercules, just for giggles.  Wisconsin DOT retired the last vestige of
it just last year - it had a 40 year run, all told.

JRJ

On 10/2/2015 1:38 PM, Mike Stein wrote:
> Is that the way it's done these days, e.g. the contents of the Location
> field in three places, Location and Manual_Type only containing one
> field, no keys other than Manual_Key etc.?
> 
> Looks like I'll have to brush up on database design... ;-)
> 
> m
> 
> 
> ----- Original Message ----- From: "Jay Jaeger" <cube1 at charter.net>
> To: <cctalk at classiccmp.org>
> Sent: Friday, October 02, 2015 11:59 AM
> Subject: Re: Thoughts on manual database design?
> 
> 
>> On 10/2/2015 12:04 AM, william degnan wrote:
>>> Coming up with a schema that works with multiple manufacturers is the
>>> big
>>> challenge.
>>>
>>
>> Not sure it is that big a challenge.  Perfection is not required.  Just
>> the ability to find stuff later.  My schema currently has manual
>> manufacturer - the original manufacturer of the machine, and then each
>> artifact (copy of a manual) has a publisher.
>>
>> Consider the case of Apollo which got bought by HP.
>>
>> For a DNxxxx machine, the machine manufacturer is always Apollo.  For a
>> 400 or 700 series, the manufacturer is always HP.  However a given copy
>> of a manual may have been published by Apollo (older) or HP (newer) -
>> with the very same number.  The schema supports that.
>>
>> (New schema posted at
>> http://webpages.charter.net/thecomputercollection/misc/manualmodel.pdf
>> ) .
>>
>> JRJ
>>
> 
>