Thoughts on manual database design?

[Mike Stein]

Ah, OK; not really relational then, but as long as 
it works reliably...

Yeah, you may not remarry or repaint a cabinet but 
it's quite possible that you'll move one...

Doesn't your software provide for hiding the auto 
keys except during maintenance?

No provision for multiple (changed) publishers so 
a given manual could show up under both Compaq or 
HP, for example?

m

----- Original Message ----- 
From: "Jay Jaeger" <cube1 at charter.net>Ah, OK
To: <cctalk at classiccmp.org>
Sent: Friday, October 02, 2015 6:53 PM
Subject: Re: Thoughts on manual database design?


> The descriptions are not the keys to any table, 
> but the names sometimes
> are, if there hasn't been a reason to use a 
> different key.
>
> For example:
>
> Location is the key to the Location table.
>
> Location . Cabinet is the key to the Cabinet 
> table - cabinets are not
> necessarily unique unto themselves but are 
> unique within a location.
> (Well, actually, I plan to make them globally 
> unique, but didn't want to
> design the database to require it).
>
> ArtifactID is the key to the Manual_Artifact 
> table.
>   Location . Cabinet are columns in the 
> Manual_Artifact table.
>   They are also foriegn keys (i.e., keys to the 
> Cabinet table).
>
> If I cared about the color of a cabinet, that 
> would be a column in the
> cabinet table.  The artifact would not care what 
> color the cabinet was.
>  Color would not be a key.
>
> If a cabinet got renamed, then the Artifacts 
> would have to change as
> well.  (This is why purists would suggest adding 
> a separate numeric key
> to the cabinet table.  I am not worried about 
> that happening - there
> isn't any reason, really, to rename a cabinet.).
>
> HOWEVER, if a cabinet got moved then the 
> Location in both the Cabinet
> and the Manual_Artifact table would have to 
> change.  That is actually
> plausible (as a "Box" is one kind of cabinet - 
> and those *do* move), so
> this gives me some reason to add such an 
> invented key that I had not
> thought of before, so I think I will make that 
> change to add a
> autoincrement key to the cabinet table.
>
> I don't like to invent these separate 
> autoincrement keys without a good
> reason - but am perfectly happy to if I find 
> such a reason.
>
> No, there is no magic with respect to the 
> database doing updates.
>
> JRJ
>
> On 10/2/2015 5:36 PM, Mike Stein wrote:
>> 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 
>> MARY'S BEDROOM becomes
>> LINDA'S BEDROOM?
>>
>> Does the software update all occurrences 
>> automatically, or am I
>> misunderstanding?
>>
>> m
>>
>> ----- Original Message ----- From: "Jay Jaeger" 
>> <cube1 at charter.net>
>> To: <cctalk at classiccmp.org>
>> 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.
>>>
>>> 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
>>>>>
>>>>
>>>>
>>
>>