Thoughts on manual database design?

[Jay Jaeger]

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
>>>>
>>>
>>>
> 
>