Thoughts on manual database design?
cube1 at charter.net
Wed Sep 23 09:17:12 CDT 2015
On 9/23/2015 1:38 AM, steven at malikoff.com wrote:
> Instead of cooking up another completely custom inventory management schema, you might consider adhering to Dublin Core for some more modern
> schema conventions:
Slightly more useful than MARC, but really it comes down to just a field
Relatively few of my fields are present there:
Title, Publisher, Creator (what I called Machine_MFG), Date (though
their definition is really really vague), identifier (my Manual_Number),
Their type would just be "Text" or "StillImage" for all of my stuff -
not sufficiently specific for my uses.
Subject is only *roughly* equivalent to my Machine table, and would not
do the job I need done with it.
I don't plan on a lengthy description. No way I am going to enter that
> For the dbms engine I would use MariaDB over MySql (but with some of the MySQL tool chain) as I prefer code to remain outside the clutches of Oracle:
> It's a drop-in replacement, runs nicely and the HeidiSQL management client is very useable.
I certainly understand that point of view. However, that really isn't
an issue for me, and MySQL is presumably better supported by hosting
> Also, why are you restricting your field lengths so much? This is the 21st century, you can afford to be a bit more generous on those VARCHARs :)
Probably. On the other hand, really easy to make them longer, too,
should that prove necessary - this isn't a 100 million row table. And
the fields are already longer, in most cases, than I would want to
actually display and longer than the fields I currently have - which
have already been lengthened as needed.
> Final thing, are you going for a completely normalised database? (ok, I haven't analysed your schema to any great extent)
THANKS FOR ASKING THAT QUESTION. Well I thought I had done a decent job
of practical normalization, but clearly missed some things that I
noticed as a result of your question.
First, I certainly should pull out the File and Online fields into a
separate table because as is they could result in
redundancy/inconsistency between multiple copies in my inventory.
Pulling them into a separate table will remove the redundancy, and also
allow for the eventual possibility of multiple online sources for a
Fields: Machine_MFG(K), Manual_Number(K), OnlineLocation(K)*,
(Filename goes away - it is just an instance of "OnlineLocation")
(*) - removed from original Manual table.
Theoretically, I should separate the copies into a separate copy table,
keyed by artifact ID, but it wouldn't really buy me much, as I almost
always store multiple copies of a manual in the same place. Still it
probably would be the way to go:
Fields: ArtifactID(K), Location*, Cabinet*, Drawer*, InventoryDate*,
Missing*, Original*, Incomplete*, Publisher*, Date*
I think I will add a field "Marked" - Boolean - true if an artifact
has been marked with its ArtifactID.
I *might* also yank Publisher and Date into this table. Have to
think about that, but with the possibility of multiple printings,
the Date almost certainly belongs here. I suppose one could argue
the same for Publisher. AH YES! HP and Apollo - different
publishers for the exact same manual.
So, yes, these need to move too.
So now, table Manual just has the following:
Machine_MFG(K), Manual_Number(K), Title, Type
And I need a relationship table to bind the two together:
ArtifactID(K), Machine_MFG, Manual_Number
> ---------------------------- Original Message ----------------------------
> Subject: Thoughts on manual database design?
> From: "Jay Jaeger" <cube1 at charter.net>
> Date: Wed, September 23, 2015 12:00 pm
> To: cctalk at classiccmp.org
>> So, I am looking to convert my old Access database I have used for many
>> years to a MySQL database, with the expectation that I will eventually
>> publish it on a web page for public lookup.
>> Below is my first cut at a database design for it. I'd be happy for
>> comments and suggestions, to the list or directly. Note, however, that
>> I don't expect to do this to the same level of complexity and
>> completeness that one might if they were the library of congress, a
>> major university library, etc. So, some suggestions to generalize may
>> be quietly ignored, even if they would in principle, be the "right
>> thing" according to some criteria or other.
>> This would be used for manuals, per se, print sets, etc. Quite possibly
>> for books as well.
>> What follows amounts to a data dictionary:
>> Table MANUAL:
>> This is the primary table of manuals. Many of the fields will be
>> available for string and/or pull down search, as appropriate.
>> Machine_MFG: VARCHAR(32)
>> The manufacturer of the machine(s) to which the manual applies
>> Part of unique key.
>> Pull down search.
>> Manual_Number: VARCHAR(40)
>> The manual number, including version strings, etc.
>> In some cases, this may be an SBN or ISBN.
>> Part of unique key
>> String search.
>> Artifact_ID: CHAR(16) [format/type still under consideration]
>> The local identifier for the manual/artifact.
>> Part of unique key.
>> Will be generated if none is entered during creation/update.
>> (Required because I may have more than one copy of a given manual).
>> Publisher: VARCHAR(32)
>> The publisher of the manual. (Typically will be the same as the
>> Machine MFG - but not necessarily always)
>> May not be NULL.
>> Type: VARCHAR(16)
>> Manual, Drawing (== printset, schematic, etc.), Book, ...
>> Pull down search.
>> Original: BOOLEAN
>> True if an original manual. False if a copy ("Xerox").
>> Missing: BOOLEAN
>> True if the manual is missing (i.e., is not where it is supposed to
>> be, and the actual location is not known).
>> Title: VARCHAR(255)
>> The title of the manual, including any subtitles.
>> Suggest "; " to separate multiple titles/subtitles
>> May not be NULL
>> String search.
>> Incomplete: CHAR(1)
>> Indicates if the manual is not a complete copy.
>> NULL indicates the copy is complete.
>> Year: DATE
>> The year the manual was published / printed / copyrighted.
>> May be NULL, signifying the date is unknown / not entered.
>> Location: VARCHAR(20)
>> The general location of the manual in my inventory
>> May be NULL, signifying the manual is not in my inventory.
>> Cabinet: CHAR(2)
>> The ID of the cabinet or box in which the manual is stored, if any.
>> May be NULL, even if the manual is in my inventory.
>> May not be NULL if Drawer is not NULL.
>> Drawer: CHAR(2)
>> The ID of the drawer of the cabinet in which the manual is stored,
>> if any. May be NULL.
>> Inventory_Date: DATE
>> The date that the manual was last inventoried.
>> Filename: VARCHAR(255)
>> The name of the file which is an image of the manual in my local
>> file storage.
>> OnlineLocation: VARCHAR(255)
>> URL of the manual located online (e.g., the bitsavers URL)
>> May be NULL. This does NOT mean that the manual is not online.
>> Of course, this field can become out of date.
>> OnlineVersion: CHAR(1)
>> = means that the online manual is the same version as this one
>> > means that there is no equal version online, but there is a
>> later version
>> < means there is no equal or later version online, but there is an
>> earlier version.
>> OnlineMD5: CHAR(32)
>> MD5 hash of online manual - used to check for moved manuals. ;)
>> TABLE MachineManual
>> This table cross references manuals to the machine(s) to which they
>> apply. This list will NOT be guaranteed to be complete. During inquiry
>> and update, this will be a list of machines with a separator character
>> in between which will not be allowed in Machine_MFG. During an update,
>> the entire list will be replaced with the first one being flagged as the
>> primary machine.
>> MachineMFG: (See above). Part of the unique key for this table.
>> Manual_Number: (See above) Part of the unique key for this table.
>> Machine: VARCHAR(16)
>> One of the machine(s) to which this manual applies.
>> Part of the unique key for this table.
>> Primary_Machine: BOOLEAN
>> When true, indicates that this machine should be listed first
>> in the list of applicable machines. For a peripheral, this will
>> typically be the machine for which the peripheral was originally
>> So a list of machines for an RX01 manual might be:
>> PDP-11, RX01, RX01, VAX-11
>> TABLE Machines
>> A lookup table of valid machines. Only column is Machine (See above).
>> Naturally this table will change over time - I anticipate that an update
>> where the updater (me) keys in an "invalid" one will be greeted with
>> some kind of confirmation panel.
>> TABLE Manufacturers
>> A lookup table of valid manufacturers. Only column is Machine_MFG
>> (See above). Naturally this table will change over time - I anticipate
>> that an update where the updater (me) keys in an "invalid" one will be
>> greeted with some kind of confirmation panel.
>> Table Types
>> A lookup table of valid Types. Only column is Type (See above)
>> TABLE: LastGeneratedArtifact
>> The last generated artifact ID - used to assign new artifact numbers
>> when one is not manually entered. Algorithm TBD. One column:
>> Artifact_ID (See above)
>> TABLE: Locations
>> A table of valid locations (e.g. Basement, Garage, STORAGE, Unknown, etc.)
>> Used for convenience during data entry.
>> (Once this one is done and running (at least locally), the next one is
>> probably media (tapes, floppies, etc.) which is not too bad, and then
>> machines, cards and parts, which is more complicated for lots of reasons).
>> After those are done (hopefully by the end of the year - I am retired
>> and this will be my primary focus for a while) comes the design for a
>> database for SMS cards and for the IBM 1410 use of SMS cards.
More information about the cctech