Managing a (video game) magazine collection?

[Ze_ro]

Alright, this is mildly off-topic, but hear me out...

I have a decently large collection of video gaming magazines, and for a while now, I've been wanting to catalog them somehow so that I can find specific reviews and articles if I want to (searching through 5 years of EGM's can get tedious), and also so that I can see which issues I have or still need.

Now, I've been using a spreadsheet to keep track of my actual video game collection, and it's been working out pretty well... I was thinking of using another spreadsheet for my magazines, but I'm not convinced it'll work properly. What I'd like is to have all the major contents (reviews (seperated by system preferably), features, strategy guides, etc) of each issue cataloged so that I can easily search for it, and also browse through the information as easily as possible.... I'm not sure that a simple spreadsheet can do this.

Does anyone have any ideas on a program (running on Linux if possible) that could handle something like this? I've been thinking of fiddling around with some generic database software like MySQL or something to see if it'll do what I want. If worst comes to worst, I might just write my own program to handle it.

I fully expect that it'll take forever to actually enter all the information, but hopefully it'll be worth it when it's all done.

--Zero

[hydr0x]

i suppose a SQL database, i'm not sure how the database should be structured though :(

it'll really be a pain in the ass to add all that info :o

[Achika]

I used to do this with my Nintendo Power magazines. It may not look pretty, but it's just a matter of making different categories in each column. If I can remember, the columns I used were:

Issue #/ Cover Image/ Major Article/ Systems Covered/ Walkthroughs/ Hints

Granted, I didn't list every single game in the categories, only those I *thought* I would be interested in recalling at a later date.

Hey, maybe if everyone chips in, we could all get this done for DP (if he wants one more thing bringing people to the site, taking away the bandwidth :P ) Maybe this will help to solidify that it's "worth it"

Personally, I wanted to atleast start a issue/image database for OXM, NP, and ODCM on my site.

[jhd7]

I did a smaller version of what you're suggesting, and it'll take a lot of time. Like you, I had a huge mag collection and wanted to find things quickly. Instead of cataloging *every* game and article, my goal was simply to reference every published hint (magazine, hint book, video) for NES games. I began a companion list of interesting articles (such as Behind the Scenes at Sunsoft), but the motivation quickly died.

My results are compiled in 5 html pages at my website, not in a database. On the main page, scroll down to LISTS and click "Nes Index."

http://nestral.topcities.com/

I think a year-end issue of GamePro contained a master index of all their hints and reviews for that year... finding those issues could save a lot of page-flipping.

Jeff D

[Ze_ro]

i suppose a SQL database, i'm not sure how the database should be structured though :(

If possible, I'd love to have something that could be restructured at will... for example, a view by issue could be something like this:


EGM (April, '93)
+-Reviews
+-Genesis
+-Outrun 2019
+-Battletoads
+-...
+-SNES
+-Shadowrun
+-Family Dog
+-...
+-Previews
+-Genesis
+-Blaster Master 2
+-Cool Spot
+-...
+-SNES
+-Batman Returns
+-Super Turrican
+-...
+-Features
+-Super FX Technology
+-Konix 32-bit System
+-...
+-Tips and Tricks
+-Genesis
+-Lotus Turbo Challenge (Passwords)
+-T2: The Arcade Game (Level skip, No overheat)
+-...
+-SNES
+-Gods (Passwords)
+-Q*Bert 3 (Access Level 11)
+-...
+-And so on...

Yet also be able to view things by game, such as:


Sonic the Hedgehog (Genesis)
+-Review
+-EGM month/year
+-Gamepro month/year
+-...
+-Previews
+-EGM month/year
+-...
+-Tips and Tricks
+-Gamepro month/year (Debug Mode)
+-EGM month/year (Level Select)
+-...

It would be nice if it was possible to view everything by arbitrary categories... like a list of all previews or all Tips and Tricks, or everything for a specific system and such. Can SQL do stuff like this?

I figure if I do one or two magazines a day, I can finish it sometime within the next decade or so.

--Zero

[Flack]

Any database program can. That's what SQL and all those are for!

If it's just for your own personal use, I'd use something like Access. For example, I have all my music, movies, etc in an Access database. From that, you build queries, and then reports. Access, SQL, MySQL, etc can all be accessed either the programs themselves or through web front ends. That's what mine is; an Access database with a web front end. So I can query for "all bands with the word Metal", all songs by Metallica, or how many Metallica CD's I have, and get that info all from the same database. Querying for all hints and tips or all things from one magazine would be no different.

[o2william]

This idea got me thinking quite a bit, since I enjoy designing things like this. I agree with Flack; Access is probably your best bet.

Here's a diagram I put together real quick. It might give you an idea of how to design your database:

http://www.classicgaming.com/o2home/test/article_erd.gif

It assumes you'd be tracking articles that have references to multiple games (hence the ArticleToGame table).

Then to look up an article that referenced NES Ninja Gaiden for example, your SQL statement would look something like this:

SELECT Issue.MagTitle, Issue.Volume, Issue.Number, Article.Title,
Article.Page
FROM Article
LEFT JOIN Issue ON Article.IssueID = Issue.IssueID
LEFT JOIN ArticleToGame on ArticleToGame.ArticleID = Article.ArticleID
LEFT JOIN Game on Game.GameID = ArticleToGame.GameID
WHERE Game.Title = 'Ninja Gaiden'
AND Game.SystemName = 'NES'

Access has a lot of built-in tools that will help you design something suitable even if you don't know a lot about databases or SQL.

Like I said, I did all this quickly so it could undoubtedly be improved. Looking at it now, there's really no need for that Magazine table.

This would be a fun application to whip up in .NET. Too bad I don't have the time to do it right now...

[Anthony1]

It would be sweet if one of you guys can actually successfully do this. I would love to have a copy of it, when you are done.

I would love to have something like that for my beloved Die Hard Game Fan's. Also my EGM's,Next - Gen's, GamePro's, VG & CE's and Game Players.

[Achika]

It would be sweet if one of you guys can actually successfully do this. I would love to have a copy of it, when you are done.

I would love to have something like that for my beloved Die Hard Game Fan's. Also my EGM's,Next - Gen's, GamePro's, VG & CE's and Game Players.

So, why not help in compiling the data from those issues and send it to the person that will be "plugging it in" so to speak?

[Ze_ro]

It would be sweet if one of you guys can actually successfully do this. I would love to have a copy of it, when you are done.

Well, I certainly hadn't planned on making this a public database really... I have a decent number of magazines, but nowhere near enough to start some comprehensive internet repository or anything like that. The main point of this was just to keep convenient records for myself. However, if this ends up working out really well, and there's a big interest in it, then who knows? Making a PHP frontend for it might not be that tough.

Anyways, I finally installed MySQL and went through the tutorial... and it seems pretty easy, and extremely robust. Certainly seems like it'll do all the stuff I want (the limitations of course, being my own lack of experience with databases and SQL). I'll have to take some time out to think everything through before I dive in and start compiling all the information. I want to make sure I get all the data structures and tables right the first time. Getting 20 issues into it and realizing I based the whole thing on some very poor fundamentals would certainly suck.

It seems that it might be easy enough to do the whole database using a single table, with each row representing an article. Each entry would include the name of the game or article, the magazine, date/issue, page number, type of article (review, preview, etc), applicable system(s ?), and whatever remarks would fit the article (ie, what score a review gave, the content of a tip/trick, and so forth). Organizing and searching the content with a single SQL command will certainly be helpful, and it'll be a nice excuse to finally learn how to use SQL.

I'm still unsure of what scope to attempt... I definitely want to keep track of reviews and tricks and such... but keeping track of news articles or advertisements (I have actually looked through magazines to find particular ads before) could be tricky to do properly, would likely take quite a while, and perhaps wouldn't even be very useful.

--Zero

[Ze_ro]

Well, I finally got around to doing this... If you want to check out what I've got so far, feel free to check it out (http://supernova.ath.cx/magtest.php). It's ugly, and it's extremely simple... but I plan to make it overly complicated soon enough ;)

I've only managed to put two magazines in there so far (GamePro July '90 and EGM May '92), but I hope to add in some other magazines in order to test this out before I make any changes. I look forward to adding stuff like Compute's Gazette and such into there. Finding code for specific C64 games has always been a huge pain.

So far the only problems I have with it is that it should have some sort of field for what company made the game or hardware that the article is about, and I'd like some way to keep track of what's on the cover of each magazine (When I'm at thrift stores, remembering magazine covers is the only way I can keep track of which magazines I even have). I'm thinking I'd have to keep a seperate table for this.

--Zero