Thoughts on manual database design?

Mike Stein mhs.stein at
Fri Oct 2 17:36:29 CDT 2015

I'm just surprised that it looks like the 
names/descriptions are the key; what if you 
repaint the BLUE cabinet in your wife MARY'S 
BEDROOM red, or for that matter if you remarry and 

Does the software update all occurrences 
automatically, or am I misunderstanding?


----- Original Message ----- 
From: "Jay Jaeger" <cube1 at>
To: <cctalk at>
Sent: Friday, October 02, 2015 5:06 PM
Subject: Re: Thoughts on manual database design?

> 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.
> 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>
>> To: <cctalk at>
>> 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
>>> ) .
>>> JRJ

More information about the cctalk mailing list