{"id":138,"date":"2014-12-19T23:30:19","date_gmt":"2014-12-19T23:30:19","guid":{"rendered":"http:\/\/aboutcomics.blogwyrm.com\/?p=138"},"modified":"2014-12-19T23:30:19","modified_gmt":"2014-12-19T23:30:19","slug":"organizing-your-comic-collection-part-2","status":"publish","type":"post","link":"https:\/\/aboutcomics.blogwyrm.com\/?p=138","title":{"rendered":"Organizing Your Comic Collection – Part 2"},"content":{"rendered":"

Last week, I presented a simple schema for organizing a comic collection. \u00a0At the heart of the schema is the idea of a many-to-many relationship between publications and stories.\u00a0 That is to say that a given story can appear in many publications and a given publication may contain many stories.\u00a0 This many-to-many relationship is mapped into three tables that are \u2018linked\u2019 together in a relational database.\u00a0 The first table contains all the information about each publication.\u00a0 The second contains all the information about each story.\u00a0 The third table contains all of the relationships between the two.<\/p>\n

In this post, I am going to present my particular implementation of this schema, and talk about some of its strengths and weaknesses.\u00a0 While the tables I\u2019ll be presenting will have a lot more information in them than the basic ones presented in the last post, the basic approach remains the same.\u00a0 The additional information is provided to make the database much more useful and rich in the kinds of queries that can be made.<\/p>\n

The database (technically a relational database management system or DBMS) that I use is Microsoft Access, which usually comes with the professional MS Office Suite. \u00a0There are freeware alternatives to Access including MY SQL and LibreOffice Base, which work just as well if not better and, in my experience, moving the tables between tools is almost painless.<\/p>\n

Let\u2019s start with the Publication Table. In my implementation, each publication has 10 attributes corresponding to the 10 columns in the table, which, in the language of relational databases, are called fields.\u00a0 Each field has a name and a data type (integer, string, etc.) that specifies what kind of data it is. A record is a row in the table where a field takes on a particular value.\u00a0 The names, data types, and explanation of the fields in the Publication Table are:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n
Field Name<\/td>\nType<\/td>\nMeaning<\/td>\n<\/tr>\n
Publication<\/td>\nInteger<\/td>\nA unique publication id label.\u00a0 No two Publication values can be the same, and the specific value is assigned by the DBMS unless overridden.<\/td>\n<\/tr>\n
Publisher<\/td>\nString<\/td>\nIdentifies the publishing house (Dark Horse, DC, Marvel, etc.)<\/td>\n<\/tr>\n
Title<\/td>\nString<\/td>\nThe title of the publication, usually as it appears in the indicia.<\/td>\n<\/tr>\n
Number<\/td>\nInteger<\/td>\nThe issue number as it appears in the indicia, or the value 1 if none is given.<\/td>\n<\/tr>\n
Vol<\/td>\nInteger<\/td>\nThe publication volume as it appears in the indicia, or the value 1 is none is given.<\/td>\n<\/tr>\n
Year<\/td>\nInteger<\/td>\nThe publication year as it appears in the indicia.<\/td>\n<\/tr>\n
Type<\/td>\nString<\/td>\nLists the type of publication.\u00a0 Usual values are \u2018Comic\u2019, \u2018Trade\u2019, or \u2018Trade Paperback\u2019, \u2019Trade\/B&W\u2019, \u2018Glossy\u2019, but they can be any string desired.<\/td>\n<\/tr>\n
Condition<\/td>\nString<\/td>\nList the condition.\u00a0 I use only \u2018Good\u2019, \u2018Fair\u2019, and \u2018Coverless\u2019, but any string including CCG labels can be used.<\/td>\n<\/tr>\n
Box<\/td>\nInteger<\/td>\nIdentifies in which box the publication can be found.\u00a0 A value of 0 means on a book shelf.<\/td>\n<\/tr>\n
copies<\/td>\nInteger<\/td>\nNumber of copies.\u00a0 Occasionally I bought more than one by accident and so I needed this field.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n

The following figure shows approximately the first 30 rows of the Publication Table.<\/p>\n

\"Publications_table\"<\/a><\/p>\n

Next is the Story Table.\u00a0 A story has 13 fields, and the names, data types, and explanations of these fields are:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
Field Name<\/td>\nType<\/td>\nMeaning<\/td>\n<\/tr>\n
Story<\/td>\nInteger<\/td>\nA unique story id label.\u00a0 No two Story values can be the same, and the specific value is assigned by the DBMS unless overridden.<\/td>\n<\/tr>\n
Title<\/td>\nString<\/td>\nThe title of the story as it appears in the original story.<\/td>\n<\/tr>\n
Year<\/td>\nInteger<\/td>\nThe year that the story was originally published.<\/td>\n<\/tr>\n
Genre<\/td>\nString<\/td>\nLists the key characters as I see it in a story.\u00a0 For example, a story with both Batman and Spider-man may have the string Batman & Spider-man<\/em><\/strong> as an entry<\/td>\n<\/tr>\n
Author<\/td>\nString<\/td>\nLists the names given author credit separated by a \u2018,\u2019 or an \u2018&\u2019 for multiple entries. No distinction is made between plot and dialog.<\/td>\n<\/tr>\n
Illustrator<\/td>\nString<\/td>\nLists the names given artist credit separated by a \u2018,\u2019 or an \u2018&\u2019 for multiple entries.<\/td>\n<\/tr>\n
Inker<\/td>\nString<\/td>\nLists the names given inker or finisher credits separated by a \u2018,\u2019 or an \u2018&\u2019 for multiple entries.<\/td>\n<\/tr>\n
Original Comic Name<\/td>\nString<\/td>\nLists the publication title in which the original story was found.<\/td>\n<\/tr>\n
Original Comic Number<\/td>\nInteger<\/td>\nLists the publication number in which the original story was found.<\/td>\n<\/tr>\n
Original Comic Vol<\/td>\nInteger<\/td>\nLists the publication volume in which the original story was found.<\/td>\n<\/tr>\n
Read<\/td>\nBoolean\/Checkbox<\/td>\nYes or No value to track whether the comic has been read.<\/td>\n<\/tr>\n
Cataloged<\/td>\nBoolean\/Checkbox<\/td>\nYes or No value to track whether the comic has been completely cataloged.<\/td>\n<\/tr>\n
Variant<\/td>\nString<\/td>\nSpecial comment field to track changes in the story when reprinted.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n

The following figure shows approximately the first 30 rows of the Story Table.<\/p>\n

\"Stories_table\"<\/a><\/p>\n

The final table is the Relationship Table which tracks the many-to-many relationships that exist between the Publication and Story tables. \u00a0It has 3 fields, and the names, data types, and explanations of these fields are:<\/p>\n\n\n\n\n\n\n
Field Name<\/td>\nType<\/td>\nMeaning<\/td>\n<\/tr>\n
Entry<\/td>\nInteger<\/td>\nA unique relationship id label.\u00a0 No two relationship values can be the same, and the specific value is assigned by the DBMS unless overridden.<\/td>\n<\/tr>\n
Publication<\/td>\nInteger<\/td>\nThe publication id corresponding to the desired publication in the Publication table.<\/td>\n<\/tr>\n
Story<\/td>\nInteger<\/td>\nThe story id corresponding to the desired story in the Story table.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n

\"Relationship_table\"<\/a><\/p>\n

Data entry into the tables can be done in a variety of ways, but the easiest is to use a spreadsheet to get a portion of each table (say 100 publications or stories) just right, and then a bulk cut & paste to set the records in the Access table to have the same content.\u00a0 There are other ways for bulk data entry, but there are good web tutorials that discuss this so I won\u2019t say anymore here.\u00a0 The one table where the data entry had to be done entirely by hand is the relationship table, but Access has some nice features that allow the user when editing any of the three tables to see what the relationships are to the others.\u00a0 These data can be accessed by clicking on the \u201c+\u201d on the far left of each record.<\/p>\n

Once the tables are filled, you are ready to mine the tables for all sorts of nuggets.\u00a0 Mining data from the tables is done through a query where the user asks for all the data meeting a specified set of criteria.\u00a0 The query is done using what is called an inner join on the tables to get the desired effects, and is perhaps the hardest piece to get right, as it involves learning some of the database language known as SQL.<\/p>\n

Putting in this much structure requires some time and dedication, but when it is all done you can make a list of all the comics in your collection featuring, say, Doctor Strange.\u00a0 The figure below shows a partial list from such a query, and you can see the multiple occurrences of the \u201cBeyond the Purple Veil!\u201d story.<\/p>\n

\"Doctor_Strange_Query\"<\/a><\/p>\n

The SQL that made this happen is:<\/p>\n

SELECT [stories].[Cataloged], [stories].[Title], [stories].[Year], [stories].[Genre], [stories].[Author], [stories].[Illustrator], [stories].[Inker], [stories].[Original Comic Name], [stories].[Original Comic Number], [stories].[Original Comic Vol], [publications].[Title], [publications].[Number], [publications].[Vol], [publications].[Publication], [stories].[Story], [stories].[Variant], [publications].[Box]FROM stories INNER JOIN (publications INNER JOIN pub_story_relation ON [publications].[Publication]=[pub_story_relation].[Publication]) ON [stories].[Story]=[pub_story_relation].[Story]WHERE ((([stories].[Genre]) Like “*Doctor Strange*”) And (([publications].[Publisher])=”Marvel”))<\/p>\n

ORDER BY [stories].[Year], [stories].[Original Comic Name], [stories].[Original Comic Vol], [stories].[Original Comic Number];<\/p>\n<\/div>\n

Despite its length, it is actually relatively easy to understand.\u00a0 The first piece is the \u2018SELECT\u2019 command, which tells the query to extract data from the fields listed.\u00a0 Each field is specified by its table name and then its field name using [<table name>].[<field name>].\u00a0 So, for this query, 17 different fields were extracted and placed in the order specified from left to right (and if you count the number of columns in the above figure you find 17 of them).\u00a0 The second piece is the \u2018FROM\u2019 command that acts like a switch board to say that the data in a given row should be from a story and a publication that has relationship with each other as determined from the Relationship Table.\u00a0 The third piece is the \u2018WHERE\u2019 command, which tells the query to only return those records that have an occurrence of the string \u2018Doctor Strange\u2019 in them and which were published by Marvel. Finally, the fourth piece is the \u2018ORDER\u2019 command that sorts the records first by the year the story was published, then by the original name of the publication, volume, and number.<\/p>\n

Most of the DBMSs don\u2019t actually require you to even write SQL; the above set of commands can be constructed visually in Access using the Design View.\u00a0 It takes some practice, but an afternoon of experimentation and some web searches should do the trick.<\/p>\n

Okay, what about stories that are partially reprinted in one volume.\u00a0 My strategy is to list the story in each of the publications.\u00a0 An example of this is the Warlock Special Edition (1982) #1 which contains the stories \u201cWho is Adam Warlock?\u201d, \u201cDeath Ship!\u201d, and the first half of \u201cJudgment!\u201d from Strange Tales #178, #179, and #180, respectively.\u00a0 Running a query with Like \u201c*Doctor Strange*\u201d replaced with Like \u201c*Warlock*\u201d (note the wild cards) gives:<\/p>\n

\"Warlock_query\"<\/a><\/p>\n

This approach works, but it is a bit awkward since it isn\u2019t clear how much of \u201cJudgment!\u201d is included in one versus the other.\u00a0 I don\u2019t think that this is particularly a problem since this situation happens relatively infrequently, but there is a case to be made that the Publication Table should have a field added, maybe called \u2018Comments\u2019, where these little notes can be kept.<\/p>\n

That brings us to the \u2018Variants\u2019 field in the Story Table.\u00a0 That field was actually added well after the table was constructed, to address the following issue.\u00a0 In 1986, Marvel started reprinting the Uncanny X-Men run that started with Giant-Size X-Men #1 in 1975.\u00a0 Due to page count differences, the reprints appearing in 1986 had a variety of new pages added, and occasionally some panels or pages removed.\u00a0 Since each issue was different, the variant field is used to track the differences.\u00a0 For example, consider the story \u201cThe Doomsmith Scenario!\u201d originally printed in X-Men #94 and then reprinted with modifications in Classic X-Men #2.\u00a0 Both stories have separate entries in the story table; the original has the Story id value of 5506 and the variant a value of 8802.\u00a0 They have identical entries for all the other fields except for the Variant field where the description of the modification is placed.<\/p>\n

\"X-Men_query\"<\/a><\/p>\n

Additional queries that I\u2019ve run have been to determine what publications are in each box, or how many of the comics I have have been written by Steve Englehart or drawn by Jim Starlin.\u00a0 Basically, once the data is in the database and the relationships are established, then any mix-and-match scenario that one can imagine can be the basis of a query.<\/p>\n

I will end with a few additional comments on the weaknesses of my implementation. \u00a0\u00a0First and foremost are the names of the fields.\u00a0 It would have been better to call the label fields used as identifications something with the abbreviation \u2018Id\u2019 in them (e.g. Publication Id instead of Publication).\u00a0 This naming convention is not only a lot more precise than the one I implemented; it also prevents confusion when trying to write\/design the query.\u00a0 Second, I might have included a series field in the Publication table. \u00a0The last 10 or so years have seen publishers recycling names without incrementing the volume number, and many sites now track a comic by name, series, and year. Currently I address this by adding a qualifier in the title field itself if needed.\u00a0 Finally, it is easy to screw up the Genre field (which, incidentally, might have been better named Key Characters) by using similar but not exact terms \u2013 for example, Dr. Strange versus Doctor Strange.\u00a0 There is no really good answer for this dilemma.\u00a0 One possible solution is to create an approved list of character names in another table which then would have a relationship to the Story Table, but this is a very difficult undertaking.\u00a0 The better choice seems to be a judicious use of the wild card \u2018*\u2019 and some perseverance when making a query.<\/p>\n","protected":false},"excerpt":{"rendered":"

Last week, I presented a simple schema for organizing a comic collection. \u00a0At the heart of the schema is the idea of a many-to-many relationship between publications and stories.\u00a0 That… Read more ><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-138","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/aboutcomics.blogwyrm.com\/index.php?rest_route=\/wp\/v2\/posts\/138","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/aboutcomics.blogwyrm.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/aboutcomics.blogwyrm.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/aboutcomics.blogwyrm.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/aboutcomics.blogwyrm.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=138"}],"version-history":[{"count":8,"href":"https:\/\/aboutcomics.blogwyrm.com\/index.php?rest_route=\/wp\/v2\/posts\/138\/revisions"}],"predecessor-version":[{"id":152,"href":"https:\/\/aboutcomics.blogwyrm.com\/index.php?rest_route=\/wp\/v2\/posts\/138\/revisions\/152"}],"wp:attachment":[{"href":"https:\/\/aboutcomics.blogwyrm.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=138"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/aboutcomics.blogwyrm.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=138"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/aboutcomics.blogwyrm.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=138"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}