Thoughts on manual database design?
Mike Stein
mhs.stein at gmail.com
Fri Oct 2 19:25:44 CDT 2015
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
>>>>>
>>>>
>>>>
>>
>>
More information about the cctech
mailing list