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" <cu...@charter.net> > To: <cctalk@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" <cu...@charter.net> >>> To: <cctalk@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 >>>> >>> >>> > >